做后台开发的兄弟大概率都写过这么一段代码先SELECT查一下记录在不在不在就INSERT在就UPDATE。我早年在做订单统计模块时也这么写表面看着稳其实里面藏着两个问题——两次网络往返之间留出了竞态窗口并发一多就容易重复插入或者你刚查出来的数据已经被别人改了。后来线上统计表真的出现了重复数据我才把MySQL的ON DUPLICATE KEY UPDATE彻底研究了一遍。这个语法说白了就八个字存在即更新不存在则插入。它既能处理单条记录也能处理批量写入还能配合表达式做计数器累加是生产环境里非常常用的upsert方案。这篇文章就把它的使用方式、执行原理和实战中容易踩的坑一次讲清楚。1. 为什么需要“存在即更新不存在则插入”从一次统计表同步说起1.1 先查再插的硬伤竞态条件与两段式网络交互大多数刚接触MySQL的后端同学第一次实现“更新或插入”时都会走三段式逻辑先查再判断最后写。伪代码大概是这样的user userMapper.selectByUsername(zhangsan); if (user null) { userMapper.insert(...); } else { userMapper.update(...); }这段代码在单线程、低并发下没有任何问题。但一旦线上流量上来问题就非常明显SELECT和INSERT之间不是一个原子操作两个请求同时判断“记录不存在”然后同时执行INSERT其中一条必然报Duplicate entry错误。为了规避这个异常有人会在catch里补一个UPDATE但如果两个人都在做“先查后写”的操作你又可能把对方刚更新的数据覆盖掉。这类问题本质上不是代码写得不够好而是把本该由数据库保证的“唯一性幂等性”交给了业务应用层。ON DUPLICATE KEY UPDATE的价值就在于把“检测冲突”和“执行更新”放在同一条SQL里由InnoDB在内部判断唯一键冲突应用层不需要再关心“先查后插”的窗口期问题。换句话说它给了一条幂等写入路径无论执行多少次最终表中只保留一行。1.2 REPLACE INTO和INSERT IGNORE为什么都不香一说“存在即更新不存在则插入”很多人的第一反应是REPLACE INTO。这个语法确实也能达到类似效果但它的实现方式是先DELETE旧行再INSERT新行。这意味着旧的行的自增ID会被消耗掉即使数据内容没有变化主键值可能已经变了如果这个表被其他表引用外键约束会直接导致DELETE失败会触发DELETE相关的触发器而ON DUPLICATE KEY UPDATE不会整行删除再插入的成本比“原地更新个别字段”高尤其在大字段多的表上差距明显。INSERT IGNORE则是另一个极端遇到重复键直接吞掉错误什么都不做。它适合“没有则插入有则跳过”的场景但它无法更新已有记录。而且它会把其他类型的错误也一并忽略比如字段超长、非空约束失败等。生产环境里如果一个错误被悄悄吞掉排查问题的成本会变得非常高。所以在“保留旧行、更新个别字段、不破坏外键关系、不浪费自增ID”这组约束下ON DUPLICATE KEY UPDATE是更合适的默认选择。REPLACE INTO只适合那些确实需要“整行重建”的场景INSERT IGNORE只适合“纯去重写入”的场景。1.3 适合上手的场景清单从我个人经验看下面这些业务场景非常适合用ON DUPLICATE KEY UPDATE场景特征典型SQL形态每日统计计数同一维度反复累加pv pv VALUES(pv)配置同步配置表反复全量覆盖批量VALUES冲突时更新全部配置字段订单状态流转同一订单更新状态与时间冲突时更新status、updated_at队列去重消费同一消息ID只保留一份消费结果冲突时更新时间戳批量导入Excel导入的数据已存在则覆盖多行VALUES按唯一键更新2. 执行原理唯一索引冲突检测是核心别拿普通索引硬套2.1 一条SQL在InnoDB内部到底做了什么先看基本语法结构INSERT INTO tbl (col1, col2, col3) VALUES (val1, val2, val3) ON DUPLICATE KEY UPDATE col2 VALUES(col2), col3 VALUES(col3);这条语句的执行过程大致是这样的MySQL先尝试执行普通的INSERT在InnoDB存储引擎层面插入一条记录前需要检查唯一索引如果发现唯一键冲突就放弃插入动作转而执行ON DUPLICATE KEY UPDATE后面的更新表达式。如果没有冲突就正常插入UPDATE子句完全不参与执行。这里最容易被忽略的点是“唯一索引”这个前提。ON DUPLICATE KEY UPDATE的冲突检测基于PRIMARY KEY和UNIQUE KEY普通索引哪怕重复一百遍也不会触发更新分支。比如你给username字段建的是普通索引那么insert两条usernamezhangsan的记录是完全合法的ON DUPLICATE KEY UPDATE永远不会有更新动作发生因为它压根没检测到“重复”。2.2 多个唯一索引同时冲突时的裁决规则很多表不止一个唯一键比如user表同时有username唯一键和email唯一键。这时ON DUPLICATE KEY UPDATE的规则是任意一个唯一键冲突都会触发UPDATE。但有个边界情况很容易踩坑如果一条插入语句同时导致两个不同的唯一键分别冲突而且它们指向的是两行不同的记录MySQL会直接报错而不是“两行都更新”。举个例子INSERT INTO user (id, username, email) VALUES (100, a, mail_b) ON DUPLICATE KEY UPDATE username VALUES(username);假设usernamea已经在id5的行上emailmail_b已经在id9的行上那么MySQL无法决定到底更新id5还是id9它会抛出类似Duplicate entry a for key username的错误。这种情况下你在设计业务唯一键时就要想清楚不要让多列唯一约束同时成为“裁决者”。最稳妥的做法是业务上只保留一个真正的唯一键其余字段用普通索引。2.3 自增主键的“无谓消耗”现象如果你用了自增主键且INSERT语句没有显式给主键列赋值即使最终记录因为唯一键冲突走了UPDATE分支那个被“尝试插入”时分配到的自增ID也不会归还。也就是说ID增长了一段但表里的实际行数没有增加ID之间出现空洞。这个现象是InnoDB为了保证并发插入性能而设计的正常情况下不值得担心也不应该作为“数据有问题”的依据。但如果你有“主键必须连续”的监控就要知道这个前提站不住脚。同样也尽量不要在ON DUPLICATE KEY UPDATE的UPDATE子句里更新主键本身否则容易出现新主键与已有行主键冲突的连环错误。3. 单条数据写入从“三步操作”到“一条SQL”3.1 最基础的标准写法与VALUES函数单条写入是最常见的用法核心写法如下INSERT INTO user_profile ( user_id, nickname, avatar, login_count, updated_at ) VALUES ( 123, 小张, /img/a.png, 1, NOW() ) ON DUPLICATE KEY UPDATE nickname VALUES(nickname), avatar VALUES(avatar), login_count login_count 1, updated_at NOW();第一次执行时user_id123不存在整行插入login_count等于1。第二次再执行同样的SQL时user_id123已存在MySQL会执行UPDATE分支nickname和avatar被更新成最新值login_count在旧值基础上加1updated_at被刷新。VALUES(column)这个函数在ON DUPLICATE KEY UPDATE里的含义是“取本次INSERT试图写入该列的值”。比如上面的VALUES(avatar)就是指INSERT语句里VALUES列表中的/img/a.png。如果不加VALUES()直接写avatar avatar那就成了“自己更新自己”等于什么都不变。这是一个非常容易混淆的地方尤其对于刚接触这个语法的同学。3.2 字段级别的更新控制哪些字段随INSERT值更新哪些保持不动并不是所有字段都需要放进UPDATE子句。比如create_time创建时间通常只在插入时写入更新时不希望被碰又比如某些字段希望保留首次写入的业务ID或负责人同样不应该出现在ON DUPLICATE KEY UPDATE的更新列表里。INSERT INTO user ( username, nickname, email, create_time ) VALUES ( lisi, 小李, lisiexample.com, NOW() ) ON DUPLICATE KEY UPDATE nickname VALUES(nickname), email VALUES(email);这里的create_time只在插入时生效如果username已存在只会更新nickname和emailcreate_time保持不变。这个能力是REPLACE INTO给不了的因为REPLACE INTO会把所有字段都重置。理解这一点后你就能很自然地用ON DUPLICATE KEY UPDATE做“部分字段覆盖式同步”而不是“整行销毁重建”。3.3 自增主键与业务唯一键同时存在时的组合写法业务表经常同时有自增主键和一个业务唯一键比如订单号、登录名、手机号。这时ON DUPLICATE KEY UPDATE的推荐写法是INSERT不指定主键只给业务字段让唯一键承担“是否存在”的判断职责。INSERT INTO account (phone, nickname, status) VALUES (13800138000, 小黑, 1) ON DUPLICATE KEY UPDATE nickname VALUES(nickname), status VALUES(status);这里的防重复判断依据是phone的唯一索引。如果phone13800138000的记录已存在那么触发更新如果不存在则插入一条新记录主键由自增机制生成。日常开发中这个模式是最舒服的业务幂等性交给业务唯一键MySQL自增ID只负责行定位两者职责干净分离。这种写法唯一的注意点在上面已经提过不要天真地认为“自增ID必须连续”。如果真的需要把业务编码做成连续编号应该单独用一张发号器表而不是依赖InnoDB自增。4. 批量更新的正确姿势一条SQL处理成百上千条记录4.1 多行VALUES的标准批量写法批量更新是ON DUPLICATE KEY UPDATE最实用的场景之一。比如同步商品库存、批量导入Excel、同步第三方接口返回的数据通常需要把一个列表里的数据全部写进MySQL已存在的更新新记录插入。标准写法如下INSERT INTO product_sku (sku_id, stock, price) VALUES (A001, 100, 19.9), (A002, 200, 29.9), (A003, 150, 39.9) ON DUPLICATE KEY UPDATE stock VALUES(stock), price VALUES(price);这样只需要一次网络交互MySQL内部会逐行判断sku_id是否已存在存在则更新stock和price不存在则插入。相比“每行执行一条SQL”、再配合事务提交的写法这种方式在性能上有质的提升尤其适合秒杀场景下的库存预热、商品共创批量导入等需求。4.2 用表达式做计数器累加与字段拼接批量场景里最经典的需求是“对已有记录累加值”。常见于埋点日志聚合、帖子浏览数、视频播放数等。这里有一个非常重要的语义区分到底是“累加”还是“赋值”。-- 错误把value直接赋给字段更新后永远是最后一次VALUES里的值 INSERT INTO topic_views (topic_id, view_count) VALUES (1, 1), (2, 1), (3, 1) ON DUPLICATE KEY UPDATE view_count VALUES(view_count); -- 正确在旧值基础上加1 INSERT INTO topic_views (topic_id, view_count) VALUES (1, 1), (2, 1), (3, 1) ON DUPLICATE KEY UPDATE view_count view_count 1;很多人都栽在这上面以为view_count VALUES(view_count)就能累加结果每次执行后都被覆盖成同一个数。如果你需要的是“每次写入都1”就必须使用view_count view_count 1这样的表达式。同理字符串字段也可以用CONCAT拼接数值字段可以用LEAST/MAX做上下限控制。比如限制登录次数最多99次login_count LEAST(login_count 1, 99)4.3 批量操作的参数边界max_allowed_packet与事务大小批量虽然好但不是“越多越好”。单条INSERT语句的大小受max_allowed_packet参数限制默认通常是64MB或16MB生产环境建议不要挑战极限。经验上每次批量提交控制在500~1000行之间比较稳妥具体取决于单行字段长度。还有一点必须强调INSERT ... ON DUPLICATE KEY UPDATE是一条语句在InnoDB里具备原子性。如果多行VALUES中的某一行在更新时触发了其他唯一键冲突比如你更新的字段撞了另一条记录的唯一值整条语句会失败并回滚前面的行也会一起回滚。这一点和“逐行UPDATE手动事务”完全不一样它是全有或全无。所以写批量导入工具时要注意控制每批数据量并且把容易冲突的数据做预清理避免一条脏数据让整批导入挂掉。5. 影响行数的1、2、0之谜与VALUES函数告别5.1 返回值在MySQL原生客户端里的真实含义ON DUPLICATE KEY UPDATE返回的影响行数并不符合普通UPDATE的直觉官方文档给出的规则如下返回值含义典型场景1新插入了一行之前不存在该唯一键2已有行执行了UPDATE且某字段值发生了变化更新生效0已有行执行了UPDATE但所有字段值都未变化幂等重复写入为什么更新成功是2而不是1因为InnoDB内部把它拆成了两个动作先尝试INSERT发现冲突再执行UPDATE。在MySQL客户端看起来就是两行受影响。这个设计初看很反直觉但理解了内部流程就顺理成章了。如果你只是执行SQL并打印rows affected可能不会太在意这些差异。但如果是编写代码通过受影响行数判断“这条数据到底是新增还是更新”就必须搞清楚这个规则。比如在做数据同步任务时你想根据返回值更新日志1代表新增2代表更新0代表“数据没有变化、可以跳过”。这时候在业务代码里用这个返回值做判断能让同步任务的可观测性强很多。5.2 JDBC与MyBatis里的表现useAffectedRows参数决定了你看什么JDBC连接MySQL时executeUpdate()或MyBatis的int update()返回值同样受这个影响但还有一层因素MySQL Connector/J默认使用CLIENT_FOUND_ROWS标志此时executeUpdate返回的是“找到的行数”而不是“受影响的行数”。这就带来一个经典坑假设有一条记录已经存在且所有字段和INSERT里的值完全一样SQL执行后确实匹配到了那一行但没有任何字段被修改。在默认的JDBC配置下返回值可能是1找到它了但没改而不是0只有在连接URL里加上useAffectedRowstrue返回的才是真正受影响的0。jdbc:mysql://localhost:3306/db?useAffectedRowstrue如果你要靠返回值区分“插入/更新/没有变化”强烈建议检查一下项目里MySQL驱动的版本和连接参数。MyBatis里大多数情况下只是拿返回值判断“是否影响了行数”那问题不大但如果业务的幂等逻辑依赖这个返回值这个细节就很关键了。这个坑属于不踩一次很难注意到踩一次就记一辈子的类型。5.3 MySQL 8.0.20之后VALUES()函数被标记废弃从MySQL 8.0.20开始官方把ON DUPLICATE KEY UPDATE子句中的VALUES()函数标记为废弃并推荐使用新的行别名/列别名语法。因为VALUES()作为函数名容易让人混淆在标准SQL里它本来有另外的含义。新写法有两种-- 行别名方式 INSERT INTO t (id, a, b) VALUES (1, 10, 20) AS new ON DUPLICATE KEY UPDATE a new.a, b new.b; -- 列别名方式 INSERT INTO t (id, a, b) VALUES (1, 10, 20) AS new(x, y) ON DUPLICATE KEY UPDATE a x, b y;这两种写法的可读性明显更好尤其在使用批量VALUES时你能直接看到new.a、new.b代表的是“这一行试图写入的值”。虽然8.0.20版本里VALUES()函数还没有被移除但官方废弃意味着后续大版本有可能直接删除。老项目升级到8.0时建议顺手把这类SQL全部替换成别名写法省得以后被迫批量改。如果你维护的是用MySQL 5.7做线上的老项目且短期内没有升级计划那继续用VALUES()也没有问题但新项目建议直接按新语法写。6. 生产环境实测锁、死锁、复制延迟与替代方案取舍6.1 并发写同一个唯一键时为什么会死锁前面讲的都是功能层面的用法生产环境真正考验人的是并发行为。ON DUPLICATE KEY UPDATE在InnoDB中的执行逻辑并不是“只读旧值再改新值”这么简单。检测到唯一键冲突后InnoDB会对冲突的记录加锁执行UPDATE时还需要获得排他锁这就引出了并发热点问题。一个很常见的原因场景是多个事务同时插入同一个唯一键比如同一部剧的播放量、同一商品的库存冲突检测的瞬间多个事务都在等待这把锁。稍微复杂一点的场景中事务A持有了行X的某把锁又需要更新行Y事务B持有了行Y的锁又需要更新行X于是形成死锁。数据库会选择回滚其中的一个事务应用层收到的错误码通常是1213 Deadlock found when trying to get lock; try restarting transaction。应对策略没有银弹最务实的几条对热点唯一键的并发写入考虑在应用层做“单key串行化”比如对同一个商品ID加分布式锁死锁概率高时捕获1213错误并做有限次重试重试时重新构造事务批量写入时控制单事务大小长事务持有锁的时间越长死锁窗口越大把唯一键冲突检测尽量放在写量少的一端减少锁竞争频率。6.2 更新列本身也是唯一键时的连环错误另一个容易被忽略的坑是ON DUPLICATE KEY UPDATE的UPDATE子句里更新的字段如果是另一个唯一键那么更新后的新值可能再次撞上其他行。比如有三条数据id1的usernameaid2的usernameb。现在执行INSERT INTO user (id, username, email) VALUES (2, a, new_emailexample.com) ON DUPLICATE KEY UPDATE username VALUES(username), email VALUES(email);id2这条数据已存在进入UPDATE分支尝试把id2的username改成a结果id1已经占了aMySQL直接报Duplicate entry a for key username整条语句回滚。这就不是简单的覆盖问题了而是你的更新操作和现有数据产生了新的冲突。所以在设计表结构时如果某列是唯一键就要慎重考虑它是否需要出现在UPDATE子句中。很多业务场景下的唯一键订单号、手机号、设备ID通常是“绑定后不变”的字段更新它们的需求本身就是异常。如果确实存在“用户改绑手机号”这类需求不建议直接用这个语法去改唯一键应该走更严谨的“先校验再UPDATE”流程。6.3 三种upsert方案的对比与最终选型最后把三种方案放在一起做一个完整对比方便你根据场景做决定对比维度INSERT IGNOREREPLACE INTOON DUPLICATE KEY UPDATE已存在时行为忽略错误不操作先删旧行再插新行更新指定字段不存在时行为插入插入插入更新部分字段不支持不支持整行重建支持灵活控制触发DELETE触发器不会会不会自增ID变化不消耗明显消耗ID必跳冲突时可能消耗修改唯一键时的风险较低较低较高需注意连锁冲突返回影响行数0或1通常21或2或0适合场景纯去重写入整行覆盖可接受删插绝大多数“存在即更新”从我自己的使用习惯来说能不用REPLACE INTO就不用除非业务能接受整行删除和自增ID跳动。INSERT IGNORE只在我确认只插不更新、并且能接受其他错误被忽略时使用。剩下的场景一律ON DUPLICATE KEY UPDATE。生产环境里真正需要留意的还是设计问题建表时算好业务唯一键UPSERT语句只更新该更新的字段批量操作控制好行数事务尽量短。当这些前提具备后ON DUPLICATE KEY UPDATE基本就是兼顾性能、语义和代码简洁度的最佳选择。最后再分享一个我个人的习惯每次写完这类SQL我都会先看执行计划确认有没有命中相关唯一索引再跑一次SHOW WARNINGS看有没有废弃警告。生产环境升级MySQL版本前也会把涉及VALUES()的语句和受影响行数判断的逻辑一起纳入回归测试。细节决定成败这句在数据库开发这里是真的管用。