企业官网建设流程全解析

先讲一个特别普通的场景。运营同学在后台勾选了两千多条商品，要求把其中一千二百条改成"下架"，八百条改成"售罄"，剩下的改成"清仓"。你盯着这张商品表，第一反应是写一条UPDATE product SET status='下架' WHERE id IN (...)，但下一秒就卡住了——因为每一行的目标状态都不一样，普通 UPDATE 只能把所有行改成同一个值。

这个需求就是典型的 MySQL 批量 UPDATE。很多后端同学都会在这里纠结：是拼一条大 SQL 用 CASE WHEN 把每行的值都映射进去，还是老老实实在代码里循环一条条 update？这两条路线我都大量用过，也踩过不少坑。这篇文章不讲虚的，直接把两种主流方式、底层原理、实测数据和避坑清单一次说清楚。

1. 批量更新不是"多行写UPDATE"这么简单

1.1 批量更新的真实需求形态

先搞清楚批量更新的本质。它跟批量插入不一样，插入是往表里加新数据，而批量更新是对已有的一组行做状态流转或者字段修正，而且绝大多数时候，每行要改成的值都不一样。

真实业务里常见的是这三种形态：

• 同一张表，按主键 ID 逐行映射新值，比如根据 ID 列表把用户状态改成不同的枚举值。
• 同一组 ID，要同时更新多个字段，比如状态、积分、备注、更新时间一起改，但每行的目标值各不相同。
• 数据来自另一张表或文件，需要关联目标表批量覆盖字段。

前两种用"一条 SQL 里的 CASE WHEN"最顺手，第三种用"UPDATE JOIN"更合理，后面会单独讲。但如果每行更新的条件和字段完全没法统一，那循环逐条更新反而更合适。

1.2 常见的错误姿势

我见过最多的错误写法，是在代码里 for 循环，每循环一次执行一次 UPDATE，然后每条都 commit。数据量小的时候看不出问题，一旦数据量到几千上万，性能立刻崩盘。

还有一种错误姿势：为了图省事，用多条 UPDATE 语句拼在一个 SQL 字符串里，中间用分号分隔，再把 JDBC 连接串加上allowMultiQueries=true。这个方案我极度不建议在生产环境用，一是 MySQL 驱动默认关闭多语句执行，打开了反而引入 SQL 注入风险，二是这种字符串几乎没法做参数化，仔细排查起来非常痛苦。

1.3 两条路线怎么选

批量更新从实现上大致分成两大流派：

• 一条 SQL 派：用CASE id WHEN ... THEN ... END把每行的新值映射进一条 UPDATE，配合WHERE id IN (...)一次执行完成。
• 循环派：在应用层 for 循环，逐条或分批执行 UPDATE，通过事务批量提交来控制性能。

两条路线不冲突。我自己的判断标准很简单：能枚举出每行的目标值，就优先一条 SQL；条件差异极大或者需要逐行做计算，就走循环。下面分别拆开讲。

2. 方式一：一条SQL，用CASE WHEN按ID映射新值

2.1 单字段版本长这样

先看最基础的单字段更新需求。假设要把 id 为 1001、1002、1003 的三个用户状态分别改为 1、0、2，SQL 这样写：

UPDATE t_user SET status = CASE id WHEN 1001 THEN 1 WHEN 1002 THEN 0 WHEN 1003 THEN 2 END WHERE id IN (1001, 1002, 1003);

这段 SQL 的核心是CASE id WHEN ...。它的执行逻辑是：MySQL 逐行扫描WHERE id IN (...)命中的行，对每一行取出当前 id，去匹配WHEN后面的值，命中哪个分支就把该分支THEN的值赋给 status。

换句话说，这张"映射表"直接写在 SQL 文本里了。UPDATE 语句本来只能对所有行赋同一个值，CASE 表达式把"每行各赋什么值"这件事变成了"每一行自己去查表"。

注意：WHERE id IN (...)绝对不能省略。不写 WHERE，CASE 表达式同样会对每一行求值，但没被WHEN覆盖到的行，因为 CASE 没有 ELSE 分支，会被直接更新成 NULL，这个后果非常严重。

2.2 多字段版本：每个字段一个CASE

业务里更多是同时更新多个字段，比如状态、积分、备注一起改。这时候 SQL 变成每个字段单独写一个 CASE：

UPDATE t_user SET status = CASE id WHEN 1001 THEN 1 WHEN 1002 THEN 0 WHEN 1003 THEN 2 END, score = CASE id WHEN 1001 THEN 90 WHEN 1002 THEN 65 WHEN 1003 THEN 80 END, updated_at = NOW() WHERE id IN (1001, 1002, 1003);

