让数据变更“有迹可循“——KingbaseES的RETURNING子句实战技巧

最新推荐文章于 2025-04-10 18:11:20 发布

金仓拾光集

最新推荐文章于 2025-04-10 18:11:20 发布

阅读量376

点赞数 8

文章标签：数据库 sql 运维 database 金仓数据库 KingbaseES

本文链接：https://blog.youkuaiyun.com/hgsandy/article/details/147046726

版权

引言：数据追踪的痛点与解法

在日常数据库运维中，我们常遇到这样的需求：
✅ 新增记录后立即获取系统生成的唯一ID
✅ 更新数据时记录修改前后的对比差异
✅ 删除操作前留存原始数据快照

传统方案需要多次查询+事务控制才能实现，而KingbaseES的RETURNING子句让这一切变得简单高效。本文将揭秘这个宝藏功能的使用技巧。

一、RETURNING子句入门手册

通过单条SQL语句即可实现数据操作+结果返回双效合一，支持三大DML操作：

操作类型	返回内容	典型场景
INSERT	新插入的完整数据行	获取自增ID、计算字段结果
UPDATE	修改后的最新数据	实时查看变更结果
DELETE	被删除的原始数据	数据归档、操作审计

二、基础用法三步走

2.1 INSERT场景：秒获自增ID

-- 创建带序列的用户表
CREATE TABLE users(
    firstname text, 
    lastname text, 
    id serial primary key
);

-- 插入数据并返回生成的ID
INSERT INTO users(firstname, lastname) 
VALUES('Joe','Cool') 
RETURNING id;

执行效果：返回新记录的id字段，无需二次查询。

2.2 UPDATE场景：实时查看变更结果

-- 批量修改姓氏并返回新值
UPDATE users 
SET firstname='Joe1' 
WHERE firstname='Joe' 
RETURNING firstname, id;

输出样例：

 firstname | id
-----------|----
 Joe1      | 101
 Joe1      | 102

2.3 DELETE场景：一键归档被删数据

-- 删除指定用户并返回完整记录
DELETE FROM users 
WHERE lastname='Cool' 
RETURNING *;

技巧：搭配CREATE TABLE AS可直接将删除数据转存到备份表。

三、高阶技巧：UPDATE如何返回修改前的值？

虽然UPDATE默认只返回新数据，但通过系统字段ctid可巧妙获取旧值：

UPDATE users NEW
SET firstname = OLD.firstname || '_A'
FROM users OLD  -- 创建OLD表别名
WHERE OLD.ctid = NEW.ctid  -- 通过物理地址关联
RETURNING 
    NEW.firstname AS new_name,
    OLD.firstname AS old_name;

实现原理：

ctid字段：系统隐藏列，记录数据行的物理位置（格式：(page_number, tuple_index)）
自连接查询：通过FROM子句创建OLD表别名，与原表NEW进行关联

注意事项：

⚠️ ctid的易变性：VACUUM等操作会改变ctid，不可用于业务逻辑
⚠️ 性能影响：关联查询会使执行计划增加一次全表扫描（见下方执行计划片段）

Update on users new (cost=28.90..60.95 rows=840 width=112)
-> Hash Join (cost=28.90..60.95 rows=840 width=112)
    Hash Cond: (new.ctid = old.ctid)
    -> Seq Scan on users new (cost=0.00..18.40 rows=840 width=42)
    -> Hash (cost=18.40..18.40 rows=840 width=38)
        -> Seq Scan on users old (cost=0.00..18.40 rows=840 width=38)