生成列 · Cloudflare D1 ドキュメント

D1 では、1 つ以上のほかの列の値、SQL 関数、あるいは抽出した JSON 値をもとに、生成列を定義できます。

書き込み時または読み取り時にデータを正規化できるため、クエリが簡単になり、複雑なアプリケーションロジックが不要になります。

生成列にはインデックスも付けられます。よく問い合わせるフィールドでは、クエリ性能が大きく上がることがあります。

生成列の種類

生成列には次の 2 種類があります。

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 の値を自動抽出するには、JSON の生データを格納する raw_data 列をもとに、location（型は TEXT）という生成列を定義できます。

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

$.measurement.location の値を持つ生成列を定義するには、その行への書き込みのたびに raw_data 列から値を取り出す json_extract 関数を使います。

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'));

これは、読み取りクエリのたびに json_extract を実行する VIRTUAL 生成列です。

生成列の定義は直接変更できません。生成方法を変えるには、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'));

その他の注意点

テーブルには、生成列以外の列が少なくとも 1 つ必要です。生成列だけからなるテーブルは定義できません。
式が参照できるのは、同じテーブル・同じ行のほかの列だけです。使える関数は決定性関数 ↗ に限ります。random()、サブクエリ、集約関数は、生成列の定義に使えません。
ALTER TABLE ... ADD COLUMN で既存テーブルに追加する列は VIRTUAL である必要があります。既存テーブルに STORED 列は追加できません。