从修改的行返回数据

应用写入数据后，经常需要立即取得数据库生成的主键、更新后的值或被删除行的内容。PostgreSQL 可以在数据修改语句末尾添加 RETURNING，让写入和取回结果由同一条语句完成。

RETURNING 不是通用 SQL。本文以 PostgreSQL 为主，并在最后说明常见数据库的替代方式。使用 OLD、NEW 显式读取修改前后值的示例要求 PostgreSQL 18 或更高版本。

为什么使用 RETURNING

不使用返回子句时，应用可能先执行写入，再执行一次 SELECT：

INSERT INTO products (sku, product_name, price)
VALUES ('BK-101', 'SQL 查询技巧', 79.00);

-- 错误思路：并发环境中最大编号可能属于其他请求
SELECT MAX(product_id)
FROM products;

第二条语句增加了一次数据库往返，也可能读到错误的行。使用 RETURNING 可以直接取得本次语句实际修改的行：

INSERT INTO products (sku, product_name, price)
VALUES ('BK-101', 'SQL 查询技巧', 79.00)
RETURNING product_id, created_at;

它适合以下场景：

• 取得标识列、序列或默认表达式生成的值；
• 取得经过计算、类型转换或触发器调整后的最终值；
• 展示批量更新的实际结果；
• 在删除同时保留被删行的信息；
• 把一次修改的结果继续交给同一条 SQL 中的后续步骤。

基本结构

RETURNING 位于数据修改语句末尾，返回列表与 SELECT 的选择列表相似：

INSERT INTO table_name (...)
VALUES (...)
RETURNING column_1, column_2;

返回列表可以包含：

• 目标表的列；
• 基于这些列计算的表达式；
• 字面量和数据库函数；
• 列别名；
• *，表示所有可返回列。

结果是一个结果集。修改一行就返回一行，修改多行就可能返回多行，没有行被修改时返回空结果集。

返回插入结果

插入时，默认返回新写入的行：

INSERT INTO products (sku, product_name, price, stock)
VALUES ('BK-102', 'SQL 事务实践', 88.00, 20)
RETURNING product_id, sku, status, created_at;

product_id 可能由标识列生成，status 和 created_at 可能来自默认值。应用不需要知道这些值是怎样生成的，也不需要再按业务键查询一次。

批量插入会为每个成功插入的行返回一行：

INSERT INTO products (sku, product_name, price)
VALUES
  ('BK-103', 'SQL 索引实践', 69.00),
  ('BK-104', 'SQL 并发控制', 92.00)
RETURNING product_id, sku;

不要依赖返回行的隐含顺序。若应用必须把返回结果与输入项一一对应，应携带唯一的业务标识（如 sku），再在应用层按该标识关联。

INSERT ... SELECT 同样可以使用 RETURNING，返回的是实际插入目标表的行，而不是简单重复源查询结果。

返回更新结果

UPDATE 默认返回更新后的新值：

UPDATE products
SET price = ROUND(price * 1.10, 2)
WHERE status = 'on_sale'
RETURNING product_id, sku, price AS new_price;

返回列表可以包含表达式：

UPDATE products
SET stock = stock - 1
WHERE product_id = 101
  AND stock >= 1
RETURNING product_id, stock, stock = 0 AS is_sold_out;

应用可以同时用空结果判断库存不足或记录不存在，用返回值取得扣减后的准确库存。条件判断、修改和取值位于同一条语句中，不需要先查后改。

如果使用 PostgreSQL 的 UPDATE ... FROM，RETURNING 还可以引用 FROM 中表的列。目标行必须与来源行一一对应，否则更新来源并不确定。

返回被删除的行

DELETE 默认返回删除前的行，因为删除后已经没有新行：

DELETE FROM products
WHERE product_id = 101
RETURNING product_id, sku, product_name, price;

它适合把删除内容写入应用审计日志，或确认实际删除了什么。不过只有在应用收到并持久化结果后，审计副本才真正存在；若审计必须与删除保持原子一致，应优先使用同一数据库事务、触发器或数据修改 CTE。

