如何为 Sqlite 数据库的表添加 generated 列

在现代数据库设计中，有时我们需要一些列的值能够根据其他列自动计算得出，而不是手动维护。Sqlite 从 3.31.0 版本开始引入了 generated 列（生成列）功能，它允许我们定义这种自动计算的列，既保证了数据一致性，又减少了应用层的计算负担。本文将详细介绍如何在 Sqlite 中使用 generated 列，包括其类型、语法和实际应用场景。

什么是 generated 列

generated 列是一种特殊的列，它的值由表中其他列的值通过表达式计算得出。Sqlite 支持两种类型的 generated 列：

STORED：计算后的值会实际存储在数据库中
VIRTUAL：值在查询时实时计算，不占用存储空间

这种设计让我们可以在数据库层面维护派生数据，而无需在应用代码中手动处理。例如，我们可以有一个 full_name 列自动由 first_name 和 last_name 组合而成，或者一个 total_price 列自动计算为 quantity 乘以 unit_price。

创建表时定义 generated 列

在创建新表时，可以通过 GENERATED ALWAYS AS 子句定义 generated 列。基本语法如下：

CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    ...
    生成列名 数据类型 GENERATED ALWAYS AS (表达式) [STORED|VIRTUAL]
);

例如，我们创建一个 products 表，其中包含一个自动计算的 total_price 列：

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    quantity INTEGER,
    unit_price REAL,
    total_price REAL GENERATED ALWAYS AS (quantity * unit_price) STORED
);

在这个例子中，total_price 会自动计算为 quantity 和 unit_price 的乘积，并且这个值会实际存储在数据库中。

使用 VIRTUAL 与 STORED 的区别

选择 VIRTUAL 还是 STORED 主要取决于使用场景：

VIRTUAL 列不占用存储空间，但在每次查询时需要重新计算
STORED 列会占用存储空间，但查询时直接读取，性能更好

示例对比：

-- VIRTUAL 示例
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL
);

-- STORED 示例
CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    subtotal REAL,
    tax_rate REAL,
    tax_amount REAL GENERATED ALWAYS AS (subtotal * tax_rate) STORED,
    total REAL GENERATED ALWAYS AS (subtotal + tax_amount) STORED
);

对于 full_name 这种轻量级计算且可能频繁更新的列，使用 VIRTUAL 更合适。而对于 tax_amount 和 total 这种需要频繁查询的计算结果，使用 STORED 可以提高查询性能。

为已有表添加 generated 列

Sqlite 不支持直接通过 ALTER TABLE 添加 generated 列。和添加外键类似，需要通过以下步骤实现：

创建包含新 generated 列的新表
将旧表数据导入新表
删除旧表
重命名新表

示例：假设我们要为现有的 orders 表添加一个 discounted_total 列：

-- 原始表结构
-- CREATE TABLE orders (id INTEGER PRIMARY KEY, total REAL, discount REAL);

-- 创建新表
CREATE TABLE orders_new (
    id INTEGER PRIMARY KEY,
    total REAL,
    discount REAL,
    discounted_total REAL GENERATED ALWAYS AS (total * (1 - discount)) STORED
);

-- 复制数据
INSERT INTO orders_new (id, total, discount) SELECT id, total, discount FROM orders;

-- 删除旧表
DROP TABLE orders;

-- 重命名新表
ALTER TABLE orders_new RENAME TO orders;

generated 列的使用限制

虽然 generated 列很实用，但有一些需要注意的限制：

generated 列不能直接插入或更新值，只能通过定义的计算表达式得出
表达式只能引用同一表中的其他列，不能引用其他表或子查询
表达式不能包含聚合函数或窗口函数
不能将 PRIMARY KEY 或 UNIQUE 约束应用于 generated 列

尝试直接修改 generated 列会导致错误：

-- 这会失败
INSERT INTO products (id, name, quantity, unit_price, total_price)
VALUES (1, 'Widget', 10, 2.5, 25.0);

-- 正确做法是省略 generated 列
INSERT INTO products (id, name, quantity, unit_price)
VALUES (1, 'Widget', 10, 2.5);

generated 列的实用场景

generated 列特别适合以下场景：

数据格式化：自动组合或格式化其他列的值

CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    country_code TEXT,
    phone_number TEXT,
    full_phone TEXT GENERATED ALWAYS AS ('+' || country_code || ' ' || phone_number) VIRTUAL
);

计算字段：自动维护计算结果的字段

CREATE TABLE rectangles (
    id INTEGER PRIMARY KEY,
    width REAL,
    height REAL,
    area REAL GENERATED ALWAYS AS (width * height) STORED,
    perimeter REAL GENERATED ALWAYS AS (2 * (width + height)) STORED
);

业务逻辑：封装固定的业务计算规则

CREATE TABLE payroll (
    id INTEGER PRIMARY KEY,
    base_salary REAL,
    bonus REAL,
    tax_rate REAL,
    net_salary REAL GENERATED ALWAYS AS (base_salary + bonus - (base_salary + bonus) * tax_rate) STORED
);

总结

Sqlite 的 generated 列功能为数据管理提供了更多灵活性，允许我们将计算逻辑下移到数据库层面。通过合理使用 VIRTUAL 和 STORED 类型，我们可以在存储空间和计算性能之间取得平衡。在实际应用中，generated 列特别适合那些需要自动维护的派生数据场景，如格式化显示、固定计算规则等。

记住几个关键点：

根据使用频率和计算复杂度选择 VIRTUAL 或 STORED
不能直接插入或更新 generated 列的值
表达式只能引用同一表的其他列
添加 generated 列到现有表需要重建表

合理使用 generated 列可以让数据库设计更加简洁，减少应用层代码，同时保证数据的一致性和准确性。