MySQL的ON DUPLICATE KEY UPDATE语句

举报
lxw1844912514 发表于 2022/03/26 23:35:23 2022/03/26
【摘要】 有这么一种场景: 查找记录 如果存在:更新字段如果不存在:插入字段 如果使用ORM来表述的话,则比较长,而且会出现并发问题,即多个提交时,假设设置了唯一索引的情况下,会发生数据冲突,然后 就会隔三差五收到错误:duplicate key ‘xxx’。 因此使用MySQL提供的 INSERT ... ON DUPLIC...

有这么一种场景:

  • 查找记录
    • 如果存在:更新字段
    • 如果不存在:插入字段

如果使用ORM来表述的话,则比较长,而且会出现并发问题,即多个提交时,假设设置了唯一索引的情况下,会发生数据冲突,然后 就会隔三差五收到错误:duplicate key ‘xxx’。

因此使用MySQL提供的 INSERT ... ON DUPLICATE KEY UPDATE 语句,这是MySQL的扩展语法,因此也就意味着,使用了这个语句之后, 数据库基本上就被绑定在MySQL上了,不过没有关系,一般谁会轻易更换数据库呢?

这个语句的语法是这样的:

INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1, b=4;

 

分三段来理解:

  • 第一段,常规的INSERT语句。INSERT INTO <table>(col1, col2, ...) VALUES (val1, val2, ...)
  • 第二段,ON DUPLICATE KEY,表示后面的语句是当数据有冲突的情况下会执行的
  • 第三段,UPDATE语句。UPDATE a=1, b=2

注意,由于有 ON DUPLICATE KEY,也就是说必须得有字段会发生冲突。什么属性的字段能冲突呢?

  • 主键(Primary Key)
  • 唯一索引(Unique Key)

Mysql on duplicate key update用法及优缺点 

在实际应用中,经常碰到导入数据的功能,当导入的数据不存在时则进行添加,有修改时则进行更新,

  在刚碰到的时候,一般思路是将其实现分为两块,分别是判断增加,判断更新,后来发现在mysql中有ON DUPLICATE KEY UPDATE一步就可以完成(Mysql独有的语法)。

ON DUPLICATE KEY UPDATE单个增加更新及批量增加更新的sql

在MySQL数据库中,如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的惟一索引或主键中产生重复值,那么就会发生旧行的更新;如果插入的行数据与现有表中记录的唯一索引或者主键不重复,则执行新纪录插入操作。

说通俗点就是数据库中存在某个记录时,执行这个语句会更新,而不存在这条记录时,就会插入。

注意点:

  因为这是个插入语句,所以不能加where条件。

  如果是插入操作,受到影响行的值为1;如果更新操作,受到影响行的值为2;如果更新的数据和已有的数据一样(就相当于没变,所有值保持不变),受到影响的行的值为0。

该语句是基于唯一索引或主键使用,比如一个字段a被加上了unique index,并且表中已经存在了一条记录值为1,

下面两个语句会有相同的效果:


  
  1. INSERT INTO table (a,b,c) VALUES (1,2,3)
  2. ON DUPLICATE KEY UPDATE c=c+1;
  3. UPDATE table SET c=c+1 WHERE a=1;

ON DUPLICATE KEY UPDATE后面可以放多个字段,用英文逗号分割。

再现一个例子:


  
  1. INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  2. ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

表中将更改(增加或修改)两条记录。

参考:MySQL :: MySQL 8.0 Reference Manual :: 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement

文章来源: blog.csdn.net,作者:lxw1844912514,版权归原作者所有,如需转载,请联系作者。

原文链接:blog.csdn.net/lxw1844912514/article/details/123378763

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。