前几天有个同事跑过来找我说生产库有一张表需要按业务规则修正 4000 多条记录他写了个 for 循环一条条 UPDATE预计要跑半小时。我把他的脚本拿过来整理成 MySQL 批量 UPDATE 之后跑完不到一分钟。这种事在 MySQL 开发里其实很常见数据订正、价格调整、状态流转、同步远程表数据说到底都是同一个需求——把一张表里的一批记录更新成各自不同的目标值。很多人第一反应是逐条写 UPDATE或者用程序循环发送这不能说错但真的很不经济。MySQL 批量 UPDATE 的两种主流做法一种是用 CASE WHEN 把多条更新压进一条 SQL另一种是借助临时表或子查询用 UPDATE JOIN 一次性关联更新。这篇文章我就把这两种方式从头到尾捋一遍包括适用边界、性能差异以及我在生产环境里实打实踩过的坑。1. 先搞清楚什么样的更新才算“批量UPDATE”1.1 你写的SQL不是真的批量UPDATE先纠正一个常见的概念偏差。很多人口中的“批量UPDATE”其实是这样的for (Long id : idList) { jdbcTemplate.update(UPDATE article SET status ? WHERE id ?, statusMap.get(id), id); }这种写法叫“循环单条更新”本质上是若干条独立的 UPDATE 语句依次执行。它和 MySQL 批量 UPDATE 是两个维度的事情。批量 UPDATE 指的是单条 UPDATE 语句影响多条记录而且每条记录被设置成不同的目标值不是简单的把所有行设成同一个值。为什么这个区分很重要因为这两种做法在数据库层面的成本完全不同。循环单条更新每一条 UPDATE 都要做一次 SQL 解析、权限检查、事务提交网络往返 N 次如果正好遇到事务中执行还会积累 N 次行锁和 undo 日志。批量 UPDATE只有一条 UPDATE 语句解析一次执行计划生成一次实际是一次原子操作。尤其当数据量比较大的时候这两者的耗时和锁竞争情况差距是数量级的。所以判断你的操作是不是真正的批量 UPDATE标准很简单是不是一条 UPDATE 语句完成了 N 行数据的差异化更新。是就是不是那还停留在“循环”阶段。1.2 一次批量UPDATE背后发生了什么既然要谈这两种方式就得先知道一条 UPDATE 在 InnoDB 里是怎么工作的。这样后面讲 CASE WHEN 和 JOIN 的性能差异你才能理解为什么不是“SQL 越短越快”。一条 UPDATE 执行时大致经历这么几个关键点Server 层收到 SQL进行词法解析和语法解析生成解析树。优化器选择执行计划决定走主键还是二级索引决定扫描多少行。InnoDB 按执行计划逐行找到符合条件的记录给每一行加排他锁X 锁。在修改前先把旧值写入 undo log用于事务回滚和 MVCC。更新聚簇索引和涉及的二级索引产生新的数据版本。提交时把变更写入 redo log并把 binlog 写入相关日志。注意行锁是一行一行加的。批量 UPDATE 一旦影响的行数过多持锁时间就会变长其他事务更新同一条记录就会阻塞。这还是“批量”吗是。但你得为此承担锁竞争的风险。所以批量 UPDATE 并不是万能的它解决的问题是“减少解析和网络开销”不是“减少锁开销”。打个比方循环单条更新像一个工人跑十趟搬十箱货每趟都要登记一次批量 UPDATE 像工人开了一辆小车一次性把十箱货拉走但如果你让这辆小车一次拉一千箱过道还是会被堵死。这个“过道会不会堵”就是 MySQL 批量 UPDATE 设计时必须考虑的问题。2. 方式一CASE WHEN一条SQL更新多行2.1 基础写法把若干UPDATE合并成一个先看应用频率最高的写法。假如现在要根据用户 ID 更新一批账号状态ID1001 的用户状态改成 2ID1002 的用户状态改成 3ID1003 的用户状态改成 2。常规写法是三条 UPDATEUPDATE user SET status 2 WHERE id 1001; UPDATE user SET status 3 WHERE id 1002; UPDATE user SET status 2 WHERE id 1003;用 CASE WHEN 合并成一条UPDATE user SET status CASE id WHEN 1001 THEN 2 WHEN 1002 THEN 3 WHEN 1003 THEN 2 ELSE status END WHERE id IN (1001, 1002, 1003);这条 SQL 的思路很简单对于每一行先取它的 id然后在 CASE 里做匹配命中了就设置成对应值没命中的用 ELSE 保持原值。为什么要写 ELSE status这是新手最容易漏掉的地方。如果不写 ELSECASE 表达式在找不到匹配时会返回 NULL。也就是说如果一个 id 不在你的预期列表里但 UPDATE 语句因为 WHERE 条件写漏或其他原因扫到了这一行status 会被直接置成 NULL。这个坑我见过不止一次生产环境里踩一次就够肉痛的了。所以凡是用 CASE WHEN 做批量更新务必给每个 CASE 写 ELSE 原字段除非你有充分理由确认所有扫到的行都在匹配范围内。WHERE 条件也不能省。有人觉得 CASE WHEN 已经限制了更新行WHERE 加不加无所谓。实际上没有 WHEREMySQL 会扫描全表给所有行加锁哪怕你最终只改了 3 行锁的影响也波及全表这在线上环境很容易引发连锁阻塞。CASE 除了“简单 CASE”写法还有“搜索 CASE”写法适合按条件区间批量更新。比如UPDATE product SET price CASE WHEN category A AND stock 100 THEN price * 0.9 WHEN category B THEN price * 0.85 ELSE price END WHERE category IN (A, B);这种写法适合“同一批记录遵循不同规则”的场景。搜索 CASE 更灵活但可读性也要靠注释保命别以为一周后你还记得每个分支是在干什么。2.2 多字段同时更新一个CASE不够就再加一个业务里经常遇到的不只是单字段更新而是同一批记录要同时更新多个字段。比如用户积分、等级、备注同时变化UPDATE member SET points CASE id WHEN 1 THEN 100 WHEN 2 THEN 200 END, level CASE id WHEN 1 THEN VIP1 WHEN 2 THEN VIP3 END, remark batch-update-by-id, update_time NOW() WHERE id IN (1, 2);这里有三个实操注意点第一每个要更新的字段都单独写一个 CASE不要试图用一个小聪明去省代码。CASE 的分支条件和顺序要尽量保持一致否则会出现 ID1 的 points 更新了level 却因为分支没匹配到被写成 NULL结果比不更新还难看。第二不想更新某行时对应的 CASE 一定要有 ELSE 原值。如果某个字段只在部分记录上需要改比如 level 只给 ID1 改那么 level 的 CASE 要写成level CASE id WHEN 1 THEN VIP2 ELSE level END否则 ID2 的 level 会变成 NULL。第三不要把update_time NOW()漏掉。批量更新场景里统一更新时间戳是追踪数据修正的好习惯。否则数据被动过了你连什么时候动的都不知道。如果你用别的字段做 WHERE 条件比如按类型更新多字段 CASE 的分支对应关系就更要仔细核对。我的习惯是先写一条等价的 SELECT把 CASE 表达式直接查出来看一眼再改成 UPDATE。比如SELECT id, CASE id WHEN 1 THEN 100 WHEN 2 THEN 200 END AS new_points, CASE id WHEN 1 THEN VIP2 ELSE level END AS new_level FROM member WHERE id IN (1, 2);确认 new_points、new_level 这些列的值没有意外再执行 UPDATE。这条检查能挡住大部分肉眼漏看的错误。2.3 动态生成SQL的边界条件工作中 CASE WHEN 批量更新的值很少是手写的多半来自接口参数、Excel 表格或者前端多选操作。这时候就要在应用层拼 SQL。以 Java 为例用 MyBatis 的foreach拼 CASE WHEN或者自己在代码里用 StringBuilder 拼都行。但是有几个边界条件必须注意行数不能太多。我一般把 CASE WHEN 单条 SQL 的行数控制在 500 行以内。如果超过 1000 行SQL 文本会变得很长解析时间和网络传输时间都会显著增加优化器执行计划生成也可能更慢。一个 500 行的 CASE WHEN 问题不大一个 5000 行的 CASE WHEN 会让我在测试环境多看两眼执行计划。注意 max_allowed_packet。一条批量 UPDATE 的 SQL 文本太长超过 MySQL 的max_allowed_packet会直接报错。默认值是 64MB看起来很大但如果你拼接的是每条记录包含多个字段的大字符串还是可能撞上。可以先测一下如果 SQL 超过几 MB就该考虑换方案了。注意 SQL 注入。通过接口传进来的 id 或值拼进 SQL 之前必须做类型校验。id 必须转成整数字符串字段要参数化绑定不能用字符串直接拼。CASE WHEN 批量更新一旦被注入等于给别人一个全表 UPDATE 的入口这是我最忌讳的事。索引必须要用上。WHERE 里的id IN (...)最好命中的是主键或唯一索引。如果命中的是普通索引CASE WHEN 也能执行但可能产生回表性能下降如果没有索引全表扫描更难受。总之CASE WHEN 适合什么场景我的判断是数据量小、来源固定、临时起意的规则更新。比如几百个产品的价格微调、几十个用户的状态订正。超出这个范围就要考虑第二种方式了。3. 方式二UPDATE JOIN临时表和子查询才是大杀器3.1 用临时表批量更新最靠实的做法当你要更新的数据不是“临时想起来的那几百条”而是来自一张外部表、一份文件、或者一个比较复杂查询结果还硬塞进 CASE WHEN 就会很痛苦。这时候我更推荐用临时表 UPDATE JOIN。做法分三步。第一步建一张临时表把“每行该更新成什么样”放进去CREATE TEMPORARY TABLE tmp_member_update ( id INT PRIMARY KEY, points INT, level VARCHAR(10) );第二步把待更新的数据导入临时表。数据可以来自程序批量插入、远程表同步、Excel 导入或者干脆就是从原表筛选出来的INSERT INTO tmp_member_update (id, points, level) VALUES (1, 100, VIP1), (2, 200, VIP3), (3, 150, VIP2);第三步执行 UPDATE JOINUPDATE member t INNER JOIN tmp_member_update u ON t.id u.id SET t.points u.points, t.level u.level, t.update_time NOW();这条语句的意思是用临时表的数据去匹配 member 表匹配上就更新。你不需要在 UPDATE 里写一大堆 CASE 分支待更新的数据是什么直接读取临时表字段即可。这个方案最大的价值是数据和代码分离。临时表里的数据可以先查一遍再更新。我通常在执行 UPDATE JOIN 前先跑SELECT t.id, t.points, u.points AS new_points FROM member t INNER JOIN tmp_member_update u ON t.id u.id;确认新值和预期一致再执行 UPDATE。这个“先查后改”的习惯救过我好几次。3.2 直接JOIN子查询的坑不能碰原表的限制有人会问能不能不建临时表直接在 UPDATE 里写一个子查询比如UPDATE member t INNER JOIN ( SELECT id, points, level FROM member WHERE points 100 ) u ON t.id u.id SET t.points 100;这在 MySQL 里是行不通的会报一个很经典的错误ERROR 1093 (HY000): You cant specify target table t for update in FROM clause意思很明确UPDATE 的目标表不能同时出现在 FROM 子查询里。MySQL 不允许你在更新一张表的同时从同一张表里读取数据来作为更新依据。这是 MySQL 的限制不是 SQL 标准的问题。绕开的方法有两种。一种是真建临时表把原表数据先拷出来再 JOIN。另一种是嵌套一层派生表UPDATE member t INNER JOIN ( SELECT id, points FROM ( SELECT id, points FROM member WHERE points 100 ) x ) u ON t.id u.id SET t.points 100;这种写法在某些 MySQL 版本下能骗过限制但性能并不好而且读起来相当别扭。遇到这种情况我的建议很简单老老实实建临时表。临时表用完自动销毁不占持久化空间也不用担心影响业务数据。它的成本就是多写几步 SQL但换来的是稳定和可控。3.3 JOIN更新的去重问题使用 UPDATE JOIN 时有一个细节特别容易被忽略临时表里关联字段的重复值。如果临时表里的 id 有重复比如 id1 出现了两次一次 points100一次 points200那么原表 id1 的记录会被 JOIN 到两行。MySQL 在 UPDATE JOIN 时碰到这种情况结果不是报错而是更新多次最终结果取决于最后匹配到哪一行这等于把结果交给随机顺序谁都不能保证一定是想要的值。所以在把数据导入临时表之后第一件事是检查唯一性SELECT id, COUNT(*) FROM tmp_member_update GROUP BY id HAVING COUNT(*) 1;这个查询应该返回 0 行。如果返回有数据说明你的待更新数据源有问题需要先去重。可以直接用INSERT INTO ... SELECT ... GROUP BY id来重写临时表或者在导入时就加DISTINCT。另一个必须注意的点是索引。UPDATE JOIN 能不能走索引直接决定性能。临时表的 id 字段建立主键或唯一索引是必须的原表侧的关联字段也就是 JOIN 里 ON 的那个字段也必须有索引。如果原表关联字段上没有索引UPDATE 会退化成全表扫描每一行都去临时表里找一遍大表直接能把数据库拖垮。这里有个容易踩的细节不要在 ON 条件里对字段做函数运算比如ON t.id 0 u.id或者ON CONCAT(t.id, ) u.id。一旦写了这种条件MySQL 的优化器基本就放弃索引了。老老实实用裸字段等值关联才是批量更新的正确姿势。4. 两种方式的对比与选型别只看SQL写得爽不爽4.1 不同维度下的适用场景我把两种方式放在一张表里对比这样选型的时候可以照着看。维度CASE WHEN 批量UPDATEUPDATE JOIN 临时表适合行数几十到几百条几百到几十万条都可以数据来源少量固定值、代码动态拼接外部文件、远程表、复杂查询结果是否需要额外表不需要需要临时表或子查询关联/条件字段索引要求WHERE 里的 id 最好走主键或唯一索引临时表关联字段必须有索引SQL 可读性行数一变长阅读困难更新逻辑清晰数据源可检查维护成本修改规则要改 SQL 文本修改数据只要改临时表数据校验需要把 CASE 表达式翻译成 SELECT 检查可以直接 SELECT JOIN 结果检查锁影响行数多时同样持锁时间长可以通过分批 JOIN 控制锁范围这张表最重要的不是谁更好而是告诉你没有绝对最好的方案只有当前场景下最合适的方案。300 行的配置表更新你非要去建临时表属于杀鸡用牛刀50 万行历史数据订正你还用 CASE WHEN 拼一个几十 MB 的 SQL那就是把自己往火坑里推。4.2 实测经验什么情况下CASE反而更慢我在本地和测试环境做过对比测试模拟一张 50 万行的订单表更新其中不同数量的记录。受索引状态和数据分布影响具体数字不会完全一致但量级可以参考更新 100 行CASE WHEN 和 UPDATE JOIN 几乎都在 50ms 以内差距可忽略。这时我更愿意用 CASE WHEN因为不用建临时表。更新 2000 行CASE WHEN 的 SQL 文本明显变大大概需要 600ms 到 800ms临时表 JOIN 走主键关联大概 400ms 左右。差距开始出现。更新 20000 行CASE WHEN 的 SQL 已经好几 MB执行时间会拖到几秒而且测试过程中出现过锁等待临时表 JOIN 仍然能控制在 1s 左右。数据量越大JOIN 的优势越明显。CASE WHEN 慢在哪我觉得主要是两个方面。一是 SQL 文本庞大解析和网络传输成本上升二是 UPDATE 每行时都要执行一次 CASE 的分支判断20000 行的分支判断累积起来非常可观。JOIN 方案则是先通过索引定位匹配关系再直接读取新值写入执行路径更短。反过来如果只是几十行CASE WHEN 不需要额外建表也没有 JOIN 的匹配开销反而更轻快。我见过有人为了更新 20 条数据特意建临时表我只能说理解严谨但真的没必要。4.3 大更新前必须做的检查清单不管用哪种方式更新行数一旦超过一千就要当成一个“小项目”来对待。我的习惯是过一遍下面这个清单先备份。最简单的办法是CREATE TABLE member_bak_20260601 AS SELECT * FROM member WHERE 更新条件;这行 SQL 几分钟就搞定关键时刻能救命。验证影响行数。把 UPDATE 改成同条件 SELECT比如SELECT COUNT(*) FROM member WHERE id IN (...)确认和预判一致再执行 UPDATE。检查关联字段索引。用EXPLAIN看执行计划确保没有全表扫描。评估事务大小。如果更新行数超过一万默认自动提交的单条 UPDATE 也会是一个大事务要有心理准备。尽量在低峰期执行。批量更新一旦锁等待影响面会扩散到其他正常查询。执行后立刻抽样验证。比如SELECT * FROM member WHERE id IN (1,2,3)对比更新前后的值确认没有意外。我见过太多线上事故都是“直接执行”造成的。多花五分钟做检查比回滚两小时舒服得多。5. 批量UPDATE最容易翻车的三个细节5.1 忘记WHERE一次把全表改错的成本第一处容易翻车的地方就是 WHERE 条件。我举个例子你本来只想更新三个会员的等级UPDATE member SET level CASE id WHEN 1 THEN VIP2 WHEN 2 THEN VIP3 ELSE level END WHERE id IN (1, 2, 3);如果某次手滑把WHERE id IN (1, 2, 3)删掉了那会发生什么如果 CASE 里写了 ELSE level所有行的 level 都不会变但 MySQL 会扫描并锁定全表即使字段最终没变锁竞争一样存在照样可能拖垮线上。如果没写 ELSE level那恭喜你全表所有不匹配的会员level 全变 NULL。这种事故不叫批量更新叫“批量清空”。成本就是从硬生生改错几万条数据开始然后花几个小时恢复备份。我的防呆方法很简单把 WHERE 条件当作 SQL 的命根子。写完 UPDATE 之后先把它改成 SELECT 执行一遍确认返回行数和预期一致再把 SELECT 改成 UPDATE。多一步操作少一次事故。5.2 长事务与主从延迟批量更新的隐形代价第二个容易翻车的地方是很多人低估了批量更新对主从复制的影响。一条 UPDATE 更新几万行在主库上可能几秒钟就完成但 InnoDB 要为每一行维护 undo log事务提交前旧版本数据不能被清理。如果这时候有其他大查询或者事务没有及时提交undo 表空间会明显膨胀。如果主库开启了 binlog尤其是 row 格式批量更新会在 binlog 里记录每一行的变更前后镜像。更新 10 万行binlog 可能直接写几百 MB。从库在应用这些日志的时候是单线程的主库执行 10 秒的批量更新从库可能要多花几十秒甚至几分钟去追赶这个期间从库上的查询会看到延迟或者干脆查询不到最新数据。所以批量更新不是“想跑就跑”的。如果你的系统对从库读实时性要求很高批量 UPDATE 一定要挑业务低峰期执行并且尽量控制单条 UPDATE 的影响行数。不要问我怎么知道的——我曾经在一个白天直接跑了一条更新 30 万行的 SQL结果从库延迟 8 分钟当时运营看板上的数据惨不忍睹。5.3 如何在生产环境安全执行批量更新最后给一套可以直接抄的流程。第一步先把待更新记录的主键和当前值导到备份表例如CREATE TABLE member_bak_20260601 AS SELECT id, points, level FROM member WHERE 条件;第二步在测试环境执行同样的 UPDATE观察耗时和是否出现锁等待。测试数据量和线上不要差太多否则没有意义。第三步执行前用 SHOW 语句看当前数据库的活跃事务确认没有长事务占着关键行锁SHOW ENGINE INNODB STATUS;第四步正式执行时如果行数比较多建议手动开启事务分批提交START TRANSACTION; UPDATE member ...; -- 检查影响行数 SELECT ROW_COUNT(); COMMIT;第五步更新完成后和备份表做对比看是否有非预期变更SELECT COUNT(*) FROM member t INNER JOIN member_bak_20260601 b ON t.id b.id WHERE t.points b.points OR t.level b.level;这个结果应该等于你预期更新的行数而不是多出几条。多出几条就说明 WHERE 条件写宽了要赶紧查。这套流程看起来繁琐但能把批量更新的风险压到最低。6. 进阶思考真正的第三种“批量”是什么6.1 应用层批处理rewriteBatchedStatements既然标题说“两种方式”为什么还要提“第三种”因为很多人会把应用层的批处理也误当成批量 UPDATE。比如 JDBC 的PreparedStatement#addBatch()。不少人都以为 addBatch 之后MySQL 会把多条 UPDATE 合并成一条批量 SQL 发过去。实际上MySQL Connector/J 默认情况下不会合并每条 UPDATE 还是单独执行只是减少了网络往返的次数。如果想让它尝试合并需要设置连接参数rewriteBatchedStatementstrue。设置之后MySQL 驱动会对 INSERT 类语句做多值拼接优化对 UPDATE 类语句也有一定几率改写成批量形式。但请注意驱动改写 UPDATE 的技术本质还是生成 CASE WHEN 或类似的多行更新语句。也就是说应用层批处理只是工程实现层面的通道底层真正执行的还是我们前面讲的那两种方式之一。所以从数据库视角看我不把应用层批处理当成第三种独立的批量更新方式。它更像是“把数据攒起来发送”的优化手段。真正决定数据库承受多大压力的仍然是你最终到达 MySQL 的那条 SQL 的形态。6.2 分批UPDATE策略上限5000行不是玄学最后聊一下超大表的更新。几万行用临时表 JOIN 已经能跑得很顺但如果一张表有几百万甚至上千万行就算一次只更新其中 10 万行长时间的持锁也会让线上其他更新和查询受到影响。这时候最稳的做法是按主键范围分批更新。比如临时表里保存了所有待更新的 id 列表可以这样UPDATE member t INNER JOIN tmp_member_update u ON t.id u.id SET t.points u.points WHERE t.id BETWEEN 1 AND 5000;跑完这一批接着跑下一批UPDATE member t INNER JOIN tmp_member_update u ON t.id u.id SET t.points u.points WHERE t.id BETWEEN 5001 AND 10000;为什么推荐 5000 行一批这不是什么精确定律而是我在多次生产操作中得到的“舒服区间”。一批 5000 行通常锁持有时间在几百毫秒到一两秒对业务影响相对可控如果单行数据特别宽比如几十个字段可以再缩小到 2000 行。关键不是死盯 5000 这个数字而是让每一批 UPDATE 的执行时间短到不会引发大面积锁等待。你可以在循环脚本里带上SLEEP(1)让两批之间留出 1 秒的间隙给从库追日志的时间也给自己一个“发现不对劲就中断”的窗口。这个过程不用手动跑几十条 SQL我通常直接用 Python 脚本或存储过程来做分页切片但每批都打印影响行数和耗时方便排查。6.3 最后一点心得体会在我做了这么多批量 UPDATE 的操作之后最大的体会是SQL 怎么写从来不是最难的部分最难的是判断当前数据量适合哪种方式以及有没有提前做好回滚准备。小批量、规则灵活的场景CASE WHEN 最顺手中大批量、数据源干净、需要先验证的场景临时表 JOIN 最稳超大表场景就别幻想一条 SQL 搞定老老实实分批执行。如果你现在正准备在生产库跑一条批量 UPDATE我的建议很简单先备份再验证最后执行。遇到拿不准的行数宁可用分批方案也不要赌一把。数据库这行稳定永远比炫技重要。