MySQL不同条件批量更新不同值的正确姿势：从CASE WHEN到临时表JOIN的完整指南

经常在技术社群里看到有人问MySQL 怎么批量更新数据要求是不同条件更新不同值。 比如运营丢过来一个 Excel里面列了 300 个 SKU 的 ID 和对应的新价格让你一次性把这些价格改掉。很多人的第一反应是写几百条 UPDATE一条条执行慢不说还容易出错更怕中间某条报错导致数据对不上。也有人说用 CASE WHEN但真到了多字段更新、上万条映射、还要考虑事务和锁的时候又不知道边界在哪。这篇文章我打算把MySQL 不同条件批量更新不同值这件事彻底拆开讲。从最基础的语法到生产环境真正可用的临时表方案再到我这些年踩过的坑——包括把数据更新成 NULL、同表子查询报错、大批量更新把主库拖到延迟一条条过。适合刚接触 MySQL 但已经把单条 UPDATE 写明白的人也适合正在为一次线上批量数据变更做方案的技术同学。保证每个例子都能直接复现。1. 需求拆解批量更新“不同值”到底卡在哪一步1.1 先搞清楚批量和不同值的组合关系单条 UPDATE 大家都会写UPDATE products SET price 299 WHERE id 101;可一旦数据量变成几百上千条这么写就非常痛苦。更关键的是需求里那句不同条件更新不同值意味着每条记录要设置的值可能都不一样id101 时价格改成 299 id102 时价格改成 199 id103 时价格改成 399如果只是同一条件更新同一个值——比如把某分类下所有商品加价 10%——那一条 UPDATE 带 WHERE 就结束了谈不上批量更新不同值。真正的难点在于每行数据的目标值不同但是你又想用尽量少的 SQL 把它们一次性更新完成。这种需求太常见了。订单状态批量流转、商品价格批量调整、用户标签批量刷新、SKU 库存批量修正本质上都是同一个问题给我一批主键和目标值帮我高效更新到表里。1.2 三种常见的错误姿势分别错在哪我先说说刚入行时大家普遍会走的弯路你对照一下自己有没有中招。第一逐条 UPDATE 拼接口。用代码循环执行几百条 SQL每条都是独立事务慢是一方面更重要的是失败恢复很狼狈。假设第 150 条失败前面 149 条已经提交了你还得想办法回滚或者继续没有原子性可言。第二用多条 UPDATE 拼在同一个事务里。这个比逐条好至少能整体回滚但并发压测下锁持有时间明显变长稍微大点的表就可能把连接池打满。而且事务提交时生成的 binlog 日志量也大主从架构下主库很容易产生延迟。第三写 UPDATE 时只想着 WHERE id IN (...) 然后 SET 成一个固定值。比如想把 id 101、102、103 分别改成不同价格有人会写三条 UPDATE 用 OR 拼条件或者更离谱把目标值参数化错了三条记录全被设成同一个值。正确思路应该是让一条 UPDATE 语句内部具备按行判断、按行赋值的能力。SQL 里能干这事的就是 CASE WHEN 表达式或者想办法把映射表 JOIN 进来。1.3 写任何批量更新之前先把数据源映射准备好无论你用哪种方案第一步都不是打开 SQL 编辑器而是先看手头的数据是什么形态。所谓不同条件更新不同值本质上是三列数据条件列一般是主键比如id目标列也就是要更新的字段比如price目标值也就是每行要设置成的新值这三列数据如果来自 Excel 或 CSV通常要清洗成规范的二维表再决定走 SQL 方案还是临时表方案。不要一上来就想着写巨复杂的表达式先问自己这个映射关系是几十条、几百条还是几万条规模直接决定了技术方案选型这也是后面第 2、3 章的切分逻辑。2. 主力方案CASE WHEN 表达式把“N 条 UPDATE”压成一条 SQL2.1 基础写法等值匹配的 CASE id WHEN当映射条数不多比如几十到几百条用 CASE WHEN 是最直观高效的。基础语法长这样UPDATE products SET price CASE id WHEN 101 THEN 299.00 WHEN 102 THEN 199.00 WHEN 103 THEN 399.00 END WHERE id IN (101, 102, 103);这条语句的意思是对于id等于 101 的行price被赋值为 299等于 102 的赋 199以此类推。WHERE id IN (...)把更新范围限定在映射列表里这是整段语句的关键——后面讲坑的时候会再提。需要注意CASE id WHEN 101 THEN ...这种写法本质是CASE WHEN id 101 THEN ...的简写它只适合等值条件。如果你的条件不是一个固定值比如当price 100时打 9 折当price 100时打 8 折那要改用更通用的搜索式 CASEUPDATE products SET price CASE WHEN price 100 THEN price * 0.9 WHEN price 100 THEN price * 0.8 END WHERE active 1;这是两条路线一个按指定主键映射一个按业务规则批量改写。实际开发中前者更多后者适合规则统一的调价活动。不管哪种都满足不同条件更新不同值的核心语义。2.2 多字段同时更新的场景很多业务需求不只是一列。比如商品调整价格的同时还要更新促销状态、更新时间甚至折扣率。这时候可以在同一个 UPDATE 里对多个字段分别挂 CASE WHENUPDATE products SET price CASE id WHEN 101 THEN 299.00 WHEN 102 THEN 199.00 WHEN 103 THEN 399.00 END, promo_status CASE id WHEN 101 THEN ON WHEN 102 THEN OFF WHEN 103 THEN ON END, updated_at NOW() WHERE id IN (101, 102, 103);这种写法看着重复但执行计划非常清晰MySQL 只需要按主键或者范围扫到这三行然后逐行计算赋值表达式。每个字段的 CASE 都是独立运算所以你可以为不同字段设计不同映射。实际业务中如果字段多且有规律建议用代码生成这段 SQL而不是手敲减少出错概率。2.3 什么时候该停用 CASE WHEN性能分水岭CASE WHEN 不是万能的。我个人的经验分水岭在几千到一万条映射左右。为什么有几个硬约束SQL 文本长度受限。每个映射都要在 SQL 里占一段文本一万个映射对应的 SQL 可能已经超过max_allowed_packet的默认配置直接被 MySQL 拒绝执行。即使没超超长 SQL 在解析器里也要花更多时间。JOIN 和 WHERE 条件不好优化。外部传入的值都在 SQL 文本里CBO 很难有效评估行数极端情况下执行计划会走全表扫描。可读性和维护性急剧下降。几千行 CASE WHEN 写完肉眼检查非常困难万一某个值敲错员工根本没法通过看 SQL 发现。这时候就应该切换到第 3 章的 JOIN 方案。但几百条范围内CASE WHEN 是完胜的——它不需要建临时表不需要额外 DDL 权限一条语句整体提交天然原子。小规模数据用 JOIN 反而重了。3. 高数据量方案JOIN 临时表/映射表SQL 只负责搬运3.1 UPDATE ... JOIN 的核心思想当映射数据多到不适合塞进 SQL 文本时换个思路把映射关系先放进一张表然后用 JOIN 把目标表和映射表关联起来一次性更新。这就像你不再手动一个个告诉橱柜该放什么而是把库存清单打出来让仓管按清单批量上架。MySQL 支持这样的 UPDATE JOIN 语法UPDATE products p JOIN tmp_price_updates t ON p.id t.id SET p.price t.new_price;这里tmp_price_updates是临时表或业务映射表里面至少有两列id和new_price。JOIN 的作用是把目标表里每一行和映射表里对应的行配对SET 子句直接引用映射表的字段值。这种方案的好处很明显映射数据可以来自 Excel 导入可以来自另一个查询结果不用手写几千行 SQLJOIN 能走索引执行计划相对可控大映射集更新不依赖 SQL 文本长度数据量上限高很多3.2 临时表方案完整流程从灌数据到清理以临时表为例完整流程分四步。第一步创建临时表CREATE TEMPORARY TABLE tmp_price_updates ( id INT PRIMARY KEY, new_price DECIMAL(10, 2) ) ENGINEInnoDB;这里我用了TEMPORARY意思是这个表只在当前会话可见会话结束自动删除。注意临时表默认不会产生 binlog 日志除非设置了binlog_format相关参数所以它非常适合做中转不会污染业务库。第二步往临时表里灌数据。数据量小可以用多个 INSERT 拼在一起INSERT INTO tmp_price_updates (id, new_price) VALUES (101, 299.00), (102, 199.00), (103, 399.00);数据量大的话可以从 CSV 文件用LOAD DATA INFILE也可以用自己的代码批量写入。第三步执行 UPDATE JOINUPDATE products p JOIN tmp_price_updates t ON p.id t.id SET p.price p.new_price;第四步确认数据无误后直接在会话里结束临时表自动销毁或者显式执行DROP TEMPORARY TABLE tmp_price_updates;这套流程非常像工业流水线数据先汇入中转区校验后统一执行。生产环境我经常这么干尤其是从运营那里拿 Excel 名单的时候。3.3 不建临时表也可以直接 JOIN 子查询有时候映射数据本身来自另一张业务的表或者是一个复杂查询的结果临时表就有点多余了。可以直接在 UPDATE 里 JOIN 一个子查询UPDATE orders o JOIN ( SELECT order_id, new_status FROM order_status_change_log WHERE batch_no B20250301 ) tmp ON o.order_id tmp.order_id SET o.order_status tmp.new_status, o.updated_at NOW();这个写法的意思是先查出某批次所有待更新的映射再和订单表做关联更新。MySQL 会先把派生表构建出来再参与 JOIN。执行计划上如果派生表很小MySQL 大概率会走materialization也就是先物化成临时结果再关联如果派生表很大可能走derived_merge优化把子查询展开。无论哪种核心是让 UPDATE 能以表关联的方式获得每行对应新值。3.4 INSERT ... ON DUPLICATE KEY UPDATE另一个常见备选方案如果你手头的数据源是待写入形态而不是已存在形态还有一个被广泛使用的方案利用主键冲突触发更新。INSERT INTO products (id, price, promo_status) VALUES (101, 299.00, ON), (102, 199.00, OFF), (103, 399.00, ON) ON DUPLICATE KEY UPDATE price VALUES(price), promo_status VALUES(promo_status);ON DUPLICATE KEY UPDATE的意思是如果插入时发生主键或唯一键冲突就执行更新操作。注意VALUES()函数在 MySQL 8.0.20 开始已经被官方标记为弃用推荐写成别名方式INSERT INTO products (id, price, promo_status) VALUES (101, 299.00, ON), (102, 199.00, OFF), (103, 399.00, ON) AS new ON DUPLICATE KEY UPDATE price new.price, promo_status new.promo_status;这个方案要谨慎使用因为它有一个隐含行为如果表中本来没有对应的 id它会插入新行。如果你的目的是只更新已存在记录那就不能直接用这个方案除非你事先确认所有 id 都存在或者后面再补一个 DELETE 清理多余行。它的优势是写起来短、适合批量导入场景缺点是语义比 UPDATE JOIN 要重一些。3.5 三种方案怎么选一张表看清楚方案适用规模前置条件主要风险CASE WHEN几十到几千条映射关系能写进 SQLSQL 过长、可维护性差UPDATE JOIN 临时表几千到几十万条建临时表/映射表权限临时表数据质量问题INSERT ... ON DUPLICATE KEY UPDATE整体替换或导入场景id 必须已存在或允许插入可能误插入新数据VALUES() 在新版弃用我的习惯是小批用 CASE WHEN大批用临时表 JOIN万不得已不用 ON DUPLICATE KEY UPDATE。因为批量更新最重要的是可预期性JOIN 方案里的 UPDATE 行为最直观不存在往表里塞多余数据的副作用。4. 绕不开的避坑清单同表子查询、NULL 覆盖、锁与主从延迟4.1 查询没问题一更新就报错You cant specify target table有人写 SQL 习惯先 SELECT 验证再改 UPDATE比如先查出所有价格异常的商品再更新它们的价格。SQL 大概是这样的UPDATE products SET price price * 0.9 WHERE id IN (SELECT id FROM products WHERE price 1000);在 MySQL 里这么写经常会报错ERROR 1093 (HY000): You cant specify target table products for update in FROM clause意思是说你不能在 UPDATE 一张表的同时子查询里去引用同一张表。这是 MySQL 的历史限制。解法很简单用派生表包一层UPDATE products SET price price * 0.9 WHERE id IN ( SELECT id FROM ( SELECT id FROM products WHERE price 1000 ) AS tmp );SELECT id FROM products WHERE price 1000被包进了外层SELECT id FROM (...)这样 MySQL 里在 UPDATE 时看到的是派生表tmp不再是products本身报错就消失了。这个原理不复杂但几乎每次自查都会发现有人在这里卡半天。4.2 不加 ELSE 的 CASE WHEN会把未命中的行更新成 NULL这是 CASE WHEN 方案里造成生产事故概率最高的点。我再写一次那个经典错误版UPDATE products SET price CASE id WHEN 101 THEN 299.00 WHEN 102 THEN 199.00 END WHERE id IN (101, 102, 103);你猜结果是什么id 101 和 102 正常更新id 103 的price会被改成NULL。原因在于 CASE 表达式里如果没有 ELSE 分支且没有任何 WHEN 条件命中CASE 整体的值就是 NULL。你在 SET 里写了SET price NULLMySQL 当然照做。所以要么把 WHERE 条件写精确确保所有行都有对应的 WHEN 分支要么加一个兜底 ELSE让未命中的行保持原值UPDATE products SET price CASE id WHEN 101 THEN 299.00 WHEN 102 THEN 199.00 ELSE price END WHERE id IN (101, 102, 103);加了ELSE price之后就算 WHERE 不小心多带了一行也不会被误更新成 NULL这算是成本最低的保险丝。生产环境里我强制要求所有 CASE WHEN 批量更新必须显式写 ELSE 分支哪怕你已经把 WHERE 范围圈得很准。原因很简单业务 SQL 迭代时后来的人很可能改了 WHERE 却忘了补 WHEN 分支到时候 NULL 一波带走查起来极其痛苦。4.3 大批量更新与锁为什么会把主库拖出延迟批量更新另一个容易被忽视的坑是锁和主从延迟。InnoDB 默认在 UPDATE 时会锁定扫描到的行如果 WHERE 条件走不了索引它可能锁住大量行甚至全表。所以批量更新前先用 EXPLAIN 看执行计划确认 WHERE 条件有没有用到索引。此外一次 UPDATE 更新几万行这个事务会持有大量行锁直到提交期间其他查询如果访问这些行会被阻塞或者等待超时。主从架构下row 格式的 binlog 会为每一行变更记录 before/after 镜像几万行更新基本等于写几万条日志事件主库提交后从库需要回放很容易造成明显的复制延迟。实际操作中如果确实要更新几万行我的做法是拆批。比如更新 5 万行分成 50 批每批 1000 行批与批之间留出间隙或者用代码控制循环执行UPDATE products SET price price * 0.9, updated_at NOW() WHERE id BETWEEN 1 AND 1000 AND active 1;分批的关键是每批的 WHERE 条件要稳定且不重叠最稳妥的是按主键范围切分。别写一个永远等不到结束的超长事务那不是性能优化是给自己埋雷。4.4 临时表数据清洗别让脏映射表带偏更新临时表方案看起来很简单但数据质量是暗坑。导入 Excel 映射时可能出现的问题包括ID 是文本格式导致匹配不上、数值列里有空格、映射重复导致同一行被更新两次、或者目标值里有非法字符串。别小看这些我遇到过运营给的 Excel 里 ID 列带了不可见字符JOIN 结果一个都匹配不上但 SQL 又不报错只是影响行数是 0差点让人误判为更新成功。为了防止这类问题临时表方案加一道校验步骤非常必要。灌完数据先查一遍-- 检查映射表里有木有目标表匹配不上的 id SELECT t.id FROM tmp_price_updates t LEFT JOIN products p ON p.id t.id WHERE p.id IS NULL;如果这一步查出无辜的 id要么补数据要么删掉这些映射再继续。批量更新前多做这一步能避免更新后才发现运营给的名单里有 N 行是无效数据。类似地还要检查映射表自身是否有多余空格、重复 id 等SQL 虽然不报错但影响范围看起来会非常诡异。5. 上线前你必须做的一次“彩排”验证范围与影响行数5.1 先 SELECT 验证再 UPDATE这两条 SQL 要一模一样我在生产环境批量更新前有个铁律先跑一条一模一样的 SELECT确认影响范围再跑 UPDATE。什么意思比如准备用 CASE WHEN 更新-- 验证范围 SELECT id, price, CASE id WHEN 101 THEN 299.00 WHEN 102 THEN 199.00 ELSE price END AS new_price FROM products WHERE id IN (101, 102, 103);先看一眼返回的new_price是否和预期一致再执行正式 UPDATE。UPDATE 语句的 WHERE 和 SELECT 保持完全一致这样基本能保证你要更新的就是验证过的那批数据。特别是涉及几十个 id 的映射时肉眼核对一次返回结果比事后回滚轻松太多。5.2 用事务包住 UPDATE留好后悔药MySQL 的 InnoDB 支持事务批量更新前手动开启一个事务是非常有效的保险START TRANSACTION; UPDATE products SET price CASE id ... END WHERE id IN (...); -- 检查 ROW_COUNT()确认影响行数符合预期 SELECT ROW_COUNT();如果影响行数和映射数量对不上或者你突然发现某个值给错了直接ROLLBACK;数据恢复原样。确认无误再COMMIT;。这个习惯在多表更新、大批量 JOIN 更新时尤其有用因为它把执行和生效分开了。5.3 影响行数和预期不一致时先查数据再看代码假设映射有 300 条但 UPDATE 之后ROW_COUNT()只有 280先别慌更别急着补更新。可能原因有几个第一映射里有 id 在目标表不存在JOIN 或 WHERE IN 根本没匹配到第二有一部分行的值本来就是目标值MySQL 发现没变化在默认行为下InnoDB 实际会更新但 ROW_COUNT 显示可能有差异第三CASE 表达式里某些分支写错了命中范围比预期小。排查顺序建议是先用映射表的 id 和业务表的 id 做LEFT JOIN找出缺失项再核对 CASE WHEN 的每个值。千万不要直接写一条那我把没更新的再跑一遍的 SQL——这种补丁式操作往往会引出连锁问题先定位根因更稳。5.4 记录变更留档可追溯最后补充一个容易被忽略的经验批量更新的 SQL、映射数据、执行时间、影响行数一定要留档。不需要搞什么复杂系统哪怕把 SQL 和受影响 id 的列表存成一个文件提交到版本管理里也行。这个习惯能帮你做三件事出问题时能快速定位是哪一次变更导致的领导问起来能直接给出改了哪 300 条、为什么改、预期影响是什么的答案下次写类似脚本时还能复用这些验证过的 SQL 片段。批量更新这事技术难点不在语法而在对数据范围和数据质量的把控。我见过太多人 SQL 写得飞起但根本不验证 WHERE 范围也不确认映射数据是否干净结果一条 UPDATE 下去几千条记录全变成 NULL 或者错误值。做好验证、留好备份、分好批次这比记住任何高级语法都重要。写在最后写了这么多回头总结一下我自己的使用习惯单条几十到几百的映射无脑用 CASE WHEN但一定加 ELSE 兜底上千到几万的映射建临时表 JOIN先做映射校验再更新几万以上直接拆批分批提交避免主从延迟和长事务锁问题。每次执行前用 SELECT 验证一次范围UPDATE 外面套一个事务影响行数确认无误再提交。这套流程看着繁琐但它在关键时刻救过我很多次。批量更新这件事稳比快重要清晰比炫技重要。希望这篇内容能帮你少走一些我曾经走过的弯路。