PostgreSQL / 系统课时
使用生成列
使用生成列
本课解决派生字段由应用重复计算、容易不一致的问题。你将使用生成列根据同一行字段自动计算金额,验证插入和更新后的结果,并掌握表达式与写入限制。
学习目标
- 使用
GENERATED ALWAYS AS定义生成列 - 验证生成值随依赖列自动变化
- 识别生成表达式的主要限制
生成列适合什么场景
生成列的值由数据库根据表达式计算,应用不需要在每次写入时重复赋值。它适合金额小计、规范化文本、组合标识等可由同一行字段确定的派生数据。
本课使用 STORED 生成列,计算结果会随行数据一起存储。读取速度稳定,但写入时需要重新计算,并占用额外存储空间。
创建并验证生成列
先运行 psql -d <DATABASE>,再执行以下完整脚本。
sql
DROP TABLE IF EXISTS order_item;
CREATE TABLE order_item (
id integer PRIMARY KEY,
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10, 2) NOT NULL CHECK (unit_price >= 0),
line_total numeric(12, 2)
GENERATED ALWAYS AS (quantity * unit_price) STORED
);
INSERT INTO order_item (id, quantity, unit_price)
VALUES (1, 3, 13.00);
SELECT id, quantity, unit_price, line_total
FROM order_item;
UPDATE order_item
SET quantity = 4
WHERE id = 1;
SELECT id, quantity, unit_price, line_total
FROM order_item;预期结果
text
首次查询:
id | quantity | unit_price | line_total
----+----------+------------+------------
1 | 3 | 13.00 | 39.00
更新数量后:
id | quantity | unit_price | line_total
----+----------+------------+------------
1 | 4 | 13.00 | 52.00应用只修改了 quantity,数据库自动重新计算 line_total,避免两个字段出现不一致。
写入与表达式限制
插入或更新时不能给生成列指定普通业务值;它的结果只能由表达式产生。生成表达式通常只能引用当前行的普通列,不能引用其他表、子查询或另一个生成列。
表达式中使用的函数必须满足数据库对生成列的稳定性要求。涉及当前时间、随机数或外部表状态的值,不适合放入生成列,应在查询、触发器或业务流程中处理。
生成列与普通列的取舍
生成列适合计算规则稳定、需要强一致并且读取频繁的值。规则经常变化、计算成本很高或依赖跨表数据时,普通列加业务更新逻辑可能更灵活。
修改生成表达式会影响表结构和已有数据处理,应在生产环境评估锁等待、重写成本和回滚方案。
按自己的节奏,确认真正理解。