MySQL的ON DUPLICATE KEY UPDATE语句

有这么一种场景：

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

如果使用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，

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

  

   

    

     

    
    

     

      INSERT INTO table (a,b,c) VALUES (1,2,3)  
     
    
   

    

     

    
    

     
  ON DUPLICATE KEY UPDATE c=c+1;  
     
    
   

    

     

    
    

     
  
     
    
   

    

     

    
    

     

      UPDATE table SET c=c+1 WHERE a=1;
     
    
  
 

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

再现一个例子：

  

   

    

     

    
    

     

      INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  
     
    
   

    

     

    
    

     
      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