多字段版本有个非常隐蔽的坑：两个 CASE 里WHEN的顺序必须保持同构。比如 status 这个 CASE 是 1001→1、1002→0、1003→2，那 score 这个 CASE 也应该按 1001、1002、1003 的顺序写。如果哪天哪只手抖了，把 score 写成了 1002、1001、1003，SQL 不会报任何错，但 1001 的分数会被写进去 65，1002 的分数会变成 90，两条数据静默写反。

这种错误在测试环境很难发现，因为表里可能恰好有大量 0 分、空值，很多测试用例根本不校验准确值。我的习惯是拼这种 SQL 之前，先把三列并排放在表格里：ID、status、score，逐行核对顺序，再生成语句。

2.3 它为什么快：从执行原理拆解

一条 CASE WHEN 语句跟 N 条循环 UPDATE 相比，性能差异来自几个层面：

• SQL 解析只发生一次。MySQL 收到一条完整语句，只需要做一次词法分析、语法分析、生成执行计划。循环方式每次都要重复这个流程。
• 网络往返只有一次。对远程数据库来说，这是最致命的一项。每次发 SQL 等结果都是一次 RTT，局域网里 0.5ms，公网 2~5ms，一万行循环就是一万次往返，光网络等待就是几十秒起步。
• 事务开销小。一条 UPDATE 在一个事务内完成，redo 刷盘、锁获取释放、binlog 写入都只发生一轮。循环逐条并逐条 commit 的话，每次 commit 都要做一次组提交和刷盘。
• 从库回放成本低。如果是 statement 格式的 binlog，一条大 SQL 到从库也只需执行一次；循环方式会在从库回放一万条语句，每条都有锁等待和解析成本。

所以它不是"看起来更快"，而是每一层都在省钱。

2.4 三个翻车点：漏ELSE、超长SQL、IN列表过大

第一个坑：CASE 里漏了某些 ID。这个问题比我预想的常见。假设 WHERE IN 里有 1001~1005 五个 ID，但 CASE 只写了 1001、1002、1003，那么 1004 和 1005 行会被更新成 NULL。因为 SQL 里没有 ELSE 兜底，CASE 对未匹配行返回 NULL，而 SET status = NULL 是会成功执行的。

要避免，最稳妥的办法是给 CASE 加 ELSE，把不在映射里的行保持原值：

SET status = CASE id WHEN 1001 THEN 1 WHEN 1002 THEN 0 ELSE status -- 保持原值 END

第二个坑：SQL 超长。一万个 ID 拼成 IN 列表，再加上每个 ID 在 CASE 里出现一次，语句长度很容易到几百 KB。MySQL 有max_allowed_packet限制，默认通常是 64MB，但很多线上配置只调到 16MB 甚至更低。可以用下面的命令查看：

SHOW VARIABLES LIKE 'max_allowed_packet';

如果单条 SQL 长度逼近甚至超过这个值，会直接报PacketTooBigException或者通讯断开的异常。所以实践上我一般把单批控制在 500~1000 行以内，超过就拆分成多条执行。这样既避免包超限，也能降低一条语句持锁时间。

第三个坑：IN 列表过大导致优化器选错路径。一万个 ID 的 IN 列表，MySQL 优化器在展开时会有很大的判断开销，有时会放弃走主键索引而选择全表扫描。虽然 InnoDB 对于主键WHERE id IN (...)通常还是会做多个 primary key lookup，但列表过长时扫描成本会明显上升。所以即使一条 SQL 能写，我也不建议一次塞几万个 ID，分块执行更可控。

3. 方式二：循环逐条更新，灵活但别裸写

3.1 最朴素的循环为什么慢

有的同学会在应用层这样写：

for row in rows: cursor.execute( "UPDATE t_user SET status = %s WHERE id = %s", (row["status"], row["id"]) ) connection.commit() # 每次循环都提交

先不说这段代码能不能跑，单看性能，它把前面说的所有劣势全踩了一遍：每条 UPDATE 一次 SQL 解析、一次网络往返、一次事务提交和刷盘。假设一条 UPDATE 本身执行只要 0.2ms，但网络往返 2ms，事务提交 1ms，那一万行算下来光额外开销就有 30 秒往上。如果你面对的还是一条公网数据库，这个数字还会成倍放大。

更麻烦的是，逐条提交时每条 UPDATE 都是独立事务，意味着每条都要重新拿锁、写 undo、写 binlog，总体的资源消耗完全不可控。

3.2 事务分批提交才是正经做法

同样是循环，正确的做法是攒一批再提交。每 500 行或 1000 行 commit 一次，既不至于让事务过于庞大，又能大幅减少提交开销。用 Python 示范一个批量提交的架子：

