数据库原理与选型 / 系统课时

生成列与表达式字段

生成列与表达式字段

本课解决“某些字段总能由其他列计算,却被重复写入并可能失去一致性”的问题。完成后,你可以创建虚拟生成列和存储生成列,并理解表达式必须具备确定性的限制。

学习目标

  • 创建 VIRTUAL 与 STORED 生成列
  • 使用表达式自动维护派生数据
  • 识别生成列表达式的限制和索引边界

创建生成列

下面的订单表通过单价和数量自动计算总金额。VIRTUAL 在读取时计算,STORED 在写入时计算并保存结果。

sql
DROP TABLE IF EXISTS order_items;

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    unit_price INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    subtotal INTEGER GENERATED ALWAYS AS (
        unit_price * quantity
    ) VIRTUAL,
    price_level TEXT GENERATED ALWAYS AS (
        CASE
            WHEN unit_price >= 10000 THEN '高价'
            ELSE '常规'
        END
    ) STORED
);

INSERT INTO order_items (id, unit_price, quantity) VALUES
(1, 12000, 2),
(2, 8000, 3);

SELECT id, unit_price, quantity, subtotal, price_level
FROM order_items
ORDER BY id;

预期输出

text
id  unit_price  quantity  subtotal  price_level
--  ----------  --------  --------  -----------
1   12000       2         24000     高价
2   8000        3         24000     常规

插入时不需要也不能直接给生成列赋值。基础列变化后,生成列会按定义自动得到新值,避免应用层忘记同步更新。

对比 VIRTUAL 与 STORED

类型 计算时机 占用表存储 适用场景
VIRTUAL 查询时 通常不额外保存结果 计算简单、读取频率一般
STORED 写入时 保存计算结果 读取频繁、计算较重

两者都可以被查询和建立索引,但 STORED 会增加写入成本和数据库文件体积。选择时应根据读写比例判断,而不是默认全部使用存储生成列。

更新基础列并验证

生成列由基础列驱动。下面只修改数量,subtotal 会自动变化。

sql
UPDATE order_items
SET quantity = 4
WHERE id = 2;

SELECT id, quantity, subtotal
FROM order_items
WHERE id = 2;

预期输出

text
id  quantity  subtotal
--  --------  --------
2   4         32000

生成列适合表达稳定的行内计算。如果结果依赖其他表、当前时间或随机值,就不应使用生成列。

理解表达式限制

生成列表达式只能引用同一行中的列,并应使用确定性标量函数。它不能包含子查询、聚合函数、窗口函数,也不能直接依赖 random() 等非确定性结果。

sql
CREATE TABLE invalid_example (
    value INTEGER,
    random_value INTEGER GENERATED ALWAYS AS (
        random()
    ) VIRTUAL
);

预期输出

text
Error: non-deterministic functions prohibited in generated columns

错误文字可能因 SQLite 版本略有差异,但核心原因相同:同一行输入必须稳定地产生同一结果。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

生成列与表达式字段

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通