1. 为什么你需要UPSERT从一条重复数据说起先讲个真实场景。去年我在维护一个订单同步系统上游ERP每天会推送三四万条订单状态变更我们的处理逻辑是先查一下订单号在不在本地表里在就UPDATE不在就INSERT。一开始数据量小倒没什么感觉等订单量涨到几十万接口响应开始变慢排查后发现光“查一下在不在”这个操作每天就要执行几万次SELECT再加上网络往返、事务开销慢是必然的。后来我把这段逻辑换成了MySQL原生的ON DUPLICATE KEY UPDATE一个SQL搞定“存在就更新不存在就插入”接口耗时直接降了一个数量级。这就是所谓的UPSERT操作——UPDATE和INSERT的合体。很多刚接触MySQL的同学看到这个语法会以为它只是个“偷懒”的写法用一条语句省掉先查再写的两步逻辑。但它的价值远不止省代码更重要的是减少了应用与数据库之间的交互次数避免了“先查后写”这个组合在并发场景下常见的竞态问题。这篇文章我把ON DUPLICATE KEY UPDATE从用法、原理、批量更新到踩坑经验完整梳理一遍。内容适合正在用MySQL做业务开发的后端工程师也适合要写数据同步脚本、清洗任务的运维和数据分析同学刚入门但已经会写基本INSERT、UPDATE语句的朋友也能直接上手。2. 基本语法与执行逻辑搞清楚DUPLICATE KEY到底在查什么2.1 一个最简单的用法示例先看最基础的写法INSERT INTO user (id, name, age) VALUES (1, 张三, 25) ON DUPLICATE KEY UPDATE name VALUES(name), age VALUES(age);这条SQL的意思是往user表里插入一条id1、name张三、age25的记录如果id1已经在表里了就把name更新成张三、age更新成25。初学者最容易困惑的点是MySQL怎么知道“重复”了答案不是全表扫描也不是任意字段重复都会触发更新而是取决于表的唯一索引和主键。2.2 触发条件唯一索引和主键ON DUPLICATE KEY UPDATE的“DUPLICATE KEY”指的就是主键PRIMARY KEY或唯一索引UNIQUE KEY。当你要插入的数据和表中已有数据在主键或任意唯一索引上发生了冲突也就是有重复值时MySQL就会转而去执行后面的UPDATE语句。这里有三个常见误解我得澄清一下不是只有主键冲突才会触发。只要任何一个唯一索引冲突了都会触发。比如一张表上既有主键id又有一个唯一索引email你插入的email已经存在同样会走更新逻辑。不是普通索引冲突就会触发。普通索引INDEX上的重复值不会触发因为普通索引本来就允许重复。多个唯一索引同时冲突时MySQL只更新一行。这一点后面讲坑的时候会细说。2.3 UPDATE子句里发生了什么当冲突触发后MySQL不会动那条已存在记录的id或者其他冲突字段而是执行ON DUPLICATE KEY UPDATE后面跟的更新表达式。你可以指定更新哪些字段也可以不更新某个字段。关键的一个点是在UPDATE子句里等号右边的字段名默认引用的是“将要被插入的那一行”的值。也就是说VALUES(name)拿到的是你在VALUES列表里传的张三age同理。如果你写ON DUPLICATE KEY UPDATE name 李四;那么冲突发生时name会被硬改成李四age保持不变。这是再简单不过的赋值逻辑很多时候你的业务其实是希望“拿新值覆盖旧值”所以直接用VALUES()函数最省事。2.4 执行效率怎么样相比“SELECT再INSERT或UPDATE”的两段式写法ON DUPLICATE KEY UPDATE只要一次网络往返、一次语句解析事务也只有一个。当数据量大的时候这个差距会被明显放大尤其是在批量写入场景里。此外它还有一个隐含优势这段逻辑在数据库端原子执行不需要你在应用层加锁来防并发覆盖。假如你手动先SELECT再UPDATE两个请求同时来理论上都存在读到旧值再覆盖写的可能但ON DUPLICATE KEY UPDATE本身是单条语句通过唯一索引定位直接更新或插入从机制上消除了这个窗口期。3. 批量更新一条SQL处理成千上万行3.1 批量写入场景下的写法在实际项目中ON DUPLICATE KEY UPDATE最香的应用场景是批量同步。比如上游给了你一千条数据你要全量灌入本地表已有的覆盖没有的新增。最直观的写法是把多条VALUES拼在一条INSERT里INSERT INTO user (id, name, age) VALUES (1, 张三, 25), (2, 李四, 30), (3, 王五, 28) ON DUPLICATE KEY UPDATE name VALUES(name), age VALUES(age);MySQL会对这三行数据逐行判断遇到主键id冲突就更新没冲突就插入一切都是数据库内部完成的。3.2 真的所有行都更新了吗ROW_COUNT的影响批量执行后如果你想知道到底插入了多少行、更新了多少行可以用ROW_COUNT()函数判断。这里有个容易踩的坑MySQL的ROW_COUNT并不是“更新了几行就返回几”而是有它自己的计数约定。插入一行新数据ROW_COUNT()返回1。更新一行已有数据ROW_COUNT()返回2。如果插入的数据和原有数据一模一样没有任何字段值发生变化ROW_COUNT()返回0。所以如果你通过ROW_COUNT()来统计“本次更新了多少行”记得要把返回值除以2才是实际更新的行数。这在做数据对账、幂等检查时特别容易算错我第一次在这里翻过车排查了很久才发现是计数方式的锅。3.3 批量处理的推荐分片策略ON DUPLICATE KEY UPDATE虽然强大但也不是让你放开了把十万条数据拼成一条SQL去执行。MySQL对单条SQL包大小有限制太长的VALUES列表会引发max_allowed_packet报错也会让主从同步的压力剧增。我的经验是批量数据分片执行每片500到1000条比较合适既保证网络包不会太大又能让事务不至于过长。如果是几万条甚至几十万条的同步任务用循环分片配合事务提交稳定性会好很多。4. 与REPLACE INTO、INSERT IGNORE的选型对比4.1 REPLACE INTO看着像实则不同很多人会把ON DUPLICATE KEY UPDATE和REPLACE INTO搞混因为两者都能实现“有则更新、无则插入”。但它们的工作机制有本质差别。REPLACE INTO的处理方式是检测到唯一键冲突时先删除旧记录再插入一条新记录。这就带来几个后果删除再插入意味着如果表上有自增主键每次REPLACE都会让自增ID变化自增序列会被快速消耗。如果有外键关联到这张表删除操作可能会触发级联删除或者报错。删除旧记录后插入新记录实际上不是“原地更新”如果表上有触发器触发器的执行次数也不同。未被SQL指定的字段会被重置为默认值而ON DUPLICATE KEY UPDATE只更新你指定的字段。从语义上看ON DUPLICATE KEY UPDATE更像是“修正”REPLACE INTO更像是“推倒重来”。对绝大多数业务场景来说原地更新都是更安全可控的选择。除非你的目的就是完全重建这条记录否则我都不建议用REPLACE INTO。4.2 INSERT IGNORE只插不改INSERT IGNORE则是另一个思路遇到主键或唯一索引冲突时直接静默跳过这一行既不报错也不更新。这适合那种“我只想把新数据灌进去已有的别动”的场景比如初始化数据、导入历史日志。三种写法放一起看更清晰语法冲突时行为自增ID消耗适用场景INSERT IGNORE跳过不报错有消耗导入数据、初始化字典表REPLACE INTO删旧插新消耗大且可能引发副作用极少使用不建议业务中用ON DUPLICATE KEY UPDATE原地更新指定字段仅在真正插入时消耗数据同步、状态更新、幂等写入4.3 为什么说UPSERT是大多数情况下的最优解从语义准确性、副作用大小、可控性三个方面看ON DUPLICATE KEY UPDATE是三者里最平衡的。它可以只更新你关心的字段不会误重置其他列不会动原记录的主键值外键关系也保持稳定。尤其在做数据回放、消息重新消费这类幂等场景时你希望多次执行同一个操作得到相同结果UPSERT天然满足这个要求。举个实际例子订单表有一列status你每天晚上从结算系统同步一批订单状态。用ON DUPLICATE KEY UPDATE更新status即使某条订单被同步了两次第二次也不会产生额外影响但同一个场景用REPLACE INTO第二次同步会先删掉原记录再插入如果这期间有别的业务往这行数据上附加了新字段就会被直接抹掉。5. MySQL 8.0.20之后告别VALUES()的推荐新写法5.1 VALUES()函数为什么要被弃用MySQL官方从8.0.20版本开始标记VALUES()函数为deprecated并在未来版本中有移除计划。原因在于VALUES()在语义上有一定迷惑性特别是在复杂的ON DUPLICATE KEY UPDATE语句里它引用的行来源不够直观。官方推荐了一种新的别名语法就是在INSERT语句后面给VALUES行起个别名然后用别名点字段名来引用。具体的新写法长这样INSERT INTO user (id, name, age) VALUES (1, 张三, 25), (2, 李四, 30) AS new ON DUPLICATE KEY UPDATE name new.name, age new.age;注意这里有个细节新增了AS new的别名声明然后在UPDATE子句里用new.name、new.age来引用要插入的值。如果你还在用MySQL 5.7或者8.0.20以下的版本旧写法VALUES()函数依然可用不受影响。但如果你在写新项目或者即将升级到更高版本建议从现在开始就用别名写法省得到时候批量改。5.2 新版写法的常见误区和兼容性验证使用新写法时有两点值得注意别名用不用AS关键字都可以new、new_value这种命名都行但别和表名、列名冲突。当然如果MySQL版本过旧可能不支持这个语法建议生产环境先验证一下版本再全面铺开。如果你在同一句INSERT里既要处理多行又要引用外部变量别名写法一样能扛住。我自己在本地用MySQL 8.0.32验证过新旧两种写法执行计划完全一致性能上没有任何差别。也就是说这不是一次“优化升级”而是一次“规范迁移”核心动机是让SQL可读性和语义完整性更好。5.3 如何平滑从VALUES()迁移到新写法迁移过程倒不复杂无非是把所有VALUES(字段名)替换成别名.字段名然后在表名后面加上AS别名。但建议先拿一个数据量小的业务表做灰度同时检查有没有ORM框架或底层DAO封装自动生成的SQL还写死着旧语法。如果你用的是MyBatis之类支持动态SQL的框架一般需要手动改XML里对应的SQL片段insert idupsertUser parameterTypemap INSERT INTO user (id, name, age) VALUES (#{id}, #{name}, #{age}) AS new ON DUPLICATE KEY UPDATE name new.name, age new.age /insert改完之后除了常规功能回归最好单独验证一下“插入”和“更新”两条分支都正常因为不少框架封装时只验证了插入路径更新路径反而被忽略了。6. 实战中的坑与避雷指南自增ID、死锁与判断条件6.1 自增ID消耗比你想的严重很多业务表都用自增主键。当你使用ON DUPLICATE KEY UPDATE时MySQL在尝试插入一行的时候哪怕最后走了更新分支也会先占用一个自增ID然后再放弃这个ID。这意味着你表里的自增序列会出现大量“空洞”ID不连续是常态。如果业务上对ID连续性有要求比如对账时需要按照ID区间切片拉数这个特性就要特别小心。但绝大多数普通业务表并不依赖ID连续所以这个坑更多是“知道就好”不需要过度设计去规避。真正需要注意的是假如应用层逻辑里把id当作“第几条记录”来用那你会被无规律的空洞搞晕。6.2 并发写入时的死锁风险高并发场景下ON DUPLICATE KEY UPDATE也会引入死锁风险。这个风险主要来源于并发事务同时尝试插入和更新同一条记录时InnoDB的锁顺序可能不一致。虽然不能说“用它就一定会死锁”但在压测中并发量大、隔离级别高的情况下确实比普通单条INSERT更容易触发死锁。规避方法也很直接保持事务短小精悍别在事务里做耗时的外部调用。控制批量片的大小避免一个事务锁太多行。给热点表做合并写入减少单行上的并发竞争。如果死锁已经发生了应用层捕捉到死锁错误后做有限次数重试这是一种通用兜底方案。从我实践看真正踩到死锁的时候大多是两三张表之间有外键关联或者多个业务入口同时对同一行做大事务更新。单纯用一条UPSERT语句做单行更新遇到死锁的概率并不高。6.3 如何判断“到底是插入了还是更新了”除了前面说的ROW_COUNT()你还可以在UPDATE子句里主动改一个字段来标记本次操作时间。比如增加一行ON DUPLICATE KEY UPDATE name new.name, age new.age, updated_at NOW();这样每次更新时updated_at肯定会刷新而插入时可以用DEFAULT CURRENT_TIMESTAMP自动填充不需要额外处理。如果你需要知道某条数据是刚插入的还是刚更新的最稳妥的做法是在插入时保留一个created_at字段插入时用默认值更新时不碰它查询时比较两个字段就能区分。6.4 多个唯一键冲突时的注意点假如一张表既有主键id又有唯一索引mobile你在一条ON DUPLICATE KEY UPDATE语句里插入的数据同时导致id和mobile都和现有记录冲突MySQL会只做一次更新不会做两次。但具体更新哪一行取决于InnoDB在内部唯一索引检查时的先后顺序通常先命中哪个索引就按哪个来。更隐蔽的问题是如果id冲突指向A行mobile冲突指向B行A和B不是同一行MySQL的行为其实依赖索引检查顺序结果不一定符合直觉。所以在设计唯一索引时要尽量避免在同一张表上有多个互不相关的唯一键并且都用在这种UPSERT场景里。能用一个业务唯一键解决的就别再加第二个。7. 扩展结合COALESCE的局部更新技巧前面讲的都是覆盖式更新新值一来旧值就被替换。但真实业务里经常有“部分更新”的需求。比如用户修改个人资料时只传了手机号没传昵称你希望nickname保留旧值不要被空字符串覆盖。一个经典操作是结合COALESCE函数INSERT INTO user (id, name, age) VALUES (1, 张三, 25) AS new ON DUPLICATE KEY UPDATE name COALESCE(new.name, name), age COALESCE(new.age, age);COALESCE会从左到右返回第一个非NULL的值。如果new.name为NULL就取原有的name如果new.name不为NULL就用新值覆盖。这招在接口入参为NULL表示“未修改”的场景下非常好用。不过这里要注意如果你在业务里习惯用空字符串表示“未修改”那COALESCE就帮不上忙了因为空字符串不是NULL会把原有值覆盖成空。针对这种情况你可以在SQL前用NULLIF把空字符串转成NULLname COALESCE(NULLIF(new.name, ), name)这样传入空字符串时会自动保留旧值传入正常字符串时会正常更新。这个组合在写数据补录脚本时极其实用推荐收藏。8. 关于性能与索引设计的两个额外提醒8.1 UPSERT性能取决于索引命中ON DUPLICATE KEY UPDATE的执行效率有一个大前提冲突检测依赖的字段必须有合适的唯一索引。如果你在普通索引上执行UPSERT它压根不会触发更新分支而是直接插入重复数据这就会导致“存在也不更新反而插入一堆重复记录”的严重问题。所以上线前一定要用SHOW INDEX FROM table_name确认一下你期望的冲突字段到底有没有建立唯一索引。如果表是新建的业务上要保证某个字段不重复就直接建UNIQUE KEY如果是老表要加唯一索引记得先排查存量数据有没有重复值否则索引建不上去。8.2 大表UPSERT的锁范围InnoDB的行锁是基于索引的你要是靠唯一索引来定位锁的范围通常就是那一行。但如果表上没有合适的索引MySQL只能走全表扫描去判断是否冲突这时候锁的范围可能扩大甚至影响整张表的写入。这个问题在数据量大的表上特别致命执行一次UPSERT相当于做了一次全表扫描性能完全不可控。因此我的习惯是所有要做UPSERT的表必须确认至少有一个主键或者唯一索引覆盖冲突字段否则宁可用先查后写的逻辑也不要用这种注定会锁表的写法。9. 写在最后一个让代码更干净的实践ON DUPLICATE KEY UPDATE这种语法给了我一个启发很多所谓“通用的业务逻辑”其实数据库本身早就提供了能力只是我们习惯性地在应用层绕一圈。把判断和写入合并成一步不仅代码量更少系统的并发稳定性也有本质提升。如果让我推荐一个最值得立刻用起来的组合那就是“唯一业务键 ON DUPLICATE KEY UPDATE COALESCE局部更新”。这个组合可以让你的数据写入逻辑变得很干净一段同步或者消费逻辑无论执行多少次数据最终都保持一致也不会产生重复记录处理速度还比“先查再写”要快。在我经历的项目里从订单状态同步、用户资料更新到各种中间表数据回刷都靠这个语法撑住了量级。实践下来最大的收益不是SQL写得短而是少了一次查询、少了一个事务、少了一堆潜在的并发隐患。希望你也能在合适的场景里把它用起来。真遇到奇怪行为的时候记住优先检查三件事唯一索引有没有建对、MySQL版本有没有到8.0.20、批量分片有没有控制在合理范围内。