SQLite 生成列该怎么用，VIRTUAL 和 STORED 如何取舍？

SQLite 生成列适合把“总是由同一行其他字段推出来”的值写进表结构，比如订单总价、拼接后的姓名、从 JSON 里提取出来的状态。它不是为了少写一行查询，而是为了让计算规则固定在数据库层，避免应用服务、后台脚本、数据导出工具各算一遍还算出不同结果。使用前先问两个问题：这个值是否只依赖本行字段？这个值是否会被频繁查询、过滤或排序？如果两个答案都是“是”，生成列通常值得考虑；如果答案里出现当前时间、其他表、权限上下文或复杂业务状态，就不要勉强塞进去。

sql
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  price NUMERIC NOT NULL,
  quantity INTEGER NOT NULL,
  total NUMERIC GENERATED ALWAYS AS (price * quantity) STORED
);
CREATE INDEX idx_orders_total ON orders(total);

还有一个容易被忽略的细节：生成列适合承载稳定规则，不适合承载临时补丁。上线前最好用几条边界数据验证表达式，比如空字符串、NULL、负数、异常 JSON 路径。这样可以提前发现类型转换和约束冲突，避免迁移后才发现历史数据无法写入新表。

追问

VIRTUAL 和 STORED 到底怎么选？

VIRTUAL 不把结果落盘，每次读取时重新计算，所以表文件更小，写入路径也更轻。STORED 会在写入或更新依赖列时计算并保存结果，查询时像普通列一样读取，更适合高频过滤、排序和索引。取舍点在读写比例：订单报表经常按 total 查区间，STORED 更稳；资料页偶尔展示 first_name || ' ' || last_name，VIRTUAL 就够。踩坑是 STORED 不是“可手动修正的缓存列”，它仍然不能直接 INSERT 或 UPDATE，算错了只能改源字段、改表达式或做表迁移。另一个边界是磁盘空间，移动端本地库如果有大量派生文本，STORED 可能会让数据库膨胀得很快。

生成列表达式有哪些限制？

生成列表达式必须是确定性的，同样输入应该得到同样输出，所以 random()、datetime('now') 这类函数不适合放进去。它不能包含子查询、聚合函数，也不能跨行引用数据，因为生成列描述的是“本行如何派生出另一个字段”。有些版本和资料对引用其他生成列的描述容易让人误会，工程里最好让生成列直接引用基础列，迁移和排查都更简单。边界判断可以很朴素：如果这个值要看别的表、统计多行、依赖当前时间，改用视图、触发器、普通列加应用逻辑会更安全。踩坑是把复杂业务规则藏进 schema 后，代码评审只看应用层很难发现真正的计算来源。

生成列能不能优化 JSON 查询？

能，而且这是 SQLite 里很实用的用法。直接在 WHERE json_extract(data,'$.status') = 'paid' 上过滤，数据量上来后通常会反复解析 JSON；把稳定字段提成生成列，再建索引，查询计划更清楚。

sql
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL CHECK (json_valid(data)),
  status TEXT GENERATED ALWAYS AS (json_extract(data, '$.status')) STORED
);
CREATE INDEX idx_events_status ON events(status);

取舍是灵活性会下降，JSON 路径一改，生成列表达式、索引和历史数据迁移都要跟着处理。适合提取状态、用户 ID、事件类型这类少数稳定字段，不适合把一整份 JSON 拆成几十个生成列。实际项目里还要注意类型，JSON 里数字和字符串混用时，索引命中和比较结果可能让人意外。

生成列可以后期随便修改吗？

不能把它当普通字段那样随手改表达式。SQLite 的 ALTER TABLE 能力有限，很多生成列变更需要新建表、复制基础列、重建索引和约束，再替换旧表。这个过程最好放在事务里，并在升级前备份；移动端或桌面端应用尤其要考虑升级中断后如何恢复。踩坑是复制数据时把生成列也写进 INSERT 列表，SQLite 会拒绝，因为生成列的值应该由表达式自动计算。边界是大表迁移会锁库并消耗时间，如果只是临时查询需求，视图或表达式索引可能比改表更轻。

sql
BEGIN;
CREATE TABLE orders_new (
  id INTEGER PRIMARY KEY,
  price NUMERIC NOT NULL,
  quantity INTEGER NOT NULL,
  total NUMERIC GENERATED ALWAYS AS (price * quantity * 1.1) STORED
);
INSERT INTO orders_new(id, price, quantity)
SELECT id, price, quantity FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

项目里什么时候不该用生成列？

如果业务规则经常变，生成列会把变化推进数据库迁移流程，成本可能比应用层计算更高。涉及汇率、权限、库存快照、用户时区这类上下文不同结果不同的值，也不适合做生成列，因为同一行在不同场景下可能不该只有一个答案。另一个坑是为了“看起来规范”把所有派生字段都做成生成列，结果表结构越来越厚，新增需求都要改 schema。比较稳的边界是：规则稳定、只依赖本行、经常查询，并且团队愿意把它当长期维护的 schema。这样用生成列，它会减少重复逻辑；反过来用，它会变成隐藏业务规则的地方。