Исполняемые доки SQLite: Generated колонки

Q: В чём разница между VIRTUAL и STORED?

VIRTUAL пересчитывается при каждом чтении и не занимает места на диске - это режим по умолчанию. STORED вычисляется один раз при записи и физически хранится в файле БД: чтение дешевле, запись - чуть дороже. Индексировать можно оба варианта, но STORED обычно выгоднее, когда выражение тяжёлое или колонку читают сильно чаще, чем пишут.

Q: Можно ли построить индекс по generated-колонке?

Да. CREATE INDEX спокойно работает и с VIRTUAL, и с STORED. Собственно, ради этого их чаще всего и заводят: индекс по производному значению (например, lower(email) или поле из JSON, вытащенное через ->>) даст планировщику возможность использовать его автоматически - переписывать запросы не придётся.

Q: Можно ли добавить generated-колонку через ALTER TABLE?

Можно, но только VIRTUAL. Команда ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUAL отрабатывает штатно. А вот добавить STORED-колонку через ALTER TABLE нельзя - придётся пересоздавать таблицу. Если планируется stored, продумывайте схему заранее.

Вычисляемая колонка - это колонка, значение которой считается формулой

Вычисляемая колонка (generated column) в SQLite - это колонка, значение которой берётся из выражения, а не из 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 при каждом чтении. Поменяете любую из этих колонок - total подтянется автоматически.

Ключевое слово GENERATED ALWAYS AS обязательно. ALWAYS - это формальность из стандарта SQL, в SQLite другого варианта всё равно нет.

VIRTUAL и STORED: в чём разница

У вычисляемой колонки в SQLite есть два режима. По умолчанию используется 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. И почти всегда это именно то, что нужно по умолчанию.

Зачем это всё? Индекс по вычисляемой колонке

Главная фишка - на generated column можно навесить индекс. Это даёт быстрый поиск по производным значениям, причём без переписывания всех запросов.

Допустим, нужен поиск по email без учёта регистра:

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. Поддержка JSON в SQLite даёт оператор ->> для извлечения скалярного значения; заверните его в generated column - и получите типизированное индексируемое поле поверх гибкого блоба.

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 навязывает несколько правил - лучше знать о них заранее, чем нарваться на ошибку:

Выражение должно быть детерминированным. random(), datetime('now') и прочие недетерминированные функции под запретом. Значение обязано однозначно вычисляться из самой строки.
В выражении можно ссылаться только на колонки той же строки. Никаких подзапросов, агрегатов или обращений к другим таблицам.
Записать значение в вычисляемую колонку напрямую через 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 самому её посчитать.

Что выбрать - 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. На запись это бесплатно, и для большинства задач этого достаточно.
Переключайтесь на STORED, когда строите индекс по колонке в таблице с активной записью (индексу всё равно нужно физически хранить значение) или когда выражение действительно дорогое.
Не зацикливайтесь на выборе. Тип хранения - часть схемы, но колонку всегда можно удалить и пересоздать, если передумаете - по крайней мере для VIRTUAL.

Вычисляемые колонки против представлений (VIEW)

С представлениями есть пересечение: и те и другие отдают вычисленные значения, не храня их (ну, иногда). Разделение обычно такое:

Вычисляемая колонка принадлежит одной строке одной таблицы. Используйте её для построчных производных значений - форматирование email, извлечение поля из JSON, подсчёт суммы.
Представление (view) - это сохранённый запрос. Берите его, когда вычисление включает джойны, агрегацию или фильтрацию по нескольким строкам.

Их можно комбинировать. View может делать SELECT из таблицы с вычисляемыми колонками и подтягивать дополнительный контекст джойнами. Вычисляемые колонки живут на уровне хранения, представления - на уровне запросов.

Дальше: ATTACH DATABASE

Вычисляемые колонки позволяют таблице считать свои собственные значения. На следующей странице двинемся в обратную сторону: подключим несколько баз SQLite одновременно через ATTACH DATABASE, чтобы один запрос мог работать сразу с несколькими файлами.

Часто задаваемые вопросы

Что такое generated-колонка в SQLite?

Это колонка, значение которой вычисляется по выражению на основе других колонок той же строки. Объявляется в CREATE TABLE через GENERATED ALWAYS AS (выражение). Напрямую в неё писать нельзя - SQLite сам пересчитает значение при чтении или записи строки.

В чём разница между VIRTUAL и STORED?

VIRTUAL пересчитывается при каждом чтении и не занимает места на диске - это режим по умолчанию. STORED вычисляется один раз при записи и физически хранится в файле БД: чтение дешевле, запись - чуть дороже. Индексировать можно оба варианта, но STORED обычно выгоднее, когда выражение тяжёлое или колонку читают сильно чаще, чем пишут.

Можно ли построить индекс по generated-колонке?

Да. CREATE INDEX спокойно работает и с VIRTUAL, и с STORED. Собственно, ради этого их чаще всего и заводят: индекс по производному значению (например, lower(email) или поле из JSON, вытащенное через ->>) даст планировщику возможность использовать его автоматически - переписывать запросы не придётся.

Можно ли добавить generated-колонку через ALTER TABLE?

Можно, но только VIRTUAL. Команда ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUAL отрабатывает штатно. А вот добавить STORED-колонку через ALTER TABLE нельзя - придётся пересоздавать таблицу. Если планируется stored, продумывайте схему заранее.

Связанные концепции

Generated-колонки в SQLite: VIRTUAL и STORED