如何利用SQL语句在UPDATE时引用字段修改前的原始值

SQL标准允许UPDATE中直接用旧值参与计算，数据库先读旧值再原子计算新值，无需子查询或变量；MySQL、PostgreSQL、SQL Server均支持，但需避免字段名与别名冲突。

UPDATE 中直接用旧值参与计算是安全的

SQL 标准允许在 UPDATE 语句的 SET 子句里直接引用被更新字段的当前值（即修改前的值），无需额外子查询或变量。数据库在执行时会先读取旧值，再按表达式计算新值，整个过程原子完成。

常见误操作是试图用 SELECT 先查再更新，不仅多一次 IO，还可能因并发导致数据不一致；或者错误地认为必须用临时变量保存旧值——其实完全没必要。

MySQL / PostgreSQL / SQL Server 都支持旧值直接运算

不同数据库对字段旧值的引用语法一致：直接写字段名即可。但要注意表达式中避免歧义，尤其是字段名和别名冲突时。

• MySQL 示例：UPDATE users SET balance = balance + 100 WHERE id = 123 —— balance 在等号右边就是更新前的值
• PostgreSQL 同样有效：UPDATE orders SET total = total * 1.08 WHERE status = 'pending'
• SQL Server 也一样：UPDATE logs SET retry_count = retry_count + 1 WHERE last_error IS NOT NULL
• 如果字段名和表别名相同（比如 UPDATE t SET t.col = t.col + 1），部分数据库（如旧版 MySQL）可能报错，建议省略别名前缀

不能在 WHERE 或 JOIN 条件里“同时依赖”新旧值

WHERE 子句在 UPDATE 执行前求值，它看到的是原始行数据；但如果你试图在 WHERE 里用一个刚被 SET 修改的字段做判断（比如 SET x = x + 1 WHERE x + 1 > 10），逻辑上可行，但实际是拿旧值计算——这没问题；真正危险的是误以为 WHERE 能看到“即将写入的新值”。

• 错误认知：UPDATE t SET a = b, b = a WHERE a > b 能交换两列？——不行，因为两字段同时基于旧值赋值，不是原子交换；正确做法是用 UPDATE t SET a = b, b = a（多数现代数据库支持，但语义仍是“用旧 b 赋给 a，用旧 a 赋给 b”）
• 真要交换且确保一致性，推荐用单条语句：UPDATE t SET a = b, b = a（PostgreSQL/MySQL 8.0+/SQL Server 均可），不要拆成两条
• WHERE 中禁止引用 SET 子句中新定义的别名（如 UPDATE t SET x = y * 2 AS new_x WHERE new_x > 10），SQL 不允许

触发器或 RETURNING 是获取旧值的补充手段

如果业务需要在更新后立刻拿到旧值（比如记录审计日志、发通知），UPDATE 本身不返回旧值，得靠扩展机制：

• PostgreSQL 可用 RETURNING OLD.*：UPDATE users SET email = '[email protected]' WHERE id = 42 RETURNING OLD.email, OLD.created_at
• MySQL 没有原生 RETURNING，需用 SELECT ... FOR UPDATE 加事务封装，但要注意锁粒度和死锁风险
• SQL Server 用 OUTPUT DELETED.*：UPDATE users SET name = 'Alice' OUTPUT DELETED.name WHERE id = 42
• 触发器里可通过 OLD（PG）或 DELETED（SQL Server）访问旧值，但触发器性能开销大，非必要不启用

最常被忽略的一点：即使你只改一个字段，整行锁仍可能被持有更久——尤其在大表上用 WHERE 匹配大量行时，旧值计算本身虽快，但锁竞争才是瓶颈。