UPDATE语句中用CASE实现状态跳转，需在SET子句中嵌套CASE，每个WHEN明确“当前状态AND动作”条件，ELSE保留原status防NULL，并配合WHERE限定合法前驱状态以阻断非法跃迁。
UPDATE 语句里怎么用 CASE 实现状态跳转
直接在 UPDATE 中嵌套 CASE 是最常用、也最可控的方式。它能避免多次查询 + 多次更新的竞态问题，同时把流转逻辑收束在一条语句里。
关键不是“能不能做”，而是“哪些跳转允许、哪些禁止”——这得靠 CASE 的条件分支来硬性约束。
- 每个
WHEN分支必须明确写出「当前状态 AND 目标动作」，比如status = 'draft' AND action = 'submit' -
ELSE status必须保留，防止意外覆盖（漏写会导致该行status变成NULL） - 不要在
CASE外再写WHERE status = 'xxx'，否则会跳过本应被拒绝的非法跳转（比如从rejected直接到published）
UPDATE orders SET status = CASE WHEN status = 'draft' AND action = 'submit' THEN 'pending' WHEN status = 'pending' AND action = 'approve' THEN 'approved' WHEN status = 'pending' AND action = 'reject' THEN 'rejected' ELSE status END, updated_at = NOW() WHERE id IN (101, 102, 105);
批量更新时如何防止非法状态跃迁
仅靠应用层校验不保险：并发请求可能绕过检查，或者事务隔离级别低导致读到过期状态。真正的防护得落在 SQL 层。
核心思路是把「合法前驱状态」作为 WHERE 条件的一部分，而不是只依赖 CASE。
- 例如：只允许从
draft → pending，就在WHERE加上status = 'draft'，这样即使action字段被恶意篡改，也不会误更新其他状态的行 - 如果要支持多起点跳转（如
pending和approved都能触发archive），WHERE就写成status IN ('pending', 'approved') - 注意：此时
CASE内部仍需重复判断，因为WHERE只控制“哪些行参与更新”，不控制“更新成什么值”
MySQL 和 PostgreSQL 对 RETURNING / OUTPUT 的处理差异
想确认哪些行真的被改了状态？或者拿到更新后的 status 做后续逻辑？不同数据库能力差别大。
- PostgreSQL 支持
RETURNING *，可直接获取更新后整行数据：UPDATE ... RETURNING id, status, updated_at - MySQL 不支持
RETURNING，只能分两步：先SELECT id FROM ... WHERE status IN (...) AND action = 'xxx'锁定候选集，再用相同条件UPDATE；或借助ROW_COUNT()判断影响行数 - SQL Server 用
OUTPUT INSERTED.*，语法位置和语义接近 PostgreSQL，但不能像 PG 那样在普通语句中直接用
为什么别把状态机逻辑全塞进触发器
看起来方便：在 BEFORE UPDATE 里统一校验、改状态。但实际线上容易出问题。
- 触发器无法被业务代码感知，调试时查日志找不到状态变更源头
- 批量更新时，触发器对每一行逐个执行，性能比单条带
CASE的UPDATE差一个数量级 - 某些 ORM（比如 Django 的
bulk_update）会绕过触发器，导致状态不一致 - 真正需要触发器的场景，通常是跨表同步或审计日志，不是状态合法性控制
状态机的核心约束必须显式、可追踪、可测试——藏在触发器里等于放弃这些。
