on duplicate key update用法和replace区别

傷城~ 2022-05-14 08:26 166阅读 0赞

\[转载于\][https://blog.csdn.net/jaryle/article/details/75909531][https_blog.csdn.net_jaryle_article_details_75909531]

**【强调】注意：where条件跟的字段必须唯一（主键也唯一）**

insert语句的末尾添加on duplicate key update语法：如果插入行出现**唯一索引或者主键**重复时，则执行旧的update；如果不会导致唯一索引或者主键重复时，就直接添加新行。

例如：如果列a被定义成唯一索引（unique）或者主键（primary key），并且a=1，此时使用insert 语句的末尾加on duplicate key update语句时，

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;

这二个语句的执行效果是一样的。此时a=1并且是唯一索引或者主键，上面的语句就会出现唯一索引或者主键重复，此时on duplicate key update就在**原有数据上更新c=c+1**；**新的数据b没有被插入进去，只是更新了c的值。**

## 扩展知识一： ##

现在问题来了，如果INSERT多行记录， ON DUPLICATE KEY UPDATE后面字段的值怎么指定？要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE，到底他会更新一行记录，还是更新所有需要更新的行。这个问题困扰了我很久了，其实使用VALUES()函数一切问题都解决了。

举个例子，字段a被定义为UNIQUE，并且原数据库表table中已存在记录(2,2,9)和(3,2,1)，如果插入记录的a值与原有记录重复，则更新原有记录，否则插入新行：

INSERT INTO TABLE (a,b,c) VALUES 
    (1,2,3), 
    (2,5,7), 
    (3,3,6), 
    (4,8,2) 
    ON DUPLICATE KEY UPDATE b=VALUES(b);

以上sql语句，发现（4,5,6）和已存在记录(2,2,9)发生唯一值冲突，此时就执行update b=VALUES(b),执行后的结果将原有记录(2,2,9)更新为(2,5,9),将(3,2,1)更新成(3,3,1)，插入新记录(1,2,3)和(4,8,2)

## 扩展知识二： ##

mysql之replace和ON DUPLICATE KEY UPDATE的区别

（1）在没有主键或者唯一索引重复时，replace与insert .. on deplicate udpate相同。

（2）在主键或者唯一索引重复时，**replace是delete老记录，而录入新的记录，所以原有的所有记录会被清除，这个时候，如果replace语句的字段不全的话，有些原有的比如c字段的值会被自动填充为默认值。**而insert .. duplicate update则只执行update标记之后的sql，从表象上来看相当于一个简单的update语句。它保留了所有字段的旧值，只更新update后面的语句，而replace没有保留旧值，直接删除再insert新值。  
从底层执行效率上来讲，replace要比insert .. on duplicate update效率要高，但是在写replace的时候，字段要写全，防止老的字段数据被删除。

[https_blog.csdn.net_jaryle_article_details_75909531]: https://blog.csdn.net/jaryle/article/details/75909531