on duplicate key update用法和replace区别

MySQL插入更新技巧

最新推荐文章于 2025-08-04 16:16:59 发布

转载最新推荐文章于 2025-08-04 16:16:59 发布 · 3.1k 阅读

文章标签：

#mybatis #mysql #insert #update

mysql 专栏收录该内容

19 篇文章

订阅专栏

本文介绍MySQL中使用INSERT...ON DUPLICATE KEY UPDATE语句处理数据插入和更新的方法，以及与REPLACE语句的区别。通过具体实例解释如何在多行记录插入时有效利用VALUES函数解决更新问题。

[转载于]https://blog.youkuaiyun.com/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的时候，字段要写全，防止老的字段数据被删除。