数据库原理与选型 / 系统课时
生成列与表达式字段
生成列与表达式字段
本课解决“某些字段总能由其他列计算,却被重复写入并可能失去一致性”的问题。完成后,你可以创建虚拟生成列和存储生成列,并理解表达式必须具备确定性的限制。
学习目标
- 创建
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 版本略有差异,但核心原因相同:同一行输入必须稳定地产生同一结果。
按自己的节奏,确认真正理解。