実行できるSQLiteドキュメント: 生成カラム

Q: SQLiteの生成カラム（generated column）とは何ですか？

生成カラムは、同じ行の他のカラムから式で値を計算するカラムです。CREATE TABLEの中でGENERATED ALWAYS AS (式)と書いて宣言します。直接INSERTやUPDATEで値を入れることはできず、行を読み書きするタイミングでSQLiteが自動で計算してくれます。

Q: VIRTUALとSTOREDはどう使い分ければいいですか？

VIRTUALは読むたびに毎回計算する方式で、ディスク容量は使いません（こちらがデフォルト）。STOREDは書き込み時に一度だけ計算してファイルに保存するので、読み込みは速い代わりに書き込みコストが少し増えます。どちらもインデックスを張れますが、計算式が重い場合や、書き込みより読み込みが圧倒的に多い場合はSTOREDが向いています。

Q: 生成カラムにインデックスは張れますか？

張れます。VIRTUALでもSTOREDでもCREATE INDEXが使えます。むしろこれが生成カラムを使う一番の理由で、lower(email)や->>で抽出したJSONフィールドのような派生値にインデックスを張っておけば、クエリ側を書き換えなくてもプランナがそのインデックスを使ってくれます。

Q: ALTER TABLEで生成カラムを後から追加できますか？

VIRTUALなら追加できます。ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUALは普通に動きます。ただしSTOREDの生成カラムはALTER TABLEでは追加できません。どうしても必要な場合はテーブルを作り直すしかないので、STOREDを使うつもりなら最初の設計段階で入れておくのが無難です。

生成カラム＝式から値が決まるカラム

SQLite の生成カラム（generated column）は、INSERT で値を渡すのではなく、式から自動的に値が決まるカラムです。CREATE TABLE のときに計算式を一度書いておけば、あとは SQLite が面倒を見てくれます。書き込みは一切できず、値を入れようとするとエラーになります。

いちばんシンプルな例がこちらです。

CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    price   REAL NOT NULL,
    tax     REAL NOT NULL,
    total   REAL GENERATED ALWAYS AS (price + tax)
);

INSERT INTO products (price, tax) VALUES (100.0, 8.5);
INSERT INTO products (price, tax) VALUES (49.99, 4.25);

SELECT id, price, tax, total FROM products;

total には一度も値を入れていないのに、すべての行で値が表示されています。SQLite は行を読み出すたびに price + tax から再計算しているのです。price か tax のどちらかを更新すれば、total もそれに追従します。

GENERATED ALWAYS AS というキーワードは省略できません。ALWAYS の部分は SQL 標準に沿った形式上の記述で、SQLite には他の選択肢はありません。

VIRTUAL と STORED の違い

sqlite の生成カラムには 2 種類のタイプがあり、どちらか一方を必ず指定します。デフォルトは VIRTUAL です。

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- SELECT のたびに再計算される
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- 一度計算してディスクに保存される
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

INSERT INTO orders (qty, unit) VALUES (3, 19.99);
SELECT * FROM orders;

モデルとしてのイメージはこんな感じです。

VIRTUAL — ディスクは一切消費しないが、読み取りのたびにCPUを使う。追加もコストゼロ、後から変更するのも気軽。
STORED — ディスク容量を食う代わりに、読み取り時の追加コストはゼロ。式が重い場合や、書き込みよりも読み取りがはるかに多いカラムで効いてくる。

キーワードを省略すると VIRTUAL になります。ほとんどのケースでこれがちょうどいいデフォルトです。

なぜ使うのか? インデックスを張れる派生カラム

最大の魅力は、生成カラムにもインデックスを張れることです。クエリを書き直さなくても、派生値 に対する高速な検索ができるようになります。

たとえば、メールアドレスを大文字小文字を区別せずに検索したいとしましょう。

CREATE TABLE users (
    id           INTEGER PRIMARY KEY,
    email        TEXT NOT NULL,
    email_lower  TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL
);

CREATE INDEX users_email_lower ON users(email_lower);

INSERT INTO users (email) VALUES ('Ada@Example.com'), ('boris@example.com');

SELECT id, email FROM users WHERE email_lower = 'ada@example.com';

このインデックスは小文字化した値に対して張られています。email_lower でフィルタするクエリは、このインデックスをそのまま使えます。SQLite には式インデックス(CREATE INDEX ... ON users(lower(email)))もありますが、生成カラムを使えば、導出した値を実体のあるカラムとして扱えるのが利点です。SELECT で取得したり、ビューから参照したり、アプリ側のコードから再利用したりできます。

JSON から値を取り出す

sqlite 生成カラムが本領を発揮するのは、JSON と組み合わせたときです。SQLite の JSON サポートには、スカラー値を取り出す ->> 演算子が用意されています。これを生成カラムでラップしてやれば、柔軟な BLOB の上に型付きでインデックスも張れるフィールドを用意できます。

CREATE TABLE events (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL,
    user_id  INTEGER GENERATED ALWAYS AS (payload ->> '$.user_id') VIRTUAL,
    kind     TEXT    GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
);

CREATE INDEX events_user_id ON events(user_id);

INSERT INTO events (payload) VALUES
    ('{"user_id": 1, "kind": "login"}'),
    ('{"user_id": 2, "kind": "purchase"}'),
    ('{"user_id": 1, "kind": "logout"}');

