用 CTE 驱动数据变更
把筛选逻辑写成有名字的步骤，再拿它去驱动更新或删除，可以让变更语句的意图一眼看清。两种数据库的能力不同：MySQL 允许把 WITH 写在更新或删除语句之前，但步骤内部只能做查询；PostgreSQL 更进一层，允许在步骤内部直接执行带返回结果的数据修改语句，于是「删旧表数据同时写入归档表」可以封装成一条语句。
基本信息
| 适用平台 | MySQL 8.0 / PostgreSQL 12 及以上 |
|---|---|
| MySQL 能力 | WITH 可以放在更新或删除之前，步骤内部不能写数据修改 |
| PostgreSQL 能力 | 步骤内部可写数据修改语句，并可用返回结果把数据传给下一步 |
| 执行单位 | 整条语句是一个执行单元，中途出错整体回滚 |
| 演练要求 | 先在备份表或测试库小批量验证，确认影响行数与预期一致 |
典型使用场景
- 按一批筛选出来的主键批量改状态，例如把长期未支付订单置为失效
- 在线归档：从业务表删除过期数据，同时插入历史归档表
- 数据修正时的两步走：先算出待处理集合，再据此更新或删除
- 按条件把明细数据汇总后回写统计表
- 变更多张表时用同一条语句保证口径一致
两库写法示例
两库写法示例
-- MySQL：把待处理主键先取出来，再驱动更新
WITH old_orders AS (
SELECT id FROM orders
WHERE created_at < '2023-01-01' AND status = 0
)
UPDATE orders o
JOIN old_orders t ON t.id = o.id
SET o.status = 9;
-- MySQL：同样的结构也可以驱动删除
WITH stale AS (
SELECT id FROM sessions WHERE created_at < '2024-01-01'
)
DELETE s FROM sessions s JOIN stale t ON t.id = s.id;
-- PostgreSQL：步骤内部直接做数据修改，并用返回结果接着写
WITH removed AS (
DELETE FROM sessions
WHERE created_at < now() - INTERVAL '30 days'
RETURNING *
)
INSERT INTO sessions_archive SELECT * FROM removed;
-- 执行前先确认影响范围：同条件先跑一次 SELECT 数行数能力对照
| 能力 | MySQL | PostgreSQL |
|---|---|---|
| 步骤前置于更新 | 支持 | 支持 |
| 步骤内写数据修改 | 不支持，会报语法错误 | 支持，并可用返回结果 |
| 删除并归档 | 分两条语句自行控制 | 一条语句内完成 |
| 出错处理 | 整条语句回滚 | 整条语句回滚 |
| 部分回滚 | 不支持，需靠保存点或拆批 | 不支持，需靠保存点或拆批 |
PostgreSQL 里同一条语句中的多个数据修改步骤共用同一个快照，彼此看不到对方刚做的改动。因此不要指望步骤之间按书写顺序传递「已更新的最新值」，需要传递的是被修改行的返回数据。
整条语句是一个执行单元意味着：中间任何一步失败，前面已经改好的行也会一起回滚。数据量大时先按主键区间拆成小批，把失败影响面控制在可控范围内。