batch_size = 500 for i in range(0, len(rows), batch_size): batch = rows[i:i + batch_size] for row in batch: cursor.execute( "UPDATE t_user SET status = %s WHERE id = %s", (row["status"], row["id"]) ) connection.commit()

这里有个细节值得解释：为什么不是攒够一万行一次性 commit？因为一个大事务在 commit 前会持有大量行锁，期间任何其他事务想更新这批数据都会被阻塞。同时 InnoDB 的 undo log、binlog 缓存也会膨胀，如果中途某个 update 失败回滚，回滚代价也很高。500~1000 行一批是成本和风险的中间值，我实测下来最省心。

如果用的是 JDBC，还有一个参数可以开：rewriteBatchedStatements=true。这个参数对批量 INSERT 的优化非常明显，能把多条 INSERT 合并成一条多值语句；但对 UPDATE 的收益有限，MySQL 驱动不会把多条 UPDATE 改写成一条 CASE WHEN，它只能减少一些额外往返。所以别指望它解决循环 UPDATE 的全部问题。

3.3 哪些场景确实该用循环

循环不是性能毒药，它只是被很多人用错了。以下场景我推荐老老实实循环：

• 每行的 SET 字段完全不同。比如这一行要改 status，下一行要改 score，再下一行要改 name，没法用统一的 CASE 模板归纳。
• 更新前需要逐行计算。比如先调用外部接口判断结果，再根据返回值决定这行更新成什么，数据没法提前拼进一条 SQL。
• 数据量本来就很小。几十条以内，循环和一条大 SQL 的性能差异可以忽略，代码可读性反而更好。
• 需要精确判断每行影响数。比如要区分"更新成功 0 行"和"这行不存在"，逐行执行返回的 affected rows 更直观。

循环派的核心是：少提交、复用语句、分批锁。做到这三点，循环的性能不会差到不可接受。

4. 实测对照：1万行数据，两种方式差距到底多大

4.1 测试口径说明

我拿本地环境做过一次简单的对照测试，数据量级仅供参考。表结构是一张 50 万行的 t_user 表，InnoDB，主键 id，目标更新其中 1 万行。三种执行方式分别是：

• 方式一：一条 CASE WHEN 大 SQL，1 万行一次执行。
• 方式二：循环 UPDATE，每 500 行 commit 一次，共提交 20 次。
• 方式三：循环 UPDATE，每行单独 commit。

测试环境是本地 MySQL 8.0，SSD，网络 RTT 基本为 0，所以这个对比更接近"纯数据库开销"。如果你的应用和数据库不在同一机房，网络延迟会把方式二和方式三的耗时进一步拉大。

4.2 结果对比表

结果大致是这样的：

方式三的耗时波动很大，主要看 redo 刷盘频率和当时系统负载。方式二在本地网络下已经能看，但放到跨机房环境，20 次 RTT 也会被放大。方式一无论哪种环境都是最快的那档。

4.3 选型判断表

把几种典型场景直接对应到方案，可以当成一个快速决策表：

5. 进阶思路：UPDATE JOIN 与 INSERT ON DUPLICATE KEY UPDATE

5.1 临时表 + UPDATE JOIN 一次搞定跨表更新

如果批量更新的数据不是手填的，而是来自一段导出文件、另一张业务表或者数仓同步表，那我更推荐"导入临时表 + UPDATE JOIN"的组合。先把源数据塞进一张带主键的临时表，再一次性关联更新目标表：

-- 第一步：源数据导入临时表，并为主键建立索引 CREATE TEMPORARY TABLE t_temp ( id INT PRIMARY KEY, status TINYINT, score INT ); -- 第二步：一次性关联更新 UPDATE t_user u JOIN t_temp t ON u.id = t.id SET u.status = t.status, u.score = t.score;

这种写法的优势是把"逐行映射"从 SQL 文本挪到了临时表里，批量更新变成了标准的表关联，SQL 长度和可读性都问题不大。性能上也很好，因为 JOIN 的关联列是主键，MySQL 可以对目标表做主键查找。

但有一个坑特别值得说：JOIN 的关联列必须是唯一索引或主键。如果临时表里的 id 有重复，JOIN 会为同一个 id 匹配出多行，MySQL 在UPDATE ... JOIN里对多行匹配不会报错，只会随机选其中一行的值来更新目标表。这个"随机"很隐蔽，数据一旦被覆盖就找不回来了。所以导入临时表后，建议先查一下有没有重复 id：

SELECT id, COUNT(*) FROM t_temp GROUP BY id HAVING COUNT(*) > 1;

5.2 INSERT ON DUPLICATE KEY UPDATE 的边界与写法

另一种常见变体是INSERT ... ON DUPLICATE KEY UPDATE。它解决的是"这行存在就更新，不存在就插入"的混合场景，严格来说不完全是批量 UPDATE，但在业务里经常被当批量更新用。

