尧图网站建设 
1. 别再迷信 saveOrUpdateBatch它到底帮你做了多少事我用 MyBatis-Plus 也有几年了早期做数据同步、批量导入这类需求的时候第一个想到的必然是saveOrUpdateBatch。这个 API 设计得太顺手了传一个 List 进去它帮你判断主键存在就 update不存在就 insert看起来完美。但后来在生产环境里接了一个每小时同步几万条库存数据的任务跑了几轮之后我盯了一眼监控发现数据库的 TPS 高得离谱SQL 日志刷屏刷得根本翻不到头。问题就出在这个 API 的“隐藏成本”上。很多人以为saveOrUpdateBatch会像 JDBC 的executeBatch一样把一批 SQL 打包发给数据库。实际上不是。你去翻一下 MyBatis-Plus 的源码就知道saveOrUpdateBatch内部是逐个遍历实体对象对每一条数据先执行一次selectById其实是通过sqlSession查一遍看记录存不存在然后根据结果决定发一条updateById还是insert。也就是说如果列表里有 10000 条数据它可能会产生 10000 次查询外加 10000 次写入操作每一轮都是完整的网络往返、SQL 解析、事务日志写入、行锁竞争。这就像你要寄 100 个快递正常的做法是叫一辆车一口气拉走但saveOrUpdateBatch是让你叫 100 辆三轮车每辆只拉一个包裹。三轮车虽然便宜但 100 辆在路上堵成一团效率能高才怪。当然这么说不是要全盘否定它——数据量小、频率低、逻辑简单的时候它确实是省代码的利器。但一旦数据量上来了它就成了性能瓶颈。那有没有既能保留 MyBatis-Plus 的开发便利性又能在 MySQL 端只发一条 SQL 把批量新增和更新一起做掉的方案有而且不复杂——用 MySQL 原生的INSERT ... ON DUPLICATE KEY UPDATE语法配合 MyBatis 的注解或 XML 手写一次批量 upsert。下面我详细拆解这套方案的实现思路、参数控制和生产环境实测数据。2. 一条 SQL 批量增改的核心逻辑与实现2.1 MySQL 的ON DUPLICATE KEY UPDATE到底是个什么机制先搞清楚底层的语法机制。MySQL 里有唯一索引和主键两种“唯一性约束”当执行INSERT语句时如果插入的数据触碰到了已存在的唯一索引或主键值默认会报一个Duplicate entry错误。而ON DUPLICATE KEY UPDATE就是在冲突发生时不再报错而是把原来那条 INSERT 变成一条 UPDATE只更新你指定的字段。一条典型的语句长这样INSERT INTO product_stock (id, product_code, stock_qty, update_time) VALUES (1, P1001, 20, NOW()), (2, P1002, 30, NOW()), (3, P1003, 15, NOW()) ON DUPLICATE KEY UPDATE stock_qty VALUES(stock_qty), update_time VALUES(update_time);注意一个关键点这条语句最终只会访问一次数据库连接、执行一次 SQL 解析、在事务里顺序检查每一行的唯一性冲突。跟逐条执行相比少掉了上万次网络往返和 SQL 解析开销这才是性能提升的本质。VALUES()函数在 INSERT ... ON DUPLICATE KEY UPDATE 语句中代表“INSERT 部分提供的值”意思是冲突发生时把stock_qty更新为 SQL 里给的那个新值。不过这里有个大坑MySQL 8.0.20 开始官方明确标记VALUES()函数在ON DUPLICATE KEY UPDATE中已过时建议改用“新值直接引用别名”的写法。我用 8.0 实测过老写法仍然能用但既然官方不推荐了新项目建议直接用别名语法INSERT INTO product_stock (id, product_code, stock_qty, update_time) VALUES (1, P1001, 20, NOW()), (2, P1002, 30, NOW()) AS new ON DUPLICATE KEY UPDATE stock_qty new.stock_qty, update_time new.update_time;AS new相当于给这段待插入的数据起了一个表别名后续 UPDATE 子句可以引用new.字段名拿到本次想写入的值。这个写法在 8.0.19 及以后版本都可以用比VALUES()更直观语义也更清晰。2.2 在 MyBatis-Plus 中通过注解写批量 upsert既然 MyBatis-Plus 的通用 Service 和 BaseMapper 都不直接暴露批量 upsert 方法最轻量的做法是在你的 Mapper 接口上加一个自定义方法用Insert注解配合script标签构建动态 SQL。public interface ProductStockMapper extends BaseMapperProductStock { Insert(script INSERT INTO product_stock (id, product_code, stock_qty, update_time) VALUES foreach collectionlist itemitem separator, (#{item.id}, #{item.productCode}, #{item.stockQty}, NOW()) /foreach AS new ON DUPLICATE KEY UPDATE stock_qty new.stock_qty, update_time NOW() /script) int batchUpsert(Param(list) ListProductStock list); }这里有几个细节要注意。ON DUPLICATE KEY UPDATE子句放在/foreach之后整个 SQL 拼接出来是完整的一条 INSERT 语句。AS new的位置紧跟在 VALUES 列表后面不是放在表名后面这个位置写错了会直接报 SQL 语法错误。还有update_time NOW()这种和行数据无关的字段直接写死常量就可以不需要从实体里取值。如果你不想用注解也可以把这段 SQL 放到 Mapper XML 里写效果一样。XML 的好处是 SQL 和 Java 代码分离后续 DBA 要审查 SQL 比较方便。但注解方式的优势更明显——你不需要额外维护 XML 文件Mapper 接口里就能看到完整的 SQL 逻辑对项目结构简单的小团队来说更友好。2.3 老版本 MySQL 不能用别名时怎么办如果你还在维护一个 MySQL 5.7 的老项目8.0 引入的AS new别名语法是用不了的。这时候有两个选择方案一——退回VALUES()写法ON DUPLICATE KEY UPDATE stock_qty VALUES(stock_qty), update_time NOW();尽管 MySQL 8.0.20 弃用了这个语法5.7 全系列都可以正常使用。如果你的数据库版本没法升级别纠结直接用。方案二——用 CASE WHEN 配合批量 UPDATE 分两步走先一次性查出库中已存在的记录把主键集合拿到内存里然后对列表做一次 partition已存在的走UPDATE批量语句不存在的走INSERT批量语句。两步各发一条 SQL同样能避开逐条操作。这个方案的优势是不依赖ON DUPLICATE KEY UPDATE的版本限制而且可以实现“只更新目标字段”的精细控制。代价是你的 Java 代码里需要多写几行分组逻辑SQL 也分成两条。两条路我都实测过。我的建议是只要是 8.0 以上的库优先用别名写法5.7 的老库用不着冒险改语法直接VALUES()就行官方弃用归弃用5.7 离 EOL 也不远了。到了 8.0 再顺手改成新语法。3. 批量 upsert 的参数选择与边界控制3.1 批量大小怎么定500 条还是 5000 条这是使用批量 upsert 的第一个分水岭问题。批量太小SQL 拼接次数多省不了多少网络往返批量太大单条 SQL 文本过长会超过 MySQL 的max_allowed_packet限制直接被拒。max_allowed_packet默认值在不同版本不一样5.7 默认是 4MB8.0 默认是 64MB。你可以用这条命令查看SHOW VARIABLES LIKE %max_allowed_packet%;假设你的表有 10 个字段每条记录拼接出来的 VALUES 片段大约 200 字节左右。500 条记录拼出来也就是 100KB离 4MB 还很远。但如果你把 5000 条、甚至 10000 条拼到一条 SQL 里文本可能达到 2MB 以上5.7 的默认配置下就有触发超限风险了。我自己的经验值是单批控制在 500~1000 条之间。原因不光是 SQL 文本长度还有事务时长。MySQL 的 InnoDB 在一条 SQL 里插入/更新多行时会全程持有必要的行锁和间隙锁单条 SQL 涉及的行数越多锁范围越大、持有时间越久。在并发写入场景里长事务会让别的更新阻塞时间变长更容易出现死锁。批量大小还要结合字段数量调整。字段少的表可以适当放大到 2000 条字段特别宽的大表建议控制在 300 条以内。做个简单换算500 条 × 单条 200 字节 100KB一条 SQL 发送时间在局域网内基本可以忽略到了 5000 条就是 1MB虽然大多数情况也能过但万一表里有个text字段或者大varchar拼接出来直接膨胀好几倍。3.2 唯一索引是前提主键冲突同样触发更新ON DUPLICATE KEY UPDATE这个名字里的 “DUPLICATE”指的是任何一个唯一索引冲突不只是主键冲突。所以你要确保表上有对应的唯一索引否则批量 upsert 就退化成纯粹的 INSERT数据就会越插越多。有一个非常常见的翻车场景表里的主键是自增 ID业务上真正标识唯一性的是业务单号比如order_no但你没有给order_no加唯一索引。你以为用了ON DUPLICATE KEY UPDATE就能做到“存在就更新、不存在就插入”实际上因为order_no不唯一每次插入都生成新的自增主键表里瞬间多出一堆重复订单而且不会出现任何报错。所以动手之前先确认你的唯一性判定靠的是主键还是业务唯一索引如果是业务唯一索引请确保它真实存在。我自己的习惯是在设计表结构阶段就把业务唯一键建好比如uk_order_no这个索引不光为 upsert 服务也能兜底防止其他途径写入的重复数据。还有一个容易被忽略的点联合唯一索引。比如说你要对(product_code, warehouse_id)两个字段的维度做库存更新唯一的判定依据是“同一个仓库的同一个商品只一条库存记录”。这时候必须建联合唯一索引ALTER TABLE product_stock ADD UNIQUE KEY uk_product_warehouse (product_code, warehouse_id);SQL 里的 INSERT 列不需要包含主键只要包含这三个维度字段和一个数量字段即可。冲突时根据联合唯一索引触发更新执行逻辑和单列唯一索引完全一致。3.3 事务边界与数据库连接配置批量 upsert 默认是在 MyBatis 的SqlSessionTemplate管理下执行的如果没有显式声明TransactionalMyBatis 不会自动开启一个包住整批操作的事务执行完自动提交。这里有一个常见的在线更新场景你希望这批数据要么全部成功、要么全部回滚那就必须加上TransactionalTransactional(rollbackFor Exception.class) public void syncStock(ListProductStock list) { int rows productStockMapper.batchUpsert(list); // 其他业务操作 }加了事务之后批量操作里任何一行触发异常比如非空字段传了 NULL整批都会回滚不会出现一部分更新成功、一部分失败的脏数据。代价是事务持锁时间变长所以事务里除了批处理本身不要塞太多无关操作否则锁等待时间被拉满并发一高就炸。连接配置方面还有两个参数直接影响批量操作的真实性能。第一是 JDBC URL 里要不要加rewriteBatchedStatementstrue。这个参数主要影响 JDBC 原生executeBatch的行为让驱动把多条INSERT重写成一条多值的 INSERT 再发给数据库。但对自定义拼接好的单条多值 INSERT 来说它不受这个参数影响加不加都一样。如果你的批量方案用的是 JDBC Batch API这个参数必须加上。第二是useServerPrepStmtsfalse默认就是 false一般不用动。这两个参数经常被人混淆我在网上看到不少帖子说 MyBatis-Plus 批量慢是因为没加rewriteBatchedStatements其实 MyBatis-Plus 的saveOrUpdateBatch根本不是走 JDBC Batch 的所以加了也没用。这一点先搞清楚后面排查问题才不会走弯路。3.4 逻辑删除字段与自动填充字段的坑如果你的表用了 MyBatis-Plus 的逻辑删除TableLogic批量 upsert 会有一个隐蔽的问题逻辑删除字段的默认值插入时是 0未删除但如果你要更新一条逻辑删除过的记录ON DUPLICATE KEY UPDATE默认不会帮你把删除标识改回来除非你在 UPDATE 子句里显式写上deleted 0。更麻烦的是MyBatis-Plus 的 BaseMapper 在做selectById时自动过滤逻辑删除记录。这意味着如果你先查再决定 insert 还是 update逻辑删除的记录会被“误判”为不存在然后你走 INSERT 分支但库里的唯一索引还占着位置直接触发 Duplicate Key 报错。批量 upsert 恰好能规避这个坑不依赖先查而是在一条 SQL 里把冲突的更新逻辑自动拦下来。但注意你的ON DUPLICATE KEY UPDATE子句必须显式加deleted 0否则冲突记录会一直保持逻辑删除状态。自动填充字段也要小心。MyBatis-Plus 的TableField(fill FieldFill.INSERT)和FieldFill.INSERT_UPDATE只在它自己的insert/update方法里生效你手写 SQL 走 Mapper 方法时MetaObjectHandler 不会介入。也就是说create_time、update_time这种字段必须自己在 SQL 里写上NOW()或者从实体取值不要在实体里配了自动填充就以为万事大吉。4. 实测对比saveOrUpdateBatch 与一条 SQL 的差距4.1 测试环境与方法我用一台本地开发机做了对比测试环境如下数据库MySQL 8.0.33单实例默认配置数据表模拟库存表10 个字段含一个主键、一个业务唯一索引数据量单批 2000 条记录一半主键不存在纯插入一半主键已存在触发更新JDK17MyBatis-Plus 3.5.3.2测试方法是分别调用saveOrUpdateBatch和自定义batchUpsert各跑 5 次取平均耗时同时记录 MySQL 层的 SQL 执行次数和日志量。4.2 耗时与 SQL 执行量对比方案总耗时ms数据库往返次数单次事务时长日志量saveOrUpdateBatch4826000 次单条多次提交事务边界频繁刷屏 2000 行自定义 batchUpsert861 次单条 SQLInnoDB 一轮执行1 行比例慢约 5.6 倍6000 倍——测试现场有个非常直观的现象saveOrUpdateBatch跑的时候 MySQL 的Com_select、Com_insert、Com_update三个计数器疯狂跳动而自定义批量方案跑的时候三个计数器只有极小幅度的变化。有人可能会说 5.6 倍的差距也没多大2000 条数据 400ms 和 80ms 的区别好像可以接受。但你要把场景放大。按这个比例推算如果一次同步 5 万条数据saveOrUpdateBatch可能耗时 10 秒以上而且期间数据库的并发连接被大量占用、主从延迟明显拉大、行锁竞争加剧批量 upsert 同样 5 万条数据只要 2 秒左右。在生产环境里后者对数据库的影响几乎可以忽略。4.3 两者的适用场景到底怎么切我整理了一张场景判断表团队里最近做新需求我基本都是按这个标准选型条件saveOrUpdateBatch自定义批量 upsert单批数据量 ≤ 100 条推荐代码省心可选看团队习惯单批数据量 100~2000 条勉强可用监控压力大推荐单批数据量 2000 条强烈不推荐必须业务本身要求逐条处理回调适用不适用表字段极多且字段经常动态变化适用不适用SQL 写起来较长对 MySQL 版本有 5.7 兼容要求适用用 VALUES() 写法也可需要补充的是saveOrUpdateBatch并不是一无是处。它的核心价值在于“自动根据主键判断新增还是更新”这个判断逻辑是框架帮你做的。如果你的数据量常年小于 100 条、执行频率不高、分页查询和详情查询是主流场景那么用框架能力继续省事完全没问题。我自己的实践原则是批量写入超过 200 条就换个思路别图省事。5. 常见问题与排查技巧实录5.1 SQL 超过 max_allowed_packet 被拒现象调用batchUpsert时抛出异常错误信息里通常带packet too large字样。原因单条 SQL 拼接长度超过 MySQL 配置的max_allowed_packet。前面说过 5.7 默认 4MB8.0 默认 64MB。如果表字段较多、批量值较大很容易踩线。排查方法先执行SHOW VARIABLES LIKE %max_allowed_packet%确认当前值然后把批量大小调小或者手动改配置SET GLOBAL max_allowed_packet 64 * 1024 * 1024;注意SET GLOBAL只对之后新建的连接生效已经在连接池里的连接不受影响修改完最好重启一下应用或让连接池重建连接。我的建议与其不断放大 SQL 长度上限不如控制单批条数。批量第一原则是控制事务大小和网络包体积不是一味追求“单批越多越好”。5.2 自增主键跳号现象批量 upsert 跑完之后表的 AUTO_INCREMENT 值大幅跳增主键之间出现大段空洞。原因INSERT ... ON DUPLICATE KEY UPDATE在插入冲突时也会消耗自增 ID。InnoDB 的默认行为是预分配一批自增值即使这一行最终没真正插入走了更新分支自增计数照样增长。影响如果业务层对主键连续性有要求比如要求主键就是展示编号这个行为会让你很难看。解决办法是调整业务设计不要把自增主键当作业务编号对外展示如果确实需要连续编号就别用自增主键改用应用层生成编号。实操提醒如果你用的是 8.0 版本可以通过设置innodb_autoinc_lock_mode0来缓解部分场景的跳号但一般不建议动这个参数它会影响并发插入性能。治本方案还是接受自增主键不连续这个事实。5.3 批量更新没有把某些字段置空现象某字段在传入数据里是 null批量更新后库里的老值没有被清空还是原来的旧值。原因ON DUPLICATE KEY UPDATE的语义是“你不指定更新哪个字段就保持原值”。假设你只写了stock_qty new.stock_qty其他字段如remark没写那么remark在冲突发生后不会被更新成新值或 NULL。解决办法根据业务需求在 UPDATE 子句里显式列出所有需要更新的字段。如果某个字段允许置空直接写remark new.remark或remark NULL。不需要更新的字段比如 create_time就原地不动这反而是批量 upsert 的一个优点——可以精确控制更新范围不像直接覆盖整行那样把所有字段都改一遍。5.4 并发写入同一批键导致死锁现象高并发场景下两个线程同时调用批量 upsert 更新同一组主键或唯一键偶尔抛出 Deadlock 异常事务回滚。原因InnoDB 在检查唯一索引冲突时需要加锁不同的行被插入和更新的顺序不一致导致两个事务互相等待对方的锁。排查思路打开 InnoDB 死锁日志查看最近一次死锁的锁等待关系检查两条 SQL 的行处理顺序是否一致。批量 upsert 的数据在 SQL 里的排列顺序最好按照主键或唯一键排序所有线程用相同的顺序处理同一批数据能大幅降低死锁概率。经验值如果业务要求同一批数据的高频并发更新建议加一个分布式锁或者把批量任务串行化。批量场景本身就是重写操作不该用乐观并发思路去扛。5.5 affected rows 语义带来的返回行数困惑现象调用batchUpsert后返回的 int 结果行数和预期不符比如 2000 条数据明明期望返回 2000结果却返回了 4000 或 3000。原因MySQL 官方文档里INSERT ... ON DUPLICATE KEY UPDATE的影响行数规则是插入新记录计 1 行已有记录触发更新计 2 行如果更新前后值没有变化计 0 行。所以“批量更新”部分的行数是双倍计算的。影响如果你用返回行数来做业务成功判定比如if (rows ! list.size())当成异常处理就会误判。我的习惯是只关心“是否大于 0”或者“有没有抛异常”不做精确的行数相等校验。如果真的需要精确确认每一条是否成功需要逐条比对那就不适合用批量方案。5.6 千万级数据的批量同步策略单条 SQL 批量 upsert 也不是银弹。如果你要同步的是几十万甚至上百万条数据就算一条 SQL 能装下事务也会大到让 InnoDB 产生大量 UNDO 日志、锁占用、甚至阻塞从库同步。我的做法是分片 分批 多线程先把总数据集按主键或唯一键排序切成多个分片每个分片内再按 500~1000 条一个批次循环调用batchUpsert每批操作放在单独事务里单独提交用固定线程池控制并发度比如 4~6 个线程避免一下把数据库连接池打满记录每批的处理状态支持失败重试重试只重放失败的那一批。这套方案实测处理 50 万条库存更新总耗时约 20~30 秒而且对线上主库的冲击比saveOrUpdateBatch跑 50 万条可能要跑十几分钟且拖垮监控要小得多。如果你的项目有阿里云或腾讯云的运维后台还可以观察它在主从延迟上的表现——批量 upsert 方案下主从延迟几乎可以忽略。6. 我现阶段的做法与后续可扩展的方向我自己团队现在的数据库操作规范里对批量增改类需求的基本策略是优先评估数据规模100 条以内允许用 MyBatis-Plus 内置 API200 条以上统一走自定义批量 upsert。为了减少重复代码我们把批量 upsert 的 SQL 抽成了一个公共方法模板针对不同表只需要改表名和字段列表新业务接入成本很小。MySQL 8.0 以后还有几个能锦上添花的特性比如原子 DDL、公用表表达式CTE。如果未来要处理更复杂的批量逻辑——比如需要先关联另一张表的汇总数据再更新目标表——可以尝试用 CTE UPDATE 的写法WITH tmp AS ( SELECT id, SUM(qty) AS total_qty FROM source_table GROUP BY id ) UPDATE product_stock ps JOIN tmp ON ps.id tmp.id SET ps.stock_qty tmp.total_qty;这种写法可以把“查-算-更”压缩到一步比先把数据拉到应用层再组装 SQL 更高效。它适用的场景和批量 upsert 不同但思路本质一致能交给数据库在一个语句里完成的事就不要让 Java 应用反复来回折腾网络。最后再分享一个我踩过的坑不管方案设计得多合理批量 SQL 上线前一定先用测试库跑一遍重点看计划里的索引命中情况和返回影响行数。我曾经因为一个字段漏选了唯一索引导致批量 upsert 在测试环境跑得飞快、上生产之后数据翻倍排查了大半天才意识到是索引缺失。所有批量方案的前提都是表结构可靠这个基础不牢性能优化做得再多都是白搭。 MyBatis-Plus批量操作优化：用ON DUPLICATE KEY UPDATE替代saveOrUpdateBatch
1. 别再迷信 saveOrUpdateBatch它到底帮你做了多少事我用 MyBatis-Plus 也有几年了早期做数据同步、批量导入这类需求的时候第一个想到的必然是saveOrUpdateBatch。这个 API 设计得太顺手了传一个 List 进去它帮你判断主键存在就 update不存在就 insert看起来完美。但后来在生产环境里接了一个每小时同步几万条库存数据的任务跑了几轮之后我盯了一眼监控发现数据库的 TPS 高得离谱SQL 日志刷屏刷得根本翻不到头。问题就出在这个 API 的“隐藏成本”上。很多人以为saveOrUpdateBatch会像 JDBC 的executeBatch一样把一批 SQL 打包发给数据库。实际上不是。你去翻一下 MyBatis-Plus 的源码就知道saveOrUpdateBatch内部是逐个遍历实体对象对每一条数据先执行一次selectById其实是通过sqlSession查一遍看记录存不存在然后根据结果决定发一条updateById还是insert。也就是说如果列表里有 10000 条数据它可能会产生 10000 次查询外加 10000 次写入操作每一轮都是完整的网络往返、SQL 解析、事务日志写入、行锁竞争。这就像你要寄 100 个快递正常的做法是叫一辆车一口气拉走但saveOrUpdateBatch是让你叫 100 辆三轮车每辆只拉一个包裹。三轮车虽然便宜但 100 辆在路上堵成一团效率能高才怪。当然这么说不是要全盘否定它——数据量小、频率低、逻辑简单的时候它确实是省代码的利器。但一旦数据量上来了它就成了性能瓶颈。那有没有既能保留 MyBatis-Plus 的开发便利性又能在 MySQL 端只发一条 SQL 把批量新增和更新一起做掉的方案有而且不复杂——用 MySQL 原生的INSERT ... ON DUPLICATE KEY UPDATE语法配合 MyBatis 的注解或 XML 手写一次批量 upsert。下面我详细拆解这套方案的实现思路、参数控制和生产环境实测数据。2. 一条 SQL 批量增改的核心逻辑与实现2.1 MySQL 的ON DUPLICATE KEY UPDATE到底是个什么机制先搞清楚底层的语法机制。MySQL 里有唯一索引和主键两种“唯一性约束”当执行INSERT语句时如果插入的数据触碰到了已存在的唯一索引或主键值默认会报一个Duplicate entry错误。而ON DUPLICATE KEY UPDATE就是在冲突发生时不再报错而是把原来那条 INSERT 变成一条 UPDATE只更新你指定的字段。一条典型的语句长这样INSERT INTO product_stock (id, product_code, stock_qty, update_time) VALUES (1, P1001, 20, NOW()), (2, P1002, 30, NOW()), (3, P1003, 15, NOW()) ON DUPLICATE KEY UPDATE stock_qty VALUES(stock_qty), update_time VALUES(update_time);注意一个关键点这条语句最终只会访问一次数据库连接、执行一次 SQL 解析、在事务里顺序检查每一行的唯一性冲突。跟逐条执行相比少掉了上万次网络往返和 SQL 解析开销这才是性能提升的本质。VALUES()函数在 INSERT ... ON DUPLICATE KEY UPDATE 语句中代表“INSERT 部分提供的值”意思是冲突发生时把stock_qty更新为 SQL 里给的那个新值。不过这里有个大坑MySQL 8.0.20 开始官方明确标记VALUES()函数在ON DUPLICATE KEY UPDATE中已过时建议改用“新值直接引用别名”的写法。我用 8.0 实测过老写法仍然能用但既然官方不推荐了新项目建议直接用别名语法INSERT INTO product_stock (id, product_code, stock_qty, update_time) VALUES (1, P1001, 20, NOW()), (2, P1002, 30, NOW()) AS new ON DUPLICATE KEY UPDATE stock_qty new.stock_qty, update_time new.update_time;AS new相当于给这段待插入的数据起了一个表别名后续 UPDATE 子句可以引用new.字段名拿到本次想写入的值。这个写法在 8.0.19 及以后版本都可以用比VALUES()更直观语义也更清晰。2.2 在 MyBatis-Plus 中通过注解写批量 upsert既然 MyBatis-Plus 的通用 Service 和 BaseMapper 都不直接暴露批量 upsert 方法最轻量的做法是在你的 Mapper 接口上加一个自定义方法用Insert注解配合script标签构建动态 SQL。public interface ProductStockMapper extends BaseMapperProductStock { Insert(script INSERT INTO product_stock (id, product_code, stock_qty, update_time) VALUES foreach collectionlist itemitem separator, (#{item.id}, #{item.productCode}, #{item.stockQty}, NOW()) /foreach AS new ON DUPLICATE KEY UPDATE stock_qty new.stock_qty, update_time NOW() /script) int batchUpsert(Param(list) ListProductStock list); }这里有几个细节要注意。ON DUPLICATE KEY UPDATE子句放在/foreach之后整个 SQL 拼接出来是完整的一条 INSERT 语句。AS new的位置紧跟在 VALUES 列表后面不是放在表名后面这个位置写错了会直接报 SQL 语法错误。还有update_time NOW()这种和行数据无关的字段直接写死常量就可以不需要从实体里取值。如果你不想用注解也可以把这段 SQL 放到 Mapper XML 里写效果一样。XML 的好处是 SQL 和 Java 代码分离后续 DBA 要审查 SQL 比较方便。但注解方式的优势更明显——你不需要额外维护 XML 文件Mapper 接口里就能看到完整的 SQL 逻辑对项目结构简单的小团队来说更友好。2.3 老版本 MySQL 不能用别名时怎么办如果你还在维护一个 MySQL 5.7 的老项目8.0 引入的AS new别名语法是用不了的。这时候有两个选择方案一——退回VALUES()写法ON DUPLICATE KEY UPDATE stock_qty VALUES(stock_qty), update_time NOW();尽管 MySQL 8.0.20 弃用了这个语法5.7 全系列都可以正常使用。如果你的数据库版本没法升级别纠结直接用。方案二——用 CASE WHEN 配合批量 UPDATE 分两步走先一次性查出库中已存在的记录把主键集合拿到内存里然后对列表做一次 partition已存在的走UPDATE批量语句不存在的走INSERT批量语句。两步各发一条 SQL同样能避开逐条操作。这个方案的优势是不依赖ON DUPLICATE KEY UPDATE的版本限制而且可以实现“只更新目标字段”的精细控制。代价是你的 Java 代码里需要多写几行分组逻辑SQL 也分成两条。两条路我都实测过。我的建议是只要是 8.0 以上的库优先用别名写法5.7 的老库用不着冒险改语法直接VALUES()就行官方弃用归弃用5.7 离 EOL 也不远了。到了 8.0 再顺手改成新语法。3. 批量 upsert 的参数选择与边界控制3.1 批量大小怎么定500 条还是 5000 条这是使用批量 upsert 的第一个分水岭问题。批量太小SQL 拼接次数多省不了多少网络往返批量太大单条 SQL 文本过长会超过 MySQL 的max_allowed_packet限制直接被拒。max_allowed_packet默认值在不同版本不一样5.7 默认是 4MB8.0 默认是 64MB。你可以用这条命令查看SHOW VARIABLES LIKE %max_allowed_packet%;假设你的表有 10 个字段每条记录拼接出来的 VALUES 片段大约 200 字节左右。500 条记录拼出来也就是 100KB离 4MB 还很远。但如果你把 5000 条、甚至 10000 条拼到一条 SQL 里文本可能达到 2MB 以上5.7 的默认配置下就有触发超限风险了。我自己的经验值是单批控制在 500~1000 条之间。原因不光是 SQL 文本长度还有事务时长。MySQL 的 InnoDB 在一条 SQL 里插入/更新多行时会全程持有必要的行锁和间隙锁单条 SQL 涉及的行数越多锁范围越大、持有时间越久。在并发写入场景里长事务会让别的更新阻塞时间变长更容易出现死锁。批量大小还要结合字段数量调整。字段少的表可以适当放大到 2000 条字段特别宽的大表建议控制在 300 条以内。做个简单换算500 条 × 单条 200 字节 100KB一条 SQL 发送时间在局域网内基本可以忽略到了 5000 条就是 1MB虽然大多数情况也能过但万一表里有个text字段或者大varchar拼接出来直接膨胀好几倍。3.2 唯一索引是前提主键冲突同样触发更新ON DUPLICATE KEY UPDATE这个名字里的 “DUPLICATE”指的是任何一个唯一索引冲突不只是主键冲突。所以你要确保表上有对应的唯一索引否则批量 upsert 就退化成纯粹的 INSERT数据就会越插越多。有一个非常常见的翻车场景表里的主键是自增 ID业务上真正标识唯一性的是业务单号比如order_no但你没有给order_no加唯一索引。你以为用了ON DUPLICATE KEY UPDATE就能做到“存在就更新、不存在就插入”实际上因为order_no不唯一每次插入都生成新的自增主键表里瞬间多出一堆重复订单而且不会出现任何报错。所以动手之前先确认你的唯一性判定靠的是主键还是业务唯一索引如果是业务唯一索引请确保它真实存在。我自己的习惯是在设计表结构阶段就把业务唯一键建好比如uk_order_no这个索引不光为 upsert 服务也能兜底防止其他途径写入的重复数据。还有一个容易被忽略的点联合唯一索引。比如说你要对(product_code, warehouse_id)两个字段的维度做库存更新唯一的判定依据是“同一个仓库的同一个商品只一条库存记录”。这时候必须建联合唯一索引ALTER TABLE product_stock ADD UNIQUE KEY uk_product_warehouse (product_code, warehouse_id);SQL 里的 INSERT 列不需要包含主键只要包含这三个维度字段和一个数量字段即可。冲突时根据联合唯一索引触发更新执行逻辑和单列唯一索引完全一致。3.3 事务边界与数据库连接配置批量 upsert 默认是在 MyBatis 的SqlSessionTemplate管理下执行的如果没有显式声明TransactionalMyBatis 不会自动开启一个包住整批操作的事务执行完自动提交。这里有一个常见的在线更新场景你希望这批数据要么全部成功、要么全部回滚那就必须加上TransactionalTransactional(rollbackFor Exception.class) public void syncStock(ListProductStock list) { int rows productStockMapper.batchUpsert(list); // 其他业务操作 }加了事务之后批量操作里任何一行触发异常比如非空字段传了 NULL整批都会回滚不会出现一部分更新成功、一部分失败的脏数据。代价是事务持锁时间变长所以事务里除了批处理本身不要塞太多无关操作否则锁等待时间被拉满并发一高就炸。连接配置方面还有两个参数直接影响批量操作的真实性能。第一是 JDBC URL 里要不要加rewriteBatchedStatementstrue。这个参数主要影响 JDBC 原生executeBatch的行为让驱动把多条INSERT重写成一条多值的 INSERT 再发给数据库。但对自定义拼接好的单条多值 INSERT 来说它不受这个参数影响加不加都一样。如果你的批量方案用的是 JDBC Batch API这个参数必须加上。第二是useServerPrepStmtsfalse默认就是 false一般不用动。这两个参数经常被人混淆我在网上看到不少帖子说 MyBatis-Plus 批量慢是因为没加rewriteBatchedStatements其实 MyBatis-Plus 的saveOrUpdateBatch根本不是走 JDBC Batch 的所以加了也没用。这一点先搞清楚后面排查问题才不会走弯路。3.4 逻辑删除字段与自动填充字段的坑如果你的表用了 MyBatis-Plus 的逻辑删除TableLogic批量 upsert 会有一个隐蔽的问题逻辑删除字段的默认值插入时是 0未删除但如果你要更新一条逻辑删除过的记录ON DUPLICATE KEY UPDATE默认不会帮你把删除标识改回来除非你在 UPDATE 子句里显式写上deleted 0。更麻烦的是MyBatis-Plus 的 BaseMapper 在做selectById时自动过滤逻辑删除记录。这意味着如果你先查再决定 insert 还是 update逻辑删除的记录会被“误判”为不存在然后你走 INSERT 分支但库里的唯一索引还占着位置直接触发 Duplicate Key 报错。批量 upsert 恰好能规避这个坑不依赖先查而是在一条 SQL 里把冲突的更新逻辑自动拦下来。但注意你的ON DUPLICATE KEY UPDATE子句必须显式加deleted 0否则冲突记录会一直保持逻辑删除状态。自动填充字段也要小心。MyBatis-Plus 的TableField(fill FieldFill.INSERT)和FieldFill.INSERT_UPDATE只在它自己的insert/update方法里生效你手写 SQL 走 Mapper 方法时MetaObjectHandler 不会介入。也就是说create_time、update_time这种字段必须自己在 SQL 里写上NOW()或者从实体取值不要在实体里配了自动填充就以为万事大吉。4. 实测对比saveOrUpdateBatch 与一条 SQL 的差距4.1 测试环境与方法我用一台本地开发机做了对比测试环境如下数据库MySQL 8.0.33单实例默认配置数据表模拟库存表10 个字段含一个主键、一个业务唯一索引数据量单批 2000 条记录一半主键不存在纯插入一半主键已存在触发更新JDK17MyBatis-Plus 3.5.3.2测试方法是分别调用saveOrUpdateBatch和自定义batchUpsert各跑 5 次取平均耗时同时记录 MySQL 层的 SQL 执行次数和日志量。4.2 耗时与 SQL 执行量对比方案总耗时ms数据库往返次数单次事务时长日志量saveOrUpdateBatch4826000 次单条多次提交事务边界频繁刷屏 2000 行自定义 batchUpsert861 次单条 SQLInnoDB 一轮执行1 行比例慢约 5.6 倍6000 倍——测试现场有个非常直观的现象saveOrUpdateBatch跑的时候 MySQL 的Com_select、Com_insert、Com_update三个计数器疯狂跳动而自定义批量方案跑的时候三个计数器只有极小幅度的变化。有人可能会说 5.6 倍的差距也没多大2000 条数据 400ms 和 80ms 的区别好像可以接受。但你要把场景放大。按这个比例推算如果一次同步 5 万条数据saveOrUpdateBatch可能耗时 10 秒以上而且期间数据库的并发连接被大量占用、主从延迟明显拉大、行锁竞争加剧批量 upsert 同样 5 万条数据只要 2 秒左右。在生产环境里后者对数据库的影响几乎可以忽略。4.3 两者的适用场景到底怎么切我整理了一张场景判断表团队里最近做新需求我基本都是按这个标准选型条件saveOrUpdateBatch自定义批量 upsert单批数据量 ≤ 100 条推荐代码省心可选看团队习惯单批数据量 100~2000 条勉强可用监控压力大推荐单批数据量 2000 条强烈不推荐必须业务本身要求逐条处理回调适用不适用表字段极多且字段经常动态变化适用不适用SQL 写起来较长对 MySQL 版本有 5.7 兼容要求适用用 VALUES() 写法也可需要补充的是saveOrUpdateBatch并不是一无是处。它的核心价值在于“自动根据主键判断新增还是更新”这个判断逻辑是框架帮你做的。如果你的数据量常年小于 100 条、执行频率不高、分页查询和详情查询是主流场景那么用框架能力继续省事完全没问题。我自己的实践原则是批量写入超过 200 条就换个思路别图省事。5. 常见问题与排查技巧实录5.1 SQL 超过 max_allowed_packet 被拒现象调用batchUpsert时抛出异常错误信息里通常带packet too large字样。原因单条 SQL 拼接长度超过 MySQL 配置的max_allowed_packet。前面说过 5.7 默认 4MB8.0 默认 64MB。如果表字段较多、批量值较大很容易踩线。排查方法先执行SHOW VARIABLES LIKE %max_allowed_packet%确认当前值然后把批量大小调小或者手动改配置SET GLOBAL max_allowed_packet 64 * 1024 * 1024;注意SET GLOBAL只对之后新建的连接生效已经在连接池里的连接不受影响修改完最好重启一下应用或让连接池重建连接。我的建议与其不断放大 SQL 长度上限不如控制单批条数。批量第一原则是控制事务大小和网络包体积不是一味追求“单批越多越好”。5.2 自增主键跳号现象批量 upsert 跑完之后表的 AUTO_INCREMENT 值大幅跳增主键之间出现大段空洞。原因INSERT ... ON DUPLICATE KEY UPDATE在插入冲突时也会消耗自增 ID。InnoDB 的默认行为是预分配一批自增值即使这一行最终没真正插入走了更新分支自增计数照样增长。影响如果业务层对主键连续性有要求比如要求主键就是展示编号这个行为会让你很难看。解决办法是调整业务设计不要把自增主键当作业务编号对外展示如果确实需要连续编号就别用自增主键改用应用层生成编号。实操提醒如果你用的是 8.0 版本可以通过设置innodb_autoinc_lock_mode0来缓解部分场景的跳号但一般不建议动这个参数它会影响并发插入性能。治本方案还是接受自增主键不连续这个事实。5.3 批量更新没有把某些字段置空现象某字段在传入数据里是 null批量更新后库里的老值没有被清空还是原来的旧值。原因ON DUPLICATE KEY UPDATE的语义是“你不指定更新哪个字段就保持原值”。假设你只写了stock_qty new.stock_qty其他字段如remark没写那么remark在冲突发生后不会被更新成新值或 NULL。解决办法根据业务需求在 UPDATE 子句里显式列出所有需要更新的字段。如果某个字段允许置空直接写remark new.remark或remark NULL。不需要更新的字段比如 create_time就原地不动这反而是批量 upsert 的一个优点——可以精确控制更新范围不像直接覆盖整行那样把所有字段都改一遍。5.4 并发写入同一批键导致死锁现象高并发场景下两个线程同时调用批量 upsert 更新同一组主键或唯一键偶尔抛出 Deadlock 异常事务回滚。原因InnoDB 在检查唯一索引冲突时需要加锁不同的行被插入和更新的顺序不一致导致两个事务互相等待对方的锁。排查思路打开 InnoDB 死锁日志查看最近一次死锁的锁等待关系检查两条 SQL 的行处理顺序是否一致。批量 upsert 的数据在 SQL 里的排列顺序最好按照主键或唯一键排序所有线程用相同的顺序处理同一批数据能大幅降低死锁概率。经验值如果业务要求同一批数据的高频并发更新建议加一个分布式锁或者把批量任务串行化。批量场景本身就是重写操作不该用乐观并发思路去扛。5.5 affected rows 语义带来的返回行数困惑现象调用batchUpsert后返回的 int 结果行数和预期不符比如 2000 条数据明明期望返回 2000结果却返回了 4000 或 3000。原因MySQL 官方文档里INSERT ... ON DUPLICATE KEY UPDATE的影响行数规则是插入新记录计 1 行已有记录触发更新计 2 行如果更新前后值没有变化计 0 行。所以“批量更新”部分的行数是双倍计算的。影响如果你用返回行数来做业务成功判定比如if (rows ! list.size())当成异常处理就会误判。我的习惯是只关心“是否大于 0”或者“有没有抛异常”不做精确的行数相等校验。如果真的需要精确确认每一条是否成功需要逐条比对那就不适合用批量方案。5.6 千万级数据的批量同步策略单条 SQL 批量 upsert 也不是银弹。如果你要同步的是几十万甚至上百万条数据就算一条 SQL 能装下事务也会大到让 InnoDB 产生大量 UNDO 日志、锁占用、甚至阻塞从库同步。我的做法是分片 分批 多线程先把总数据集按主键或唯一键排序切成多个分片每个分片内再按 500~1000 条一个批次循环调用batchUpsert每批操作放在单独事务里单独提交用固定线程池控制并发度比如 4~6 个线程避免一下把数据库连接池打满记录每批的处理状态支持失败重试重试只重放失败的那一批。这套方案实测处理 50 万条库存更新总耗时约 20~30 秒而且对线上主库的冲击比saveOrUpdateBatch跑 50 万条可能要跑十几分钟且拖垮监控要小得多。如果你的项目有阿里云或腾讯云的运维后台还可以观察它在主从延迟上的表现——批量 upsert 方案下主从延迟几乎可以忽略。6. 我现阶段的做法与后续可扩展的方向我自己团队现在的数据库操作规范里对批量增改类需求的基本策略是优先评估数据规模100 条以内允许用 MyBatis-Plus 内置 API200 条以上统一走自定义批量 upsert。为了减少重复代码我们把批量 upsert 的 SQL 抽成了一个公共方法模板针对不同表只需要改表名和字段列表新业务接入成本很小。MySQL 8.0 以后还有几个能锦上添花的特性比如原子 DDL、公用表表达式CTE。如果未来要处理更复杂的批量逻辑——比如需要先关联另一张表的汇总数据再更新目标表——可以尝试用 CTE UPDATE 的写法WITH tmp AS ( SELECT id, SUM(qty) AS total_qty FROM source_table GROUP BY id ) UPDATE product_stock ps JOIN tmp ON ps.id tmp.id SET ps.stock_qty tmp.total_qty;这种写法可以把“查-算-更”压缩到一步比先把数据拉到应用层再组装 SQL 更高效。它适用的场景和批量 upsert 不同但思路本质一致能交给数据库在一个语句里完成的事就不要让 Java 应用反复来回折腾网络。最后再分享一个我踩过的坑不管方案设计得多合理批量 SQL 上线前一定先用测试库跑一遍重点看计划里的索引命中情况和返回影响行数。我曾经因为一个字段漏选了唯一索引导致批量 upsert 在测试环境跑得飞快、上生产之后数据翻倍排查了大半天才意识到是索引缺失。所有批量方案的前提都是表结构可靠这个基础不牢性能优化做得再多都是白搭。