SELECT id, kind FROM events WHERE user_id = 1;

user_id と kind は、クエリから見れば普通のカラムのように扱えますが、実体は payload の中に格納されています。JSON を書き換えれば、これらのカラムも自動で追従します。user_id にインデックスを張っておけば、検索も高速です。

ルールと制約

SQLite にはいくつかの制約があります。あとでハマらないように、先に押さえておきましょう。

式は決定的(deterministic) である必要があります。random() や datetime('now') のような非決定的な関数は使えません。同じ行からは必ず同じ値が得られないとダメ、ということです。
式から参照できるのは同じ行のカラムだけです。サブクエリも集約関数も他テーブルもNG。
生成カラムに直接 INSERT や UPDATE はできません。INSERT INTO products (total) VALUES (5) はエラーになります。
STORED カラムは ALTER TABLE ... ADD COLUMN で追加できません。後から追加できるのは VIRTUAL カラムだけです。
生成カラムにも NOT NULL、CHECK、UNIQUE、さらには FOREIGN KEY といった制約を付けられます。この点では普通のカラムとまったく同じように振る舞います。

書き込みルールの動きをサクッと確認してみましょう。

sqlite> INSERT INTO products (price, tax, total) VALUES (10, 1, 999);
Runtime error: cannot INSERT into generated column "total"

解決策はシンプルで、INSERT の対象カラムから生成カラムを外し、SQLite に計算を任せればOKです。

VIRTUAL と STORED、どちらを選ぶか

VIRTUAL と STORED の使い分けは、基本的に「読み書きの比率」と「式の計算コスト」で決まります。

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- 軽い算術なので、読み取りごとに再計算しても問題ない。
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- 重めの式で、頻繁に参照されるが書き込みは少ない → 保存する。
    bucket      TEXT GENERATED ALWAYS AS (
        CASE
            WHEN celsius < 0  THEN 'freezing'
            WHEN celsius < 15 THEN 'cool'
            WHEN celsius < 25 THEN 'mild'
            ELSE 'hot'
        END
    ) STORED
);

INSERT INTO measurements (celsius) VALUES (-3), (10), (22), (31);
SELECT celsius, fahrenheit, bucket FROM measurements;

使い分けの目安:

基本は VIRTUAL でOK。書き込み時のコストはゼロで、たいていの用途で十分です。
書き込みが多いテーブルでそのカラムにインデックスを張るときは STORED に切り替えましょう（インデックスを作る以上、値は実体化される必要があるので）。式の計算が本当に重いケースも同様です。
そこまで悩まなくて大丈夫。種類はスキーマの一部ですが、VIRTUAL ならカラムを削除して作り直せば変更できます。

生成カラムとビューの違い

ビューと役割が重なる部分があります。どちらも計算結果を（実体として持たずに）見せられるからです。とはいえ、住み分けはだいたい次のとおり:

生成カラムは1つのテーブルの1行に紐づくもの。メールアドレスの整形、JSONフィールドの取り出し、合計値の算出など、行ごとの派生値に使います。
ビューは保存されたクエリ。JOINや集計、複数行をまたぐフィルタリングが絡む計算に向いています。

組み合わせも可能です。生成カラムを持つテーブルから SELECT するビューを作って、追加の情報をJOINで足す、といった使い方ができます。生成カラムはストレージ層、ビューはクエリ層、と覚えておくと整理しやすいでしょう。

次回: ATTACH DATABASE

生成カラムは、ひとつのテーブル内で値を計算するための仕組みでした。次のページではその逆、ATTACH DATABASE で複数のSQLiteデータベースを同時につないで、ひとつのクエリでファイルをまたいで扱う方法を見ていきます。

よくある質問

SQLiteの生成カラム（generated column）とは何ですか？

生成カラムは、同じ行の他のカラムから式で値を計算するカラムです。CREATE TABLEの中でGENERATED ALWAYS AS (式)と書いて宣言します。直接INSERTやUPDATEで値を入れることはできず、行を読み書きするタイミングでSQLiteが自動で計算してくれます。

VIRTUALとSTOREDはどう使い分ければいいですか？

VIRTUALは読むたびに毎回計算する方式で、ディスク容量は使いません（こちらがデフォルト）。STOREDは書き込み時に一度だけ計算してファイルに保存するので、読み込みは速い代わりに書き込みコストが少し増えます。どちらもインデックスを張れますが、計算式が重い場合や、書き込みより読み込みが圧倒的に多い場合はSTOREDが向いています。

生成カラムにインデックスは張れますか？

張れます。VIRTUALでもSTOREDでもCREATE INDEXが使えます。むしろこれが生成カラムを使う一番の理由で、lower(email)や->>で抽出したJSONフィールドのような派生値にインデックスを張っておけば、クエリ側を書き換えなくてもプランナがそのインデックスを使ってくれます。

ALTER TABLEで生成カラムを後から追加できますか？

VIRTUALなら追加できます。ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUALは普通に動きます。ただしSTOREDの生成カラムはALTER TABLEでは追加できません。どうしても必要な場合はテーブルを作り直すしかないので、STOREDを使うつもりなら最初の設計段階で入れておくのが無難です。