SQLite 生成列：VIRTUAL 与 STORED 详解及示例

生成列就是计算列

生成列的值来自一个表达式，而不是来自 INSERT。你在 CREATE TABLE 中声明一次公式，剩下的交给 SQLite。你永远不会写入它：试图写入会报错。

最简短的例子：

CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    price   REAL NOT NULL,
    tax     REAL NOT NULL,
    total   REAL GENERATED ALWAYS AS (price + tax)
);

INSERT INTO products (price, tax) VALUES (100.0, 8.5);
INSERT INTO products (price, tax) VALUES (49.99, 4.25);

SELECT id, price, tax, total FROM products;

total 从未被插入，却出现在每一行中。每次读取这一行时，SQLite 都会根据 price + tax 重新计算它。更新其中任何一列，total 都会跟着变。

关键字短语 GENERATED ALWAYS AS 是必须写的。其中的 ALWAYS 只是 SQL 标准的形式要求：在 SQLite 中没有别的选项。

VIRTUAL 与 STORED

每个生成列都属于两种类型之一。默认是 VIRTUAL：

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- recomputed on every SELECT
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- computed once and saved to disk
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

INSERT INTO orders (qty, unit) VALUES (3, 19.99);
SELECT * FROM orders;

可以这样理解：

• VIRTUAL：磁盘上占零字节，每次读取都消耗 CPU。添加成本低，以后修改也容易。
• STORED：占用磁盘空间，读取时没有额外开销。当表达式开销大，或者这一列的读取远多于写入时，它更划算。

如果不写关键字，得到的就是 VIRTUAL。这几乎总是正确的默认选择。

为什么要用？可以建索引的派生值

最强大的特性是：你可以在生成列上建立索引。这样就能对“派生”值进行快速查找，而不必改写每一条查询。

假设你想按邮箱查找且不区分大小写：

CREATE TABLE users (
    id           INTEGER PRIMARY KEY,
    email        TEXT NOT NULL,
    email_lower  TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL
);

CREATE INDEX users_email_lower ON users(email_lower);

INSERT INTO users (email) VALUES ('Ada@Example.com'), ('boris@example.com');

SELECT id, email FROM users WHERE email_lower = 'ada@example.com';

索引覆盖的是小写形式。按 email_lower 过滤的查询会直接使用这个索引。SQLite 也支持表达式索引（CREATE INDEX ... ON users(lower(email))），但生成列把派生值变成了一个真实的列，你可以 SELECT 它，在视图中引用它，也可以在应用代码中复用它。

从 JSON 中提取值

生成列和 JSON 搭配使用时尤其出色。SQLite 的 JSON 支持提供了 ->> 来提取标量值；把它包进一个生成列，你就在灵活的数据块之上得到了一个有类型、可索引的字段。

CREATE TABLE events (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL,
    user_id  INTEGER GENERATED ALWAYS AS (payload ->> '$.user_id') VIRTUAL,
    kind     TEXT    GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
);

CREATE INDEX events_user_id ON events(user_id);

INSERT INTO events (payload) VALUES
    ('{"user_id": 1, "kind": "login"}'),
    ('{"user_id": 2, "kind": "purchase"}'),
    ('{"user_id": 1, "kind": "logout"}');

SELECT id, kind FROM events WHERE user_id = 1;

在你的查询看来，user_id 和 kind 就像普通列一样，但数据实际存放在 payload 中。修改 JSON，这些列也会随之更新。user_id 上的索引让查找变得很快。

规则与限制

SQLite 会强制执行以下几条规则，最好在碰到之前就了解：

• 表达式必须是确定性的。random()、datetime('now') 等非确定性函数都不允许使用。值必须能从这一行重新算出来。
• 表达式只能引用同一行中的列。不能用子查询，不能用聚合，也不能引用其他表。
• 你不能直接对生成列执行 INSERT 或 UPDATE。INSERT INTO products (total) VALUES (5) 会报错。
• 不能用 ALTER TABLE ... ADD COLUMN 添加 STORED 列。事后只能添加 VIRTUAL 列。
• 生成列可以带有 NOT NULL、CHECK、UNIQUE，甚至 FOREIGN KEY 约束。在这些方面，它们和其他列没有区别。

快速演示一下写入规则：

sqlite> INSERT INTO products (price, tax, total) VALUES (10, 1, 999);
Runtime error: cannot INSERT into generated column "total"

解决办法是把生成列从 INSERT 的列列表中去掉，让 SQLite 自己计算。

选择 VIRTUAL 还是 STORED

这个决定通常取决于读写比例和表达式的开销：

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- Cheap arithmetic, fine to recompute every read.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- Heavier expression, queried often, written rarely → store it.
    bucket      TEXT GENERATED ALWAYS AS (
        CASE
            WHEN celsius < 0  THEN 'freezing'
            WHEN celsius < 15 THEN 'cool'
            WHEN celsius < 25 THEN 'mild'
            ELSE 'hot'
        END
    ) STORED
);

INSERT INTO measurements (celsius) VALUES (-3), (10), (22), (31);
SELECT celsius, fahrenheit, bucket FROM measurements;

经验法则：

• 默认使用 VIRTUAL。它在写入时没有开销，几乎适用于所有场景。
• 当你在写入频繁的表上给这一列建立索引（索引本来就需要持久化这个值），或者表达式确实开销很大时，改用 STORED。
• 不必为此纠结。类型是表结构的一部分，但如果你改变主意，可以删除列再重新创建，至少 VIRTUAL 列可以这样做。

生成列与视图

生成列和视图有重叠之处：两者都能在不存储的情况下暴露计算出的值（嗯，有时候是这样）。通常的分工是：

• 生成列属于某一行、某一张表。用它做逐行的派生：格式化邮箱、提取 JSON 字段、计算总额。
• 视图是保存下来的查询。当计算涉及连接、聚合或跨行过滤时使用它。

两者可以混用。视图可以从带有生成列的表中 SELECT，并连接其他表补充上下文。生成列位于存储层，视图位于查询层。

下一篇：ATTACH DATABASE

生成列让一张表自己计算出值。下一页则反过来：用 ATTACH DATABASE 同时连接多个 SQLite 数据库，让一条查询可以跨越多个文件。