生成列

D1 允许您根据一个或多个其他列、SQL 函数，甚至提取的 JSON 值定义生成列。

这使您可以在写入或读取表时规范化数据，使查询更容易，并减少对复杂应用逻辑的需求。

生成列还可以在其上定义索引，这可以大幅提升频繁查询字段的查询性能。

生成列有两种类型：

• VIRTUAL（默认）：读取时生成列。好处是不消耗存储，但可能增加计算时间（从而降低查询性能），尤其是较大的查询。
• STORED：写入行时生成列。列占用与常规列相同的存储空间，但读取时无需每次生成列，这可以提升读取查询性能。

在生成列表达式中省略时，生成列默认为 VIRTUAL 类型。当生成列计算密集时，建议使用 STORED 类型。例如，解析大型 JSON 结构时。

生成列可以在 CREATE TABLE 语句中创建表时定义，或在之后通过 ALTER TABLE 语句定义。

要创建定义生成列的表，使用 AS 关键字：

CREATE TABLE some_table (
    -- other columns omitted
    some_generated_column AS <function_that_generates_the_column_data>
)

作为一个具体示例，要自动从以下 JSON 传感器数据中提取 location 值，您可以定义一个名为 location（类型 TEXT）的生成列，基于存储 JSON 数据原始表示的 raw_data 列。

{
    "measurement": {
        "temp_f": "77.4",
        "aqi": [21, 42, 58],
        "o3": [18, 500],
        "wind_mph": "13",
        "location": "US-NY"
    }
}

要定义值为 $.measurement.location 的生成列，可以使用 json_extract 函数在每次写入该行时从 raw_data 列提取值：

CREATE TABLE sensor_readings (
    event_id INTEGER PRIMARY KEY,
    timestamp INTEGER NOT NULL,
    raw_data TEXT,
    location as (json_extract(raw_data, '$.measurement.location')) STORED
);

生成列可选地使用 column_name GENERATED ALWAYS AS <function> [STORED|VIRTUAL] 语法指定。GENERATED ALWAYS 语法是可选的，省略时不会改变生成列的行为。

生成列也可以添加到现有表。如果 sensor_readings 表没有生成列 location，可以通过运行 ALTER TABLE 语句添加：

ALTER TABLE sensor_readings
ADD COLUMN location as (json_extract(raw_data, '$.measurement.location'));

这定义了一个 VIRTUAL 生成列，在每次读取查询时运行 json_extract。

生成列定义无法直接修改。要更改生成列的生成方式，可以使用 ALTER TABLE table_name REMOVE COLUMN，然后 ADD COLUMN 重新定义生成列，或使用 ALTER TABLE table_name RENAME COLUMN current_name TO new_name 重命名现有列，再使用新定义调用 ADD COLUMN。

生成列不仅限于 json_extract 等 JSON 函数：您可以使用几乎任何可用函数来定义生成列的生成方式。

例如，您可以根据 sensor_reading 表中之前的 timestamp 列生成 date 列，在数据库内自动将 Unix 时间戳转换为 YYYY-MM-dd 格式：

ALTER TABLE your_table
-- date(timestamp, 'unixepoch') converts a Unix timestamp to a YYYY-MM-dd formatted date
ADD COLUMN formatted_date AS (date(timestamp, 'unixepoch'))

或者，您可以定义一个 expires_at 列来计算未来日期，并在查询中按该日期过滤：

-- Filter out "expired" results based on your generated column:
-- SELECT * FROM your_table WHERE current_date() > expires_at
ALTER TABLE your_table
-- calculates a date (YYYY-MM-dd) 30 days from the timestamp.
ADD COLUMN expires_at AS (date(timestamp, '+30 days'));

• 表必须至少有一个非生成列。您不能定义仅包含生成列的表。
• 表达式只能引用同一表和行中的其他列，且只能使用确定性函数 ↗。不能使用 random()、子查询或聚合函数来定义生成列。
• 通过 ALTER TABLE ... ADD COLUMN 添加到现有表的列必须是 VIRTUAL。您无法向现有表添加 STORED 列。