INSERT INTO t_user (id, status, score) VALUES (1001, 1, 90), (1002, 0, 65) ON DUPLICATE KEY UPDATE status = VALUES(status), score = VALUES(score);

这里有个 MySQL 8.0 才需要注意的细节：从 8.0.20 开始，VALUES()函数在ON DUPLICATE KEY UPDATE中被标记为弃用，官方推荐用行别名，写法更清晰，也避免歧义：

INSERT INTO t_user (id, status, score) VALUES (1001, 1, 90), (1002, 0, 65) AS new ON DUPLICATE KEY UPDATE status = new.status, score = new.score;

使用这个方案的前提是目标表必须有主键或者唯一索引，否则ON DUPLICATE KEY UPDATE永远走的是 INSERT 分支，不会触发更新。另外注意一点：如果唯一索引有多个（比如同时有主键和业务唯一键），那么任何一个冲突都会触发更新，提前搞清楚触发条件是哪个键。

5.3 三种方案怎么选

CASE WHEN 适合"我已经在应用层把每一行的目标值算好了"的场景；UPDATE JOIN 适合"源数据在另一张表/文件里，我要批量覆盖"的场景；ON DUPLICATE KEY UPDATE 适合"同一批数据里有新增有修改，想用一次请求全部落库"的场景。

如果问我日常怎么选，我的默认排序是：能用一条 SQL 表达清楚的，绝不循环；源数据在表里的，优先 JOIN；带插入需求的，考虑 ON DUPLICATE；剩下的才交给循环分批。

6. 批量更新的避坑清单与我的实操习惯

6.1 带着线上任务先做这四件事

无论用哪种方式，批量 UPDATE 上线前我都坚持做四件事，这几步救过我很多次：

1. 先用 SELECT 预览。把 UPDATE 原样改成 SELECT，加上同样的 WHERE 和关联条件，先看命中了哪些行、映射的结果对不对。CASE WHEN 的顺序错位、JOIN 的重复 id，用 SELECT 预览能提前暴露。
2. 手动开事务。小数据量可以直接执行，但几千上万行那种，我在命令行或工作台里一定先BEGIN，执行完看影响行数，确认无误再COMMIT。宁可在事务里多等一会儿，也别把错误的 UPDATE 直接落库。
3. 查一下max_allowed_packet。大 SQL 拼好之后，估一下语句长度，确认没有超过限制。
4. 看一下当前锁和负载。高峰期执行大更新可能把同表的读写全部堵住，我的习惯是更新前看一眼SHOW PROCESSLIST，尽量挑低峰期操作，并且分批执行，避免一条大事务长时间持有锁。

6.2 MyBatis 里批量更新最容易踩的隐藏细节

如果你用 MyBatis，最常见的是用<foreach>拼 CASE WHEN。一个可靠的写法是这样的：

<update id="batchUpdateStatus"> UPDATE t_user SET status = <foreach collection="list" item="item" open="CASE id" close="END"> WHEN #{item.id} THEN #{item.status} </foreach>, updated_at = NOW() WHERE id IN <foreach collection="list" item="item" open="(" separator="," close=")"> #{item.id} </foreach> </update>

注意两个点。第一，list为空时生成的 SQL 是SET status = CASE id END，直接语法错误，所以调用前必须判空。第二，<foreach>里拼接的是WHEN #{item.id} THEN #{item.status}，每个分支对应一行，顺序必须和传入列表一致。

还有一个 MyBatis 特有的认知误区：很多人以为用ExecutorType.BATCH批量执行 UPDATE 会自动合并成一条大 SQL。其实不是，MyBatis 的 BatchExecutor 只是复用了同一个 PreparedStatement，连续执行多条 UPDATE，减少了解析和预编译开销，但每条请求依旧是独立发给 MySQL 的。它比逐条无脑执行快，但达不到一条 CASE WHEN 的效果。另外批量执行时getUpdateCount可能返回 -2 之类的占位值，这也是很多同学排查半天才发现的问题。

6.3 我的默认选择

写到最后，分享一下我自己的默认习惯。凡是我需要手动拼 SQL 的批量更新，10 万行以内的数据，我最常用的是 CASE WHEN 搭配 500~1000 行分块执行；源数据来自文件或另一张表时，直接走临时表 + UPDATE JOIN；少量数据或者字段差异极大，才在应用层循环并显式分批提交。

再分享一个压箱底的小技巧：不管用哪种方案，更新前把WHERE条件单独跑一次 SELECT 并核对影响行数，更新后立刻再做一次抽样SELECT验证结果。批量 UPDATE 的高危程度不比 DELETE 低，多花这两分钟，比事后修复数据要划算太多。