SQLite 的生成列（Generated Columns）如何使用？

SQLite 的生成列（Generated Columns）提供了一种自动计算列值的方式：

1. 生成列的概念

   • 生成列的值由表达式自动计算得出
   • 不能直接插入或更新生成列的值
   • 分为 STORED（存储）和 VIRTUAL（虚拟）两种类型

2. 生成列类型

   • STORED：计算结果存储在磁盘上，占用空间但查询快
   • VIRTUAL：每次查询时计算，不占用空间但查询慢

3. 创建生成列

   sql
   -- STORED 生成列
   CREATE TABLE products (
       id INTEGER PRIMARY KEY,
       name TEXT,
       price REAL,
       quantity INTEGER,
       total REAL GENERATED ALWAYS AS (price * quantity) STORED
   );
   
   -- VIRTUAL 生成列
   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       first_name TEXT,
       last_name TEXT,
       full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL
   );

4. 生成列的约束

   • 表达式必须确定，不能包含随机函数
   • 不能引用其他生成列
   • 不能引用同一表的其他行
   • 不能包含子查询
   • 不能使用非确定性函数（如 random()）

5. 使用生成列的优势

   • 数据一致性：自动保持数据同步
   • 减少冗余：避免手动维护计算字段
   • 性能优化：STORED 列可以建立索引
   • 代码简化：减少应用层计算逻辑

6. 为生成列创建索引

   sql
   CREATE TABLE orders (
       id INTEGER PRIMARY KEY,
       order_date TEXT,
       status TEXT,
       year INTEGER GENERATED ALWAYS AS (CAST(strftime('%Y', order_date) AS INTEGER)) STORED
   );
   
   -- 为生成列创建索引
   CREATE INDEX idx_year ON orders(year);

7. 修改生成列 SQLite 不支持直接修改生成列，需要：

   sql
   -- 创建新表
   CREATE TABLE products_new (
       id INTEGER PRIMARY KEY,
       name TEXT,
       price REAL,
       quantity INTEGER,
       total REAL GENERATED ALWAYS AS (price * quantity * 1.1) STORED
   );
   
   -- 迁移数据
   INSERT INTO products_new (id, name, price, quantity)
   SELECT id, name, price, quantity FROM products;
   
   -- 删除旧表，重命名新表
   DROP TABLE products;
   ALTER TABLE products_new RENAME TO products;

8. 生成列的使用场景

   • 计算字段（总价、平均值等）
   • 数据转换（日期格式转换、类型转换）
   • 数据提取（从 JSON 中提取字段）
   • 数据验证（检查条件）
   • 派生数据（基于其他列计算）

9. 性能考虑

   • STORED 列：占用存储空间，查询快，写入慢
   • VIRTUAL 列：不占用存储空间，查询慢，写入快
   • 根据查询频率选择合适的类型
   • 为频繁查询的 STORED 列创建索引

10. 与其他特性结合

    • 与 JSON 扩展结合：提取 JSON 字段
    • 与全文搜索结合：生成搜索文本
    • 与约束结合：验证数据完整性

生成列是 SQLite 提供自动化数据维护的强大工具。