后端干得稍微久一点几乎都躲不过这种需求运营拿了一张Excel里面是几千个商品ID、实时库存、上下架状态或者财务让你批量修正一批订单的支付状态。事情本身不复杂复杂的是怎么把这条“mysql批量更新语句”写好不然一条一条UPDATE线上马上会给你点颜色看——请求变慢、连接池被占满、锁等待超时后台管理页点一下能转半天。这篇内容适合做后端开发、数据处理或经常写SQL脚本的同学。我会把真实项目里能用上的批量更新方案都过一遍CASE WHEN拼接、INSERT ... ON DUPLICATE KEY UPDATE、临时表JOIN、应用层批处理提交以及每个方案在哪类场景下性价比最高。不会只给一个标准答案因为生产环境里的数据量、字段长度、复制架构都不一样选错方案比写错语法更难受。1. 批量更新要解决的三个核心问题1.1 逐条UPDATE为什么慢先说最朴素的写法拿到几千个商品循环执行UPDATE product SET stock xx WHERE id yy。单看一条语句可能只要零点几毫秒但放大到几千条问题就出来了。第一层开销是网络往返。每次UPDATE都要经过客户端到MySQL服务器一次完整的请求-响应周期。在内网环境里单次往返可能只有0.2毫秒但几千次累积起来就是几百毫秒到几秒而且这条链路是在一个请求里串行执行的。如果Web层到数据库之间还有代理或跨机房网络这个时间会进一步放大。第二层开销是SQL解析与执行准备。每一条UPDATE到达MySQL后无论内容多简单都要走词法分析、语法分析、生成执行计划、打开表、加锁、更新、提交释放这一整套流程。重复几千次MySQL的CPU和日志写入都会跟着遭殃尤其当连接数一多整个实例的负载都会上去。第三层开销是事务和锁的放大。如果用的是autocommit模式每一条UPDATE就是一个独立事务一个锁获取和释放周期如果手动开了事务再循环执行又有一个超长事务的风险——持有行锁的时间被几千条SQL拉得极长其他业务一碰同一批数据就会卡在锁等待上。把这三层开销压下去就是批量更新存在的意义。1.2 批量更新的本质用哪种代价换哪种收益市面上常见的批量更新写法本质上都在做同一件事减少语句执行次数降低锁的持有时间同时把网络和解析开销摊薄。不过不同方案省的东西不一样方案主要省掉的成本需要付出的代价CASE WHEN一条SQL网络往返、多次解析、事务开销单条SQL文本变大适合控制行数使用INSERT ... ON DUPLICATE KEY UPDATE网络往返语句更紧凑需要有唯一键或主键有插入副作用临时表JOIN UPDATE复杂场景的多次联查、手工比对多一步建表和导数过程JDBC/MyBatis批处理应用到数据库的往返次数对UPDATE语句不像INSERT那样能重写合并清楚了这一点你再看网上各种方案之争就不会迷茫。比如CASE WHEN和INSERT INTO...ON DUPLICATE KEY UPDATE两者都能一次提交几十上百行但适用前提不同后面我会展开。1.3 执行前必须确认的底线无论选哪种方案有一个动作不能省先确认影响范围。我见过太多次线上事故都是因为少了这一步——最后发现WHERE条件写漏了或者导入文件里的ID有重复直接导致几千行不该动的数据被改了。在生产库执行任何批量UPDATE之前我习惯先跑一条等价的SELECT统计行数再把涉及到的原始数据导成备份SQL文件。数据量不大用mysqldump --whereid IN (...)数据量大就建一张备份表把整批旧数据拷进去。这个习惯看着土但出了问题时它是你最后的后悔药。2. 方案一CASE WHEN一条SQL完成多行更新2.1 基础写法与执行逻辑CASE WHEN应该是应用最广的批量更新写法核心是让更新语句在遍历匹配到的行时根据ID取不同的目标值。UPDATE product SET stock CASE id WHEN 1001 THEN 20 WHEN 1002 THEN 35 WHEN 1003 THEN 8 ELSE stock END WHERE id IN (1001, 1002, 1003);这条SQL的重点在于把“按ID找行”和“按ID给值”合在了一起。MySQL执行时会根据WHERE条件定位到这三行然后对每一行计算CASE表达式的值把对应行的stock更新成指定数字。ELSE stock的意思是如果某行在WHERE里但不在CASE分支中就保持原值不动。没有ELSE的情况非常危险。比如你CASE里只写了1001和1002但WHERE里写了三个ID第三行匹配到CASE时没有任何分支命中表达式的结果就是NULL更新完成后那一行直接被写成NULL。这不是语法报错而是一个逻辑陷阱稍不留神就会让整批数据报废。2.2 多列组合更新的写法实际业务里一次改动往往不止一个字段比如既要调库存又要改上下架状态还要记录更新时间。CASE WHEN可以拆成多个表达式放在同一条UPDATE里UPDATE product SET stock CASE id WHEN 1001 THEN stock - 2 WHEN 1002 THEN stock 5 ELSE stock END, status CASE id WHEN 1001 THEN ACTIVE WHEN 1002 THEN PAUSED ELSE status END, updated_at NOW() WHERE id IN (1001, 1002);注意到这里有个更有用的写法THEN stock - 2。它不是写死一个目标值而是基于当前行的旧值做加减。这意味着同一批更新里你可以一部分商品加库存、一部分减库存而不需要分别拆两次SQL。MySQL解析时会在每一行上读取当前stock再执行对应运算天然支持这种差异化更新。2.3 拼接时最容易踩的坑CASE WHEN的问题不在SQL本身而在拼接过程。首先是单条SQL的长度上限。整条UPDATE文本会在SQL层解析也会在复制时写入二进制日志。默认的max_allowed_packet通常是64MB但实际不建议把一条SQL撑到几MB。我一般按每次500到1000行拆分字段多、更新值长时取小值比如300行一批。这样单条SQL文本一般能控制在几十到几百KB解析和日志压力都可控。其次是拼接顺序和WHERE范围的一致性。如果CASE里的ID和WHERE里的ID不完全对应就会出现前面说的NULL覆盖。比较稳妥的做法是先从业务数据里提取一个List再去生成CASE分支时遍历同一个List最后从同一个List生成IN列表。三个部分必须基于同一份数据源不要手写一部分、代码生成一部分。2.4 应用层动态拼接的工程姿势如果你在Java、Go这类后端语言里动态生成这条SQL最常见的做法是用StringBuilder循环拼。我见过很多代码是直接把值和ID当作字符串拼进去ListLong ids new ArrayList(); StringBuilder sql new StringBuilder(UPDATE product SET stock CASE id ); for (Product p : productList) { sql.append(WHEN ).append(p.getId()).append( THEN ).append(p.getStock()).append( ); ids.add(p.getId()); } sql.append(ELSE stock END WHERE id IN (); for (int i 0; i ids.size(); i) { if (i 0) sql.append(, ); sql.append(ids.get(i)); } sql.append());如果字段值来源是可信的数值型数据这种直接拼接问题不大但一旦值来自外部导入或用户输入我强烈建议在拼接前做严格类型校验或者改为参数化占位符写法。数字型字段可以校验Long.parseLong、Integer.parseInt字符串字段则要处理转义。更安全的方式是把WHEN ? THEN ?这样的占位符拼进SQL再用PreparedStatement逐个绑定但占位符数量一多代码会繁琐一些需要自己权衡。还要提醒一点如果你用的是MyBatis的foreach标签其实也是生成同样的CASE WHEN语句。别把foreach和JDBC批处理混为一谈前者生成的仍然是一条大SQL。3. 方案二INSERT ... ON DUPLICATE KEY UPDATE的隐藏身份3.1 为什么它能用来做批量更新很多从后端入手的同学会忽略这个方案因为它看起来像插入语句。但它的执行语义是如果插入的数据在唯一键上冲突就转为更新已有记录。利用这一点我们可以把“按ID批量更新”改写成一次批量INSERT。INSERT INTO product (id, stock, status, updated_at) VALUES (1001, 20, ACTIVE, NOW()), (1002, 35, PAUSED, NOW()), (1003, 8, SOLD_OUT, NOW()) ON DUPLICATE KEY UPDATE stock VALUES(stock), status VALUES(status), updated_at VALUES(updated_at);当表里这些ID已经存在时不会真正插入而是把对应行的stock、status、updated_at更新成VALUES里准备的值。这种写法有一个天然优势VALUES列表本质上就是一个紧凑的数据行集合文本比CASE WHEN的表达式更短每行的开销也更小。在几千行数据导入场景里语句的解析成本明显更低性能往往比CASE WHEN还要好一点。3.2 MySQL 8.0.20之后的写法变化这个方案有个绕不开的版本坑从MySQL 8.0.20开始VALUES()函数被标记为弃用虽然继续使用还能跑但会在日志中提示DEPRECATED后续版本可能直接移除。官方推荐用行别名代替INSERT INTO product (id, stock, status, updated_at) VALUES (1001, 20, ACTIVE, NOW()), (1002, 35, PAUSED, NOW()), (1003, 8, SOLD_OUT, NOW()) AS new ON DUPLICATE KEY UPDATE stock new.stock, status new.status, updated_at new.updated_at;这里AS new给待插入的数据行起了别名后面的ON DUPLICATE KEY UPDATE直接引用new.stock、new.status语义更清晰也不怕版本演进。如果你的生产环境还是MySQL 5.7或早期8.0暂时用VALUES()没有大问题但新项目一律建议写到别名语法减少未来升级的适配成本。3.3 副作用和适用边界别急着把线上所有批量更新都改写成这个方案有几个边界条件必须想清楚。最核心的边界是目标表必须有唯一键。这里的唯一键可以是主键也可以是某个业务唯一的索引。如果你的表没有任何唯一键这条语句会变成纯插入根本不更新数据还会越插越多。第二个边界是插入副作用。ON DUPLICATE KEY UPDATE在ID不存在时会把新行插进去。如果你只想更新已有的记录就在批量导入前先做一轮ID存在性过滤如果业务允许这批量数据“不存在就新增”那反而成了优点类似“有则更新、无则写入”的同步逻辑。第三个边界是自增ID的消耗。InnoDB在尝试插入时会预分配自增ID即使最终走了更新分支这些ID也会被消耗掉。长时间大量使用这个方案你会看到表的下一个自增ID跳跃式增长间距变大。这对大多数业务没有实质性影响但如果你有严格的自增ID连续性要求就要慎重。3.4 与CASE WHEN怎么选选择标准并不复杂数据来源是一批“按主键或唯一键”组织的记录且表结构能满足唯一键条件优先用INSERT...ON DUPLICATE KEY UPDATE。它的语句更短解析更快批量导入性能更好。更新逻辑需要基于当前行的其他字段做复杂运算比如“按库存类型决定加减量”用CASE WHEN表达更直接。如果你的MySQL版本偏老团队对版本差异不敏感CASE WHEN的兼容性更稳。4. 数据量再大或规则再复杂临时表 JOIN更新4.1 临时表方案怎么搭当更新规则不是简单的“ID对应一个值”而是要拼凑多张表的数据或者需要对几万行做同步时CASE WHEN这种内联写法的可维护性就撑不住了。这时候把更新数据落到临时表再让目标表和临时表做JOIN更新是更稳的方案。CREATE TEMPORARY TABLE tmp_product_update ( id BIGINT NOT NULL, stock INT NOT NULL, status VARCHAR(20) NOT NULL, PRIMARY KEY (id) ) ENGINE InnoDB;接下来把目标数据批量写入临时表。数据量小可以用多条INSERT数据量大可以直接LOAD DATA或者分批导入。核心更新就一句UPDATE product p JOIN tmp_product_update t ON p.id t.id SET p.stock t.stock, p.status t.status, p.updated_at NOW();临时表方案看着多了一步但它的优势是先集中准备数据再集中执行一次UPDATE。对MySQL而言这只是一条JOIN更新语句执行计划里临时表因为有主键走的是索引关联性能和可读性都很好。而且你可以在临时表里加业务字段、状态标记先做一轮校验再更新逻辑非常清晰。4.2 为什么复杂批量场景特别适合我在做某次商品数据同步时遇到过这样一个场景更新规则不是简单赋值而是“根据订单表中某个时间段内各商品的销售数量反写商品表的累计销量”。如果用CASE WHEN你得先在应用层把所有商品算好再拼出几百个WHEN...THEN...分支SQL又长又难审计。用临时表就舒服多了CREATE TEMPORARY TABLE tmp_sales_stat AS SELECT product_id, SUM(quantity) AS total_qty FROM orders WHERE order_date 2025-01-01 AND order_date 2025-02-01 GROUP BY product_id;然后UPDATE product p JOIN tmp_sales_stat t ON p.id t.product_id SET p.sales_count p.sales_count t.total_qty;这段逻辑如果用一句SQL嵌套子查询也能写但可读性和可维护性差很多。临时表方案还能让你在更新前对统计结果做抽查用SELECT看一眼要更新的数据是否符合预期这是其他方案很难替代的。还有一点临时表是会话级别的连接断开自动消失。如果你在多线程环境里用连接池每个线程拿到的连接可能不是同一个会话临时表不一定互相可见。遇到这种情况要么保证更新和建表在同一个连接里完成要么用一张正式的表来承载中间数据。4.3 应用层批量提交JDBC Batch 和 MyBatis 的姿势如果你是Java后端还有一种更贴近业务代码的做法不追求单条大SQL而是用PreparedStatement的批量执行能力把多条UPDATE打包提交。try (Connection conn dataSource.getConnection()) { conn.setAutoCommit(false); String sql UPDATE product SET stock ?, status ? WHERE id ?; try (PreparedStatement ps conn.prepareStatement(sql)) { for (int i 0; i productList.size(); i) { Product p productList.get(i); ps.setInt(1, p.getStock()); ps.setString(2, p.getStatus()); ps.setLong(3, p.getId()); ps.addBatch(); if ((i 1) % 500 0) { ps.executeBatch(); } } ps.executeBatch(); conn.commit(); } catch (Exception e) { conn.rollback(); throw e; } }这里的关键点是手动管理事务。setAutoCommit(false)和最后的commit()是配套关系不能只在循环里执行addBatch()而忘记提交。批大小我习惯控制在500左右太大容易让单次提交持锁时间变长太小又体现不出批量优势。需要说明的是MySQL的JDBC驱动在打开rewriteBatchedStatementstrue时对批量INSERT的优化非常明显会把多条插入合并改写成多值INSERT但对UPDATE语句驱动并不会像INSERT那样把多条合并成一条多值更新更多是减少客户端和数据库之间的往返次数。所以JDBC批处理适合更新条数大、但每条UPDATE本身简单直接的场景。如果你的项目用MyBatis可以在获取SqlSession时指定ExecutorType.BATCH然后循环调用同一个UPDATE Mapper方法。这样每条更新不会立即提交而是进入JDBC批量缓冲区到一定数量后统一执行。注意不要在这个模式下执行查询操作否则批量Executor会触发隐式flush影响批处理效果。4.4 方案选择参考表场景典型特征推荐方案几十到一千行字段简单希望原子更新CASE WHEN一条SQL数据按主键/唯一键导入允许“不存在则插入”INSERT ... ON DUPLICATE KEY UPDATE更新规则来自统计查询或复杂关联临时表 JOIN UPDATE应用层已有完整对象列表更新逻辑简单JDBC/MyBatis批处理几万行以上且不想让单条SQL太大临时表或批处理按500行一组拆分方案没有绝对最优关键是看你的数据形态和更新规则落在哪一列。5. 批量更新路上的典型故障与排查实录5.1 更新后个别行变成了NULL这是CASE WHEN方案最常见的事故。表现是更新完发现某一行或某几行的字段值变成NULL而其他行正常。原因通常是CASE表达式缺少ELSE分支同时WHERE里的ID集合比CASE分支里的ID集合多没匹配到的行被算成了NULL。排查方法很简单把SQL里CASE分支的ID列表和WHERE条件的ID列表拉出来做集合比对看哪些ID漏掉了。更好的做法是在拼接代码里断言两个集合完全一致或者团队约定CASE表达式里必须写ELSE 原字段用原值兜底。养成这个习惯后这类事故基本能根除。5.2 锁等待超时和死锁批量更新时长事务会把锁拖很久尤其是在数据量大的情况下。我遇到过Lock wait timeout exceeded; try restarting transaction报错第一反应不是加大超时时间而是查谁在占锁。先看有没有其他事务没提交。用下面这条SQL查看当前锁等待关系SELECT * FROM performance_schema.data_lock_waits\G配合SHOW ENGINE INNODB STATUS查看LATEST DETECTED DEADLOCK段能定位到具体的事务和SQL。死锁的典型成因之一是多个事务以不同顺序更新同一批行。解决办法是让批量更新按统一的排序执行比如所有更新都按ID升序处理。排序在单机数据量不大时效果不明显但并发量一上来差距立刻拉开。5.3 主从延迟和binlog暴涨一条批量UPDATE影响几万行时主库执行可能只要一两秒但从库复制同样一条语句时如果开启的是基于行的复制格式binlog里会记录每一行变更前后的完整数据。几万行的更新binlog事件轻松达到几百MB从库应用这些事件的时间会被拉长主从延迟瞬间飙升。这个问题的排查思路是先看主库binlog大小和从库的Seconds_Behind_Master。规避方法其实和减少锁持有一个路数拆批。把几万行拆成若干批每批几百行中间加一点间隔。这样每批生成的binlog事件是可控的从库也能一批一批消化不会一次性阻塞住。5.4 单条SQL太长触发max_allowed_packet拼接出来的批量更新SQL如果太长会直接报Packets larger than max_allowed_packet are not allowed。这个参数默认值在不同版本和配置里不一有的是4MB有的是64MB。先看当前值SHOW VARIABLES LIKE max_allowed_packet;不要为了跑一条超大SQL临时把这个参数调到天上去。更好的做法是限制单批行数控制在1000行以内再重试。如果你的字段特别多或者更新的值本身很长批次再往下降。5.5 排查速查表现象可能原因排查动作更新后字段为NULLCASE没有ELSE且ID不匹配比对ID集合给CASE加ELSE原字段锁等待超时其他长事务未提交查data_lock_waits定位持锁事务死锁多个事务更新顺序不一致统一排序缩短事务时间主从延迟高单次更新行数太多binlog事件巨大拆批控制单批行数触发max_allowed_packet单条SQL文本超限调大批次限制或降低单批行数更新后数据与预期不一致WHERE条件不规范、ID有重复先用SELECT统计影响行数验证清单6. 我最后沉淀下来的几条习惯做批量更新做得多了你会形成一些自己的肌肉记忆我把自己一直遵守的几条写在这里算不上什么高深理论但关键时刻很管用。第一能拆批就拆批。即使CASE WHEN性能很好我也不建议一次性更新上万行。把批大小控制在500到1000行既能压住SQL文本长度又能让锁持有时间和主从复制压力都处在安全线内。批与批之间如果业务允许可以加非常短的间隔给从库一点喘息时间。第二更新前默认先做备份。很多人觉得测试环境验证过了生产库直接跑没问题。但生产数据往往有测试环境没模拟出来的脏数据、重复ID、历史逻辑差异。批量更新前用CREATE TABLE xxx_bak AS SELECT ...备份一下涉及到的行成本很低收益极高。不需要每次都做但凡是涉及线上核心业务表、影响行数超过一百这个动作我不会省。第三事后必须校验。执行完批量更新别急着告诉业务方“跑完了”。我会从更新清单里随机抽几条和数据库里的最新值比对同时用SQL汇总一下影响行数是否和预期一致。少数派异常数据能在校验这一步被拦下来等业务反馈就晚了。关于“mysql批量更新语句”网上方案很多但真正决定成败的往往是一些细节批次怎么拆、事务怎么提交、版本差异怎么处理、更新逻辑怎么兜底。把这些细节都照顾到你基本就不会在批量更新上翻车了。
后端干得稍微久一点几乎都躲不过这种需求运营拿了一张Excel里面是几千个商品ID、实时库存、上下架状态或者财务让你批量修正一批订单的支付状态。事情本身不复杂复杂的是怎么把这条“mysql批量更新语句”写好不然一条一条UPDATE线上马上会给你点颜色看——请求变慢、连接池被占满、锁等待超时后台管理页点一下能转半天。这篇内容适合做后端开发、数据处理或经常写SQL脚本的同学。我会把真实项目里能用上的批量更新方案都过一遍CASE WHEN拼接、INSERT ... ON DUPLICATE KEY UPDATE、临时表JOIN、应用层批处理提交以及每个方案在哪类场景下性价比最高。不会只给一个标准答案因为生产环境里的数据量、字段长度、复制架构都不一样选错方案比写错语法更难受。1. 批量更新要解决的三个核心问题1.1 逐条UPDATE为什么慢先说最朴素的写法拿到几千个商品循环执行UPDATE product SET stock xx WHERE id yy。单看一条语句可能只要零点几毫秒但放大到几千条问题就出来了。第一层开销是网络往返。每次UPDATE都要经过客户端到MySQL服务器一次完整的请求-响应周期。在内网环境里单次往返可能只有0.2毫秒但几千次累积起来就是几百毫秒到几秒而且这条链路是在一个请求里串行执行的。如果Web层到数据库之间还有代理或跨机房网络这个时间会进一步放大。第二层开销是SQL解析与执行准备。每一条UPDATE到达MySQL后无论内容多简单都要走词法分析、语法分析、生成执行计划、打开表、加锁、更新、提交释放这一整套流程。重复几千次MySQL的CPU和日志写入都会跟着遭殃尤其当连接数一多整个实例的负载都会上去。第三层开销是事务和锁的放大。如果用的是autocommit模式每一条UPDATE就是一个独立事务一个锁获取和释放周期如果手动开了事务再循环执行又有一个超长事务的风险——持有行锁的时间被几千条SQL拉得极长其他业务一碰同一批数据就会卡在锁等待上。把这三层开销压下去就是批量更新存在的意义。1.2 批量更新的本质用哪种代价换哪种收益市面上常见的批量更新写法本质上都在做同一件事减少语句执行次数降低锁的持有时间同时把网络和解析开销摊薄。不过不同方案省的东西不一样方案主要省掉的成本需要付出的代价CASE WHEN一条SQL网络往返、多次解析、事务开销单条SQL文本变大适合控制行数使用INSERT ... ON DUPLICATE KEY UPDATE网络往返语句更紧凑需要有唯一键或主键有插入副作用临时表JOIN UPDATE复杂场景的多次联查、手工比对多一步建表和导数过程JDBC/MyBatis批处理应用到数据库的往返次数对UPDATE语句不像INSERT那样能重写合并清楚了这一点你再看网上各种方案之争就不会迷茫。比如CASE WHEN和INSERT INTO...ON DUPLICATE KEY UPDATE两者都能一次提交几十上百行但适用前提不同后面我会展开。1.3 执行前必须确认的底线无论选哪种方案有一个动作不能省先确认影响范围。我见过太多次线上事故都是因为少了这一步——最后发现WHERE条件写漏了或者导入文件里的ID有重复直接导致几千行不该动的数据被改了。在生产库执行任何批量UPDATE之前我习惯先跑一条等价的SELECT统计行数再把涉及到的原始数据导成备份SQL文件。数据量不大用mysqldump --whereid IN (...)数据量大就建一张备份表把整批旧数据拷进去。这个习惯看着土但出了问题时它是你最后的后悔药。2. 方案一CASE WHEN一条SQL完成多行更新2.1 基础写法与执行逻辑CASE WHEN应该是应用最广的批量更新写法核心是让更新语句在遍历匹配到的行时根据ID取不同的目标值。UPDATE product SET stock CASE id WHEN 1001 THEN 20 WHEN 1002 THEN 35 WHEN 1003 THEN 8 ELSE stock END WHERE id IN (1001, 1002, 1003);这条SQL的重点在于把“按ID找行”和“按ID给值”合在了一起。MySQL执行时会根据WHERE条件定位到这三行然后对每一行计算CASE表达式的值把对应行的stock更新成指定数字。ELSE stock的意思是如果某行在WHERE里但不在CASE分支中就保持原值不动。没有ELSE的情况非常危险。比如你CASE里只写了1001和1002但WHERE里写了三个ID第三行匹配到CASE时没有任何分支命中表达式的结果就是NULL更新完成后那一行直接被写成NULL。这不是语法报错而是一个逻辑陷阱稍不留神就会让整批数据报废。2.2 多列组合更新的写法实际业务里一次改动往往不止一个字段比如既要调库存又要改上下架状态还要记录更新时间。CASE WHEN可以拆成多个表达式放在同一条UPDATE里UPDATE product SET stock CASE id WHEN 1001 THEN stock - 2 WHEN 1002 THEN stock 5 ELSE stock END, status CASE id WHEN 1001 THEN ACTIVE WHEN 1002 THEN PAUSED ELSE status END, updated_at NOW() WHERE id IN (1001, 1002);注意到这里有个更有用的写法THEN stock - 2。它不是写死一个目标值而是基于当前行的旧值做加减。这意味着同一批更新里你可以一部分商品加库存、一部分减库存而不需要分别拆两次SQL。MySQL解析时会在每一行上读取当前stock再执行对应运算天然支持这种差异化更新。2.3 拼接时最容易踩的坑CASE WHEN的问题不在SQL本身而在拼接过程。首先是单条SQL的长度上限。整条UPDATE文本会在SQL层解析也会在复制时写入二进制日志。默认的max_allowed_packet通常是64MB但实际不建议把一条SQL撑到几MB。我一般按每次500到1000行拆分字段多、更新值长时取小值比如300行一批。这样单条SQL文本一般能控制在几十到几百KB解析和日志压力都可控。其次是拼接顺序和WHERE范围的一致性。如果CASE里的ID和WHERE里的ID不完全对应就会出现前面说的NULL覆盖。比较稳妥的做法是先从业务数据里提取一个List再去生成CASE分支时遍历同一个List最后从同一个List生成IN列表。三个部分必须基于同一份数据源不要手写一部分、代码生成一部分。2.4 应用层动态拼接的工程姿势如果你在Java、Go这类后端语言里动态生成这条SQL最常见的做法是用StringBuilder循环拼。我见过很多代码是直接把值和ID当作字符串拼进去ListLong ids new ArrayList(); StringBuilder sql new StringBuilder(UPDATE product SET stock CASE id ); for (Product p : productList) { sql.append(WHEN ).append(p.getId()).append( THEN ).append(p.getStock()).append( ); ids.add(p.getId()); } sql.append(ELSE stock END WHERE id IN (); for (int i 0; i ids.size(); i) { if (i 0) sql.append(, ); sql.append(ids.get(i)); } sql.append());如果字段值来源是可信的数值型数据这种直接拼接问题不大但一旦值来自外部导入或用户输入我强烈建议在拼接前做严格类型校验或者改为参数化占位符写法。数字型字段可以校验Long.parseLong、Integer.parseInt字符串字段则要处理转义。更安全的方式是把WHEN ? THEN ?这样的占位符拼进SQL再用PreparedStatement逐个绑定但占位符数量一多代码会繁琐一些需要自己权衡。还要提醒一点如果你用的是MyBatis的foreach标签其实也是生成同样的CASE WHEN语句。别把foreach和JDBC批处理混为一谈前者生成的仍然是一条大SQL。3. 方案二INSERT ... ON DUPLICATE KEY UPDATE的隐藏身份3.1 为什么它能用来做批量更新很多从后端入手的同学会忽略这个方案因为它看起来像插入语句。但它的执行语义是如果插入的数据在唯一键上冲突就转为更新已有记录。利用这一点我们可以把“按ID批量更新”改写成一次批量INSERT。INSERT INTO product (id, stock, status, updated_at) VALUES (1001, 20, ACTIVE, NOW()), (1002, 35, PAUSED, NOW()), (1003, 8, SOLD_OUT, NOW()) ON DUPLICATE KEY UPDATE stock VALUES(stock), status VALUES(status), updated_at VALUES(updated_at);当表里这些ID已经存在时不会真正插入而是把对应行的stock、status、updated_at更新成VALUES里准备的值。这种写法有一个天然优势VALUES列表本质上就是一个紧凑的数据行集合文本比CASE WHEN的表达式更短每行的开销也更小。在几千行数据导入场景里语句的解析成本明显更低性能往往比CASE WHEN还要好一点。3.2 MySQL 8.0.20之后的写法变化这个方案有个绕不开的版本坑从MySQL 8.0.20开始VALUES()函数被标记为弃用虽然继续使用还能跑但会在日志中提示DEPRECATED后续版本可能直接移除。官方推荐用行别名代替INSERT INTO product (id, stock, status, updated_at) VALUES (1001, 20, ACTIVE, NOW()), (1002, 35, PAUSED, NOW()), (1003, 8, SOLD_OUT, NOW()) AS new ON DUPLICATE KEY UPDATE stock new.stock, status new.status, updated_at new.updated_at;这里AS new给待插入的数据行起了别名后面的ON DUPLICATE KEY UPDATE直接引用new.stock、new.status语义更清晰也不怕版本演进。如果你的生产环境还是MySQL 5.7或早期8.0暂时用VALUES()没有大问题但新项目一律建议写到别名语法减少未来升级的适配成本。3.3 副作用和适用边界别急着把线上所有批量更新都改写成这个方案有几个边界条件必须想清楚。最核心的边界是目标表必须有唯一键。这里的唯一键可以是主键也可以是某个业务唯一的索引。如果你的表没有任何唯一键这条语句会变成纯插入根本不更新数据还会越插越多。第二个边界是插入副作用。ON DUPLICATE KEY UPDATE在ID不存在时会把新行插进去。如果你只想更新已有的记录就在批量导入前先做一轮ID存在性过滤如果业务允许这批量数据“不存在就新增”那反而成了优点类似“有则更新、无则写入”的同步逻辑。第三个边界是自增ID的消耗。InnoDB在尝试插入时会预分配自增ID即使最终走了更新分支这些ID也会被消耗掉。长时间大量使用这个方案你会看到表的下一个自增ID跳跃式增长间距变大。这对大多数业务没有实质性影响但如果你有严格的自增ID连续性要求就要慎重。3.4 与CASE WHEN怎么选选择标准并不复杂数据来源是一批“按主键或唯一键”组织的记录且表结构能满足唯一键条件优先用INSERT...ON DUPLICATE KEY UPDATE。它的语句更短解析更快批量导入性能更好。更新逻辑需要基于当前行的其他字段做复杂运算比如“按库存类型决定加减量”用CASE WHEN表达更直接。如果你的MySQL版本偏老团队对版本差异不敏感CASE WHEN的兼容性更稳。4. 数据量再大或规则再复杂临时表 JOIN更新4.1 临时表方案怎么搭当更新规则不是简单的“ID对应一个值”而是要拼凑多张表的数据或者需要对几万行做同步时CASE WHEN这种内联写法的可维护性就撑不住了。这时候把更新数据落到临时表再让目标表和临时表做JOIN更新是更稳的方案。CREATE TEMPORARY TABLE tmp_product_update ( id BIGINT NOT NULL, stock INT NOT NULL, status VARCHAR(20) NOT NULL, PRIMARY KEY (id) ) ENGINE InnoDB;接下来把目标数据批量写入临时表。数据量小可以用多条INSERT数据量大可以直接LOAD DATA或者分批导入。核心更新就一句UPDATE product p JOIN tmp_product_update t ON p.id t.id SET p.stock t.stock, p.status t.status, p.updated_at NOW();临时表方案看着多了一步但它的优势是先集中准备数据再集中执行一次UPDATE。对MySQL而言这只是一条JOIN更新语句执行计划里临时表因为有主键走的是索引关联性能和可读性都很好。而且你可以在临时表里加业务字段、状态标记先做一轮校验再更新逻辑非常清晰。4.2 为什么复杂批量场景特别适合我在做某次商品数据同步时遇到过这样一个场景更新规则不是简单赋值而是“根据订单表中某个时间段内各商品的销售数量反写商品表的累计销量”。如果用CASE WHEN你得先在应用层把所有商品算好再拼出几百个WHEN...THEN...分支SQL又长又难审计。用临时表就舒服多了CREATE TEMPORARY TABLE tmp_sales_stat AS SELECT product_id, SUM(quantity) AS total_qty FROM orders WHERE order_date 2025-01-01 AND order_date 2025-02-01 GROUP BY product_id;然后UPDATE product p JOIN tmp_sales_stat t ON p.id t.product_id SET p.sales_count p.sales_count t.total_qty;这段逻辑如果用一句SQL嵌套子查询也能写但可读性和可维护性差很多。临时表方案还能让你在更新前对统计结果做抽查用SELECT看一眼要更新的数据是否符合预期这是其他方案很难替代的。还有一点临时表是会话级别的连接断开自动消失。如果你在多线程环境里用连接池每个线程拿到的连接可能不是同一个会话临时表不一定互相可见。遇到这种情况要么保证更新和建表在同一个连接里完成要么用一张正式的表来承载中间数据。4.3 应用层批量提交JDBC Batch 和 MyBatis 的姿势如果你是Java后端还有一种更贴近业务代码的做法不追求单条大SQL而是用PreparedStatement的批量执行能力把多条UPDATE打包提交。try (Connection conn dataSource.getConnection()) { conn.setAutoCommit(false); String sql UPDATE product SET stock ?, status ? WHERE id ?; try (PreparedStatement ps conn.prepareStatement(sql)) { for (int i 0; i productList.size(); i) { Product p productList.get(i); ps.setInt(1, p.getStock()); ps.setString(2, p.getStatus()); ps.setLong(3, p.getId()); ps.addBatch(); if ((i 1) % 500 0) { ps.executeBatch(); } } ps.executeBatch(); conn.commit(); } catch (Exception e) { conn.rollback(); throw e; } }这里的关键点是手动管理事务。setAutoCommit(false)和最后的commit()是配套关系不能只在循环里执行addBatch()而忘记提交。批大小我习惯控制在500左右太大容易让单次提交持锁时间变长太小又体现不出批量优势。需要说明的是MySQL的JDBC驱动在打开rewriteBatchedStatementstrue时对批量INSERT的优化非常明显会把多条插入合并改写成多值INSERT但对UPDATE语句驱动并不会像INSERT那样把多条合并成一条多值更新更多是减少客户端和数据库之间的往返次数。所以JDBC批处理适合更新条数大、但每条UPDATE本身简单直接的场景。如果你的项目用MyBatis可以在获取SqlSession时指定ExecutorType.BATCH然后循环调用同一个UPDATE Mapper方法。这样每条更新不会立即提交而是进入JDBC批量缓冲区到一定数量后统一执行。注意不要在这个模式下执行查询操作否则批量Executor会触发隐式flush影响批处理效果。4.4 方案选择参考表场景典型特征推荐方案几十到一千行字段简单希望原子更新CASE WHEN一条SQL数据按主键/唯一键导入允许“不存在则插入”INSERT ... ON DUPLICATE KEY UPDATE更新规则来自统计查询或复杂关联临时表 JOIN UPDATE应用层已有完整对象列表更新逻辑简单JDBC/MyBatis批处理几万行以上且不想让单条SQL太大临时表或批处理按500行一组拆分方案没有绝对最优关键是看你的数据形态和更新规则落在哪一列。5. 批量更新路上的典型故障与排查实录5.1 更新后个别行变成了NULL这是CASE WHEN方案最常见的事故。表现是更新完发现某一行或某几行的字段值变成NULL而其他行正常。原因通常是CASE表达式缺少ELSE分支同时WHERE里的ID集合比CASE分支里的ID集合多没匹配到的行被算成了NULL。排查方法很简单把SQL里CASE分支的ID列表和WHERE条件的ID列表拉出来做集合比对看哪些ID漏掉了。更好的做法是在拼接代码里断言两个集合完全一致或者团队约定CASE表达式里必须写ELSE 原字段用原值兜底。养成这个习惯后这类事故基本能根除。5.2 锁等待超时和死锁批量更新时长事务会把锁拖很久尤其是在数据量大的情况下。我遇到过Lock wait timeout exceeded; try restarting transaction报错第一反应不是加大超时时间而是查谁在占锁。先看有没有其他事务没提交。用下面这条SQL查看当前锁等待关系SELECT * FROM performance_schema.data_lock_waits\G配合SHOW ENGINE INNODB STATUS查看LATEST DETECTED DEADLOCK段能定位到具体的事务和SQL。死锁的典型成因之一是多个事务以不同顺序更新同一批行。解决办法是让批量更新按统一的排序执行比如所有更新都按ID升序处理。排序在单机数据量不大时效果不明显但并发量一上来差距立刻拉开。5.3 主从延迟和binlog暴涨一条批量UPDATE影响几万行时主库执行可能只要一两秒但从库复制同样一条语句时如果开启的是基于行的复制格式binlog里会记录每一行变更前后的完整数据。几万行的更新binlog事件轻松达到几百MB从库应用这些事件的时间会被拉长主从延迟瞬间飙升。这个问题的排查思路是先看主库binlog大小和从库的Seconds_Behind_Master。规避方法其实和减少锁持有一个路数拆批。把几万行拆成若干批每批几百行中间加一点间隔。这样每批生成的binlog事件是可控的从库也能一批一批消化不会一次性阻塞住。5.4 单条SQL太长触发max_allowed_packet拼接出来的批量更新SQL如果太长会直接报Packets larger than max_allowed_packet are not allowed。这个参数默认值在不同版本和配置里不一有的是4MB有的是64MB。先看当前值SHOW VARIABLES LIKE max_allowed_packet;不要为了跑一条超大SQL临时把这个参数调到天上去。更好的做法是限制单批行数控制在1000行以内再重试。如果你的字段特别多或者更新的值本身很长批次再往下降。5.5 排查速查表现象可能原因排查动作更新后字段为NULLCASE没有ELSE且ID不匹配比对ID集合给CASE加ELSE原字段锁等待超时其他长事务未提交查data_lock_waits定位持锁事务死锁多个事务更新顺序不一致统一排序缩短事务时间主从延迟高单次更新行数太多binlog事件巨大拆批控制单批行数触发max_allowed_packet单条SQL文本超限调大批次限制或降低单批行数更新后数据与预期不一致WHERE条件不规范、ID有重复先用SELECT统计影响行数验证清单6. 我最后沉淀下来的几条习惯做批量更新做得多了你会形成一些自己的肌肉记忆我把自己一直遵守的几条写在这里算不上什么高深理论但关键时刻很管用。第一能拆批就拆批。即使CASE WHEN性能很好我也不建议一次性更新上万行。把批大小控制在500到1000行既能压住SQL文本长度又能让锁持有时间和主从复制压力都处在安全线内。批与批之间如果业务允许可以加非常短的间隔给从库一点喘息时间。第二更新前默认先做备份。很多人觉得测试环境验证过了生产库直接跑没问题。但生产数据往往有测试环境没模拟出来的脏数据、重复ID、历史逻辑差异。批量更新前用CREATE TABLE xxx_bak AS SELECT ...备份一下涉及到的行成本很低收益极高。不需要每次都做但凡是涉及线上核心业务表、影响行数超过一百这个动作我不会省。第三事后必须校验。执行完批量更新别急着告诉业务方“跑完了”。我会从更新清单里随机抽几条和数据库里的最新值比对同时用SQL汇总一下影响行数是否和预期一致。少数派异常数据能在校验这一步被拦下来等业务反馈就晚了。关于“mysql批量更新语句”网上方案很多但真正决定成败的往往是一些细节批次怎么拆、事务怎么提交、版本差异怎么处理、更新逻辑怎么兜底。把这些细节都照顾到你基本就不会在批量更新上翻车了。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
