PostgreSQL / 系统课时
使用 RETURNING 返回写入结果
使用 RETURNING 返回写入结果
本课解决写入后还要再次查询的问题。完成后,你能在 INSERT、UPDATE 和 DELETE 后追加 RETURNING,直接获得数据库实际写入或删除的行,并验证返回值与最终数据状态是否一致。
学习目标
- 掌握三类写操作的
RETURNING用法 - 返回自动生成主键、更新后的字段和删除前的数据
- 避免不必要的二次查询,理解返回结果的边界
INSERT、UPDATE 与 DELETE 的返回值
RETURNING 写在写操作语句末尾,可以指定列,也可以使用 *。它在执行修改语句时直接生成结果集,无须先完成写入再额外执行一次 SELECT。
三类操作的返回值存在明确区别:
INSERT RETURNING返回新插入的行,包括自动生成的标识列、默认值和表达式计算结果。UPDATE RETURNING返回更新后的行,可以直接获取修改后的字段值。DELETE RETURNING返回删除前的旧行,即使记录随后从表中消失,仍能取得被删除的数据。
下面以商品库存表为例,完整执行新增、更新、删除和最终状态检查。在独立练习数据库中运行,避免删除业务环境中的同名表。
DROP TABLE IF EXISTS lesson_inventory;
CREATE TABLE lesson_inventory (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name varchar(100) NOT NULL,
stock integer NOT NULL
);
-- 新增商品,直接返回数据库生成的主键和库存
INSERT INTO lesson_inventory (name, stock)
VALUES ('Keyboard', 10), ('Mouse', 5)
RETURNING id, name, stock;
-- 库存增加 3,返回更新后的值
UPDATE lesson_inventory
SET stock = stock + 3
WHERE name = 'Keyboard'
RETURNING id, name, stock;
-- 删除商品,返回删除前的记录
DELETE FROM lesson_inventory
WHERE name = 'Mouse'
RETURNING id, name, stock;
-- 验证最终保留的数据
SELECT id, name, stock
FROM lesson_inventory
ORDER BY id;预期结果(在新建的练习表中执行):
INSERT 返回:
id | name | stock
----+----------+------
1 | Keyboard | 10
2 | Mouse | 5
UPDATE 返回:
id | name | stock
----+----------+------
1 | Keyboard | 13
DELETE 返回:
id | name | stock
----+-------+------
2 | Mouse | 5
最终 SELECT 返回:
id | name | stock
----+----------+------
1 | Keyboard | 13从结果可以确认:新增时取得了自动生成的 id,更新后立即拿到了库存 13,删除时仍然取得了 Mouse 被删除前的库存 5。
最后一次 SELECT 只剩 Keyboard,说明 DELETE RETURNING 返回的记录并不是仍然保留在表中的记录,而是被删除操作实际影响的旧行。
常见使用场景
新增后返回主键,可直接交给后续业务逻辑;更新后返回最新状态,可避免再次执行 SELECT;删除后返回旧数据,便于记录审计日志或构造响应。
例如,订单服务新增一条记录后,可以从 INSERT RETURNING id 直接取得新订单编号,不必先写入再根据业务字段查询。库存接口执行 UPDATE 时,可以直接使用返回的 stock 构造响应,避免额外读取。
RETURNING 只返回实际受影响的行。若 WHERE 没有匹配任何记录,结果集为空,而不是返回一行包含 NULL 的记录,也不会仅因零行受影响而自动报错。
一次写操作如果影响多行,RETURNING 就可能返回多行。应用程序必须按结果集处理,不应假设每次只有一条记录,也不应依赖多行返回的默认顺序。如果业务要求确定的处理顺序,应依据主键等字段在应用层排序。
选择返回列
业务代码通常只返回必要列,避免 RETURNING * 在表结构变化后带来额外数据和接口耦合。需要多个字段时,按普通查询列清单书写即可。
例如,上面的示例使用 RETURNING id, name, stock,可以让调用方同时确认记录身份与库存值。如果新增接口只需要主键,使用 RETURNING id 即可,没必要返回整行。
还需要注意两个边界:
- 限制修改范围:
RETURNING只负责返回受影响的数据,不负责限制写入范围。UPDATE或DELETE缺少必要的WHERE条件时,可能修改或删除整张表,并返回大量记录。 - 区分返回与提交:
RETURNING表示当前语句已经产生相应的修改结果,不代表事务一定完成提交。如果随后执行ROLLBACK,这些修改仍会被撤销。需要向外部系统发布消息时,应结合事务提交结果处理。
实际使用时,先检查返回的行数是否符合预期,再使用返回的主键和业务字段。对于需要获取写入结果的接口,优先使用 RETURNING;只有确实需要读取其他未参与本次修改的数据时,再考虑额外查询。