如何在MySQL中批量更新多行数据且每行更新值都不同？-mysql教程-PHP中文网

case when实现按行赋值更新是最直接、兼容性好且无需额外权限的方案，须将case置于set右侧、每分支then值类型一致、务必加else回退原值、where限定范围防全表扫描。

如何在mysql中批量更新多行数据且每行更新值都不同？

用 `CASE WHEN` 实现按行赋值更新

这是最直接、兼容性最好（MySQL 5.0+ 全支持）、无需额外权限的方案。核心是把每行的更新逻辑写进 CASE 表达式里，而不是靠应用层循环拼多条 UPDATE。

常见错误是写成 UPDATE t SET col = ('a','b','c') WHERE id IN (1,2,3)——这会报错，因为等号右边必须是标量。

CASE 必须放在 SET 子句中，且每个 WHEN 分支的 THEN 值类型要一致（比如全是字符串或全是数字），混用会导致隐式转换，可能截断或报 Truncated incorrect DOUBLE value
务必加 ELSE col，否则没匹配上的行会被设为 NULL，不是“不动”，而是“清空”
WHERE id IN (...) 不是可选的——漏掉就变成全表扫描，哪怕只改 3 行，也可能锁住 10 万行

示例：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

UPDATE users 
SET status = CASE id 
  WHEN 101 THEN 'active' 
  WHEN 102 THEN 'pending' 
  WHEN 105 THEN 'blocked' 
  ELSE status 
END 
WHERE id IN (101, 102, 105);

`INSERT ... ON DUPLICATE KEY UPDATE` 适用场景

它不是真正意义上的“更新”，而是“尝试插入，冲突时更新”。适合你手头有一组完整记录（含主键/唯一键），且能接受“以插入为入口”的语义。

和 CASE WHEN 的关键区别在于：它要求你提供整行数据（哪怕只改一个字段），且必须触发唯一键冲突才能走更新分支。

如果某条记录的主键在表里根本不存在，ON DUPLICATE KEY UPDATE 会把它插进去——这不是你想要的“纯更新”
不能用它来只更新部分字段而不暴露其他字段值（比如你只知道要改 status，但不想碰 created_at）
性能上，当批量很大时，它比 CASE WHEN 更容易利用主键索引，因为本质是按主键做 upsert

示例：

INSERT INTO users (id, status) 
VALUES (101, 'active'), (102, 'pending'), (105, 'blocked') 
ON DUPLICATE KEY UPDATE status = VALUES(status);

大批量更新（>1000 行）该选临时表还是分批 `CASE WHEN`？

别硬扛。MySQL 对超长 IN 列表（比如几千个 ID）的优化器行为不可靠：可能放弃走索引，退化成全表扫描；生成的 SQL 也容易超 max_allowed_packet。

单次 CASE WHEN 更新建议控制在 500–800 行以内，用 EXPLAIN 确认 type 是 const 或 range，不是 ALL
真要更新几万行，优先建临时表：CREATE TEMPORARY TABLE 插入目标 ID 和新值，再用 UPDATE JOIN ——逻辑清晰、可复用、执行计划稳定
避免在存储过程里循环拼 CASE 分支：动态 SQL 难调试，预编译失效，反而更慢

临时表示例：

CREATE TEMPORARY TABLE tmp_updates (id INT PRIMARY KEY, new_status VARCHAR(20));
INSERT INTO tmp_updates VALUES (101,'active'),(102,'pending'),(105,'blocked');
UPDATE users u JOIN tmp_updates t ON u.id = t.id SET u.status = t.new_status;