SQLite RETURNING 子句：从 INSERT、UPDATE、DELETE 取回行

看清刚刚发生了什么

执行 INSERT、UPDATE 或 DELETE 时，SQLite 会告诉你改动了多少行，但不会告诉你是哪些行，也不会告诉你它们最终的值是什么。传统的变通办法是再跟一条 SELECT。这意味着两次往返、两条语句，还有一个小小的竞态窗口：在两条语句之间，别人可能已经改了这一行。

RETURNING 解决了这个问题。把它加在写入语句后面，列出你想取回的列，SQLite 就会把受影响的行交给你，就像你刚刚对它们执行了一次 SELECT：

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO notes (title) VALUES ('First note')
RETURNING id, title, created_at;

一条语句，一次往返，你就拿到了生成的 id，以及数据库替你填上的默认 created_at 值。

RETURNING 是在 SQLite 3.35.0（2021 年 3 月）中加入的。如果你的语句被当作语法错误拒绝，用 SELECT sqlite_version(); 查看一下版本：旧版本不认识这个关键字。

取回生成的 ID

使用 RETURNING 最常见的原因，是在插入之后立刻拿到自动生成的主键：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (email) VALUES ('rosa@example.com')
RETURNING id;

在有 RETURNING 之前，你得先插入，再在同一个连接上调用 last_insert_rowid()（或者驱动提供的等价方法）。这仍然可行，但它依赖连接状态，在连接池或多线程下很容易出错。RETURNING id 是显式的，只作用于这条语句，不管连接由谁托管，行为都一样。

如果你的表没有声明显式的 INTEGER PRIMARY KEY，你仍然可以取回隐式的行标识符：

CREATE TABLE logs (event TEXT);

INSERT INTO logs (event) VALUES ('startup')
RETURNING rowid, event;

每张普通的 SQLite 表都有 rowid，RETURNING 会把它交给你。

多列与表达式

RETURNING 接受的写法和 SELECT 的列列表一样。可以列出列、使用 *、构造表达式，还能起别名：

CREATE TABLE line_items (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    price REAL NOT NULL,
    quantity INTEGER NOT NULL
);

INSERT INTO line_items (product, price, quantity)
VALUES ('Notebook', 4.50, 3)
RETURNING id, product, price * quantity AS subtotal;

当你想要所有内容（包括数据库填上的默认值），又不想逐个写列名时，RETURNING * 很方便：

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (name) VALUES ('login')
RETURNING *;

你会看到新的 id、你传入的 name，以及 SQLite 计算出的时间戳。

在 UPDATE 中使用 RETURNING

在 UPDATE 中，RETURNING 给你的是更新后的值，也就是你的修改生效之后这一行的样子：

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance INTEGER NOT NULL
);
INSERT INTO accounts (name, balance) VALUES ('Ada', 100), ('Boris', 50);

UPDATE accounts
SET balance = balance + 25
WHERE name = 'Ada'
RETURNING id, name, balance;

你拿到的是 Ada 的新余额 125，而不是旧的 100。这让 RETURNING 非常适合原子计数器和充值、扣款操作：你不需要先读取、计算、写入，再重新读取一遍。

如果 WHERE 匹配了多行，每一行受影响的行都会返回一行：

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products (name, price) VALUES ('A', 10), ('B', 20), ('C', 30);

UPDATE products
SET price = price * 1.1
RETURNING id, name, price;

改了三行，就返回三行。返回顺序没有保证：如果你需要特定顺序，请在客户端对结果排序。

在 DELETE 中使用 RETURNING

在 DELETE 中，RETURNING 给你的是删除之前那一刻的行。这对归档、审计记录，或者只是确认删掉了什么都很有用：

CREATE TABLE sessions (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    expires_at TEXT NOT NULL
);
INSERT INTO sessions (user_id, expires_at) VALUES
    (1, '2024-01-01'), (2, '2024-01-02'), (3, '2026-01-01');

DELETE FROM sessions
WHERE expires_at < '2025-01-01'
RETURNING id, user_id, expires_at;

你会取回两条已过期的会话，所有字段都完整保留，尽管它们已经不在表里了。如果你想把它们挪到别处，这正好可以配合一张归档表：在同一个事务中读取结果，再插入到别的地方。

在 UPSERT 中使用 RETURNING

RETURNING 也能和 INSERT ... ON CONFLICT ... DO UPDATE 一起使用。返回的行反映实际执行的是哪个分支：新插入，还是冲突时的更新：

CREATE TABLE counters (
    name TEXT PRIMARY KEY,
    value INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, value) VALUES ('visits', 1)
ON CONFLICT(name) DO UPDATE SET value = value + 1
RETURNING name, value;

把这条语句执行两次。第一次它插入并返回 ('visits', 1)。第二次触发冲突，值被加一，你拿到 ('visits', 2)。无论哪种情况，都是一条语句、返回一行，继续之前不需要再问“到底是插入了还是更新了？”

在 SQLite 中，要实现“给我当前值，没有就创建”而又不想多一次往返，这是最简洁的写法。

几点需要注意

有几个细节经常让人措手不及：

• 对于 INSERT 和 UPDATE，RETURNING 看到的永远是修改之后的行；对于 DELETE，看到的是修改之前的行。没有语法可以要另一边的值。
• 返回行的顺序没有保证。如果顺序重要，请在客户端加上 ORDER BY。
• 不能把 RETURNING 放进子查询里。它是写入语句的顶层子句，而不是表达式。
• RETURNING 不会返回 BEFORE 触发器修改过的数据，它返回的是实际写入的值。AFTER 触发器在写入之后、返回行之前执行。
• 生成列和 DEFAULT 值在结果中都可见。这正是 RETURNING * 能快速查看数据库替你填了什么的原因。

下一步：导入 CSV 数据

当你一次只写一行或几行，并想立刻看到结果时，RETURNING 非常好用。当你要从文件中加载成千上万行时，你会改用 SQLite 的 CSV 导入工具，这就是下一页的内容。