例如，PostgreSQL 可以将删除结果直接插入归档表：

WITH deleted_products AS (
  DELETE FROM products
  WHERE status = 'off_sale'
  RETURNING product_id, sku, product_name, price
)
INSERT INTO archived_products (
  product_id,
  sku,
  product_name,
  price,
  archived_at
)
SELECT
  product_id,
  sku,
  product_name,
  price,
  CURRENT_TIMESTAMP
FROM deleted_products;

整条语句成功时同时完成删除和归档，任一部分失败时都不会留下半成品。

只返回需要的列

RETURNING * 适合交互式探索：

UPDATE products
SET status = 'off_sale'
WHERE product_id = 101
RETURNING *;

稳定的应用接口更适合显式列名：

UPDATE products
SET status = 'off_sale'
WHERE product_id = 101
RETURNING product_id, sku, status;

这样可以减少传输量，并避免表新增列后悄悄改变接口结构或返回敏感字段。对 MERGE 尤其要谨慎：来源表和目标表常有同名列，RETURNING * 可能得到大量重复、难以辨认的字段。

可以像 SELECT 一样添加别名和表达式：

UPDATE products
SET price = ROUND(price * 0.90, 2)
WHERE sku = 'BK-102'
RETURNING
  product_id,
  price AS discounted_price,
  CURRENT_TIMESTAMP AS changed_at;

changed_at 是返回结果中的计算列；除非更新语句也把它写入表，否则它不会被持久化。

同时取得修改前后的值

PostgreSQL 18 引入了在 RETURNING 中直接使用 OLD 和 NEW 的能力。更新时可以同时取得原值、新值和变化量：

UPDATE products
SET price = ROUND(price * 1.10, 2)
WHERE sku = 'BK-102'
RETURNING
  product_id,
  OLD.price AS old_price,
  NEW.price AS new_price,
  NEW.price - OLD.price AS price_change;

默认情况下：

语句	OLD	NEW
INSERT	通常为 NULL	插入后的行
UPDATE	更新前的行	更新后的行
DELETE	删除前的行	通常为 NULL
MERGE	取决于实际执行的动作	取决于实际执行的动作

某些特殊操作会不同。例如，INSERT ... ON CONFLICT DO UPDATE 发生冲突时可以存在旧行；重写规则也可能改变通常的结果。

还可以为旧行和新行设置输出别名：

UPDATE products
SET stock = stock + 5
WHERE product_id = 101
RETURNING WITH (OLD AS o, NEW AS n)
  n.product_id,
  o.stock AS old_stock,
  n.stock AS new_stock;

设置别名后，应使用别名引用对应行。PostgreSQL 17 及更早版本不支持这套 OLD/NEW 返回语法；在旧版本中，普通 UPDATE ... RETURNING 只能直接取得新值，若必须可靠记录前后值，应考虑审计触发器、版本历史表，或在事务与适当锁保护下读取旧值。

触发器与最终结果

目标表存在触发器时，RETURNING 能看到经过触发器处理后的行。例如触发器统一修正名称或维护更新时间：

UPDATE products
SET product_name = '  SQL 实战  '
WHERE product_id = 101
RETURNING product_name, updated_at;

如果数据库触发器会执行 TRIM 并更新 updated_at，返回结果就是触发器处理后的最终值。这正是 RETURNING 优于应用自行推测结果的地方。

触发器也可能取消操作、修改其他表或抛出异常。应用不应仅凭输入推断结果，而应处理数据库真正返回的行和错误。

与 MERGE 和冲突处理结合

支持相应版本的 PostgreSQL 可以在 MERGE 后使用 RETURNING，取得每个实际插入、更新或删除的结果。由于来源行和目标行可能包含同名列，返回时应使用表别名限定字段。

INSERT ... ON CONFLICT 也可以返回最终插入或更新后的行：

INSERT INTO products (sku, product_name, price)
VALUES ('BK-102', 'SQL 事务实践（修订版）', 90.00)
ON CONFLICT (sku) DO UPDATE
SET product_name = EXCLUDED.product_name,
    price = EXCLUDED.price
RETURNING product_id, sku, product_name, price;

如果使用 ON CONFLICT DO NOTHING，发生冲突且没有插入行时通常不会返回行。应用应把空结果作为一种正常分支处理。

MERGE、ON CONFLICT 和 RETURNING 的组合能力与细节具有版本差异，上线前应按实际 PostgreSQL 版本验证。

返回结果不等于已经提交

RETURNING 表示语句已经产生修改结果，但事务仍可能回滚：

BEGIN;

UPDATE products
SET stock = stock - 1
WHERE product_id = 101
  AND stock >= 1
RETURNING product_id, stock;

ROLLBACK;

应用虽然收到一行结果，回滚后数据库仍不会保留这次修改。因此，不应在事务提交前根据返回值发送无法撤销的外部通知、扣除外部账户余额或确认任务永久完成。

需要协调数据库与消息系统时，可采用事务发件箱等模式：在同一数据库事务中写入业务数据和待发送事件，提交后再由独立流程投递。

顺序、行数与应用处理

多行 RETURNING 的结果没有隐含顺序，不能假设它与 VALUES、物理存储或主键顺序一致。RETURNING 本身也不提供通用的 ORDER BY。确需排序时，可以让应用按返回列排序，或在适合的 PostgreSQL 语句中通过 CTE 包装后查询：

WITH updated_products AS (
  UPDATE products
  SET status = 'off_sale'
  WHERE stock = 0
  RETURNING product_id, sku, status
)
SELECT product_id, sku, status
FROM updated_products
ORDER BY product_id;

应用还应区分三种结果：

1. 返回一行：单行操作成功并得到结果；
2. 返回零行：条件未匹配、冲突被忽略，或操作被数据库规则跳过；
3. 返回多行：集合操作成功，但要确认数量是否符合业务预期。

数据库驱动通常把 RETURNING 当作查询结果集处理。不要只读取“受影响行数”后丢弃结果，也不要在尚未消费完整结果集时复用不支持该行为的连接。

跨数据库差异

数据库	常见方式	说明
PostgreSQL	RETURNING	支持多种数据修改语句；显式 OLD/NEW 取决于版本
SQLite	RETURNING	支持范围和限制与 PostgreSQL 不完全相同，应按 SQLite 版本核对
SQL Server	OUTPUT inserted... / deleted...	使用 inserted、deleted 表示新旧行，语法不同
Oracle Database	RETURNING ... INTO	常与宿主变量结合，批量行为需使用对应接口
MySQL	驱动生成键接口等	没有可直接等同于 PostgreSQL 的通用 DML RETURNING

应用只需要新插入的自增主键时，优先使用数据库驱动提供的生成键接口，而不是执行 SELECT MAX(id)。需要更新或删除后的完整行时，应根据数据库能力设计事务内查询、输出子句或审计机制。

即使两个数据库都使用 RETURNING 关键字，也不能假设它们在触发器、虚拟表、旧值访问、结果顺序和可组合性方面完全一致。

使用检查清单

1. 是否只返回调用方真正需要的列，避免稳定接口使用 RETURNING *；
2. 多行操作是否携带业务键，以便可靠关联输入和输出；
3. 是否正确处理空结果、一行和多行结果；
4. 是否把返回结果理解为当前事务结果，而不是已经提交的承诺；
5. 是否需要触发器处理后的最终值，或修改前后的差异；
6. 使用 OLD、NEW、MERGE 时是否核对了 PostgreSQL 版本；
7. 删除审计是否与删除位于同一事务，而不是依赖随后可能失败的应用写入；
8. 应用是否使用参数化查询并正确消费驱动返回的结果集；
9. 多行结果是否避免依赖未声明的顺序；
10. 跨数据库实现是否使用了对应方言或驱动 API，而不是机械替换关键字。

RETURNING 的核心价值是让数据库直接交付“实际写入了什么”。它减少查询往返，也消除了应用根据输入或最大编号猜测结果的竞态条件。