用CASE WHEN可在单条UPDATE中为不同行设不同值，需统一SET、每个字段配独立CASE、必须写ELSE防NULL，WHERE应显式指定；大量数据宜用临时表JOIN更新。
用 CASE WHEN 实现单条 UPDATE 更新多行不同值
直接在一条 UPDATE 语句里给不同记录设不同值，核心就是用 CASE WHEN 表达式。它不是“批量提交”的替代方案，而是 SQL 层面的条件赋值逻辑。
常见错误是试图写多个 SET 子句，比如 SET name='a', age=10 SET name='b', age=20 —— 这会语法报错：You have an error in your SQL syntax。
正确做法是把所有要更新的字段都放在一个 SET 后面，每个字段用独立的 CASE 控制：
UPDATE users
SET
status = CASE id
WHEN 1 THEN 'active'
WHEN 2 THEN 'inactive'
WHEN 5 THEN 'pending'
ELSE status
END,
updated_at = CASE id
WHEN 1 THEN NOW()
WHEN 2 THEN NOW()
WHEN 5 THEN NOW()
ELSE updated_at
END
WHERE id IN (1, 2, 5);
-
ELSE分支必须写，否则没匹配上的行会被设为NULL（除非字段允许 NULL 且你真想要这个效果） -
WHERE条件建议显式加上，避免全表扫描或误更新 - 每个
CASE是独立表达式，可混合不同类型（如字符串、数字、函数），但同一字段的所有分支返回类型需兼容
用 JOIN 关联临时数据批量更新（适合大量行）
当要更新的行数超过几十行，或者新值来自外部系统（比如 CSV、程序变量），硬写 CASE 维护成本高，容易出错。这时更适合用临时表或派生表 + JOIN。
典型场景：从应用层拿到一批 id → new_value 映射，需要高效写入数据库。
操作步骤：
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
- 创建临时表：
CREATE TEMPORARY TABLE tmp_updates (id INT PRIMARY KEY, new_status VARCHAR(20), new_score INT) - 批量插入数据（可用
INSERT INTO ... VALUES (...), (...), ...或客户端批量导入） - 执行带
JOIN的更新：UPDATE users u JOIN tmp_updates t ON u.id = t.id SET u.status = t.new_status, u.score = t.new_score;
注意：JOIN 方式要求 tmp_updates.id 唯一，否则可能触发 MySQL 的“安全更新模式”报错：Safe update mode is enabled。解决方法是在语句前加 SET SQL_SAFE_UPDATES = 0;，但更稳妥的是确保关联键唯一。
WHERE 条件不匹配导致“看似没更新”
执行完批量更新后 SELECT 查不到变化？大概率是 WHERE 条件没命中任何行。MySQL 的 UPDATE 默认不报错，只返回 “X rows affected”，如果显示 0 就得立刻检查条件。
常见疏漏点：
- 用字符串匹配数字 ID，比如
WHERE id = '123 '（末尾空格）或WHERE id = '00123'—— MySQL 会隐式转换，但可能不符合预期 -
IN列表里混入NULL：WHERE id IN (1, 2, NULL)整个条件恒为FALSE，不会报错但也不匹配任何行 - 时间字段比较时忽略时区或精度，例如用
updated_at = '2024-01-01'匹配DATETIME字段（实际存的是'2024-01-01 14:30:00'）
性能与锁注意事项
批量更新本质是一次性持有行锁，范围越大，锁等待越明显。尤其在高并发写场景下，容易引发锁超时或死锁。
优化方向：
- 尽量缩小
WHERE范围，用主键或唯一索引列过滤；避免WHERE status = 'old'这类无索引扫描 - 单次更新控制在 1000 行以内，更多数据建议分批次（例如按
id BETWEEN ? AND ?分片） - 确认事务隔离级别，
REPEATABLE READ下大更新可能触发间隙锁，影响其他插入 - 如果只是覆盖部分字段，别在
SET里重复赋原值（如name = name），虽不报错但增加日志体积和复制延迟
最易被忽略的是：用 CASE WHEN 更新时，即使某分支没走，整个语句仍会对所有匹配行加锁 —— 锁范围由 WHERE 决定，跟 CASE 逻辑无关。
