Генерируемые столбцы

1. Введение

Генерируемые столбцы (иногда также называемые «вычисляемыми столбцами») — это столбцы таблицы, значения которых являются функцией от значений других столбцов той же строки. Генерируемые столбцы можно считывать, но их значения нельзя записывать напрямую. Изменить значение генерируемого столбца можно только изменив значения других столбцов, используемых для вычисления этого столбца.

2. Синтаксис

Синтаксически генерируемые столбцы задаются с помощью ограничения столбца "GENERATED ALWAYS". Например:

CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
   e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
);

В приведённом выше операторе есть три обычных столбца: "a" (PRIMARY KEY), "b" и "c", а также два генерируемых столбца "d" и "e".

Ключевые слова "GENERATED ALWAYS" в начале ограничения и ключевое слово "VIRTUAL" или "STORED" в конце являются необязательными. Обязательны только ключевое слово "AS" и выражение в скобках. Если завершающее ключевое слово "VIRTUAL" или "STORED" опущено, по умолчанию используется VIRTUAL. Поэтому приведённый выше пример можно упростить до следующего:

CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT AS (a*abs(b)),
   e TEXT AS (substr(c,b,b+1)) STORED
);

2.1. Столбцы VIRTUAL и STORED

Генерируемые столбцы могут быть VIRTUAL или STORED. Значение столбца VIRTUAL вычисляется при чтении, тогда как значение столбца STORED вычисляется при записи строки. Столбцы STORED занимают место в файле базы данных, тогда как для чтения столбцов VIRTUAL требуется больше циклов процессора.

С точки зрения SQL столбцы STORED и VIRTUAL почти одинаковы. Запросы к обоим типам генерируемых столбцов дают одинаковые результаты. Единственное функциональное отличие состоит в том, что с помощью команды ALTER TABLE ADD COLUMN нельзя добавлять новые столбцы STORED. С помощью ALTER TABLE можно добавлять только столбцы VIRTUAL.

2.2. Возможности

1. Генерируемые столбцы могут иметь тип данных. SQLite пытается преобразовать результат генерирующего выражения к этому типу данных, используя те же правила аффинности, что и для обычных столбцов.

2. Генерируемые столбцы могут иметь ограничения NOT NULL, CHECK и UNIQUE, а также ограничения внешнего ключа, как и обычные столбцы.

3. Генерируемые столбцы могут участвовать в индексах, как и обычные столбцы. Индекс, использующий генерируемые столбцы STORED, — это обычный индекс, а индекс, использующий один или несколько генерируемых столбцов VIRTUAL, является индексом по выражению.

4. Выражение генерируемого столбца может ссылаться на любые другие объявленные столбцы таблицы, включая другие генерируемые столбцы, если выражение прямо или косвенно не ссылается само на себя.

5. Генерируемые столбцы могут располагаться в любом месте определения таблицы. Их можно чередовать с обычными столбцами. Нет необходимости помещать генерируемые столбцы в конец списка столбцов в определении таблицы, как показано в примерах выше.

2.3. Ограничения

1. У генерируемых столбцов не может быть значения по умолчанию (для них нельзя использовать предложение "DEFAULT"). Значение генерируемого столбца всегда определяется выражением, следующим за ключевым словом "AS".

2. Генерируемые столбцы нельзя использовать в составе PRIMARY KEY. (В будущих версиях SQLite это ограничение для столбцов STORED может быть смягчено.)

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

4. Выражение генерируемого столбца может ссылаться на другие генерируемые столбцы той же строки, но ни один генерируемый столбец не может зависеть от самого себя — прямо или косвенно.

5. Выражение генерируемого столбца не может напрямую ссылаться на ROWID, хотя оно может ссылаться на столбец INTEGER PRIMARY KEY, который часто соответствует тому же значению.

6. В каждой таблице должен быть хотя бы один негenerated-столбец.

7. Невозможно добавить столбец STORED с помощью ALTER TABLE ADD COLUMN. Однако можно добавить столбец VIRTUAL.

8. Тип данных и последовательность сортировки генерируемого столбца определяются только типом данных и предложением COLLATE в определении столбца. Тип данных и последовательность сортировки выражения GENERATED ALWAYS AS не влияют на тип данных и последовательность сортировки самого столбца.

9. Генерируемые столбцы не входят в список столбцов, возвращаемый оператором PRAGMA table_info. Однако они включаются в результат нового оператора PRAGMA table_xinfo.

3. Совместимость

Поддержка генерируемых столбцов добавлена в SQLite версии 3.31.0 (2020-01-22). Если более ранняя версия SQLite попытается прочитать файл базы данных, в схеме которого есть генерируемый столбец, эта версия воспримет синтаксис генерируемого столбца как ошибку и сообщит о повреждении схемы базы данных.

Поясним: SQLite версии 3.31.0 может читать и записывать любые базы данных, созданные любой предыдущей версией SQLite вплоть до SQLite 3.0.0 (2004-06-18). Более ранние версии SQLite, выпущенные до 3.31.0, могут читать и записывать базы данных, созданные SQLite версии 3.31.0 и новее, если схема базы данных не содержит функций, таких как генерируемые столбцы, которые не поддерживаются более ранней версией. Проблемы возникают только в том случае, если создать новую базу данных с генерируемыми столбцами с помощью SQLite версии 3.31.0 или новее, а затем попытаться прочитать или записать этот файл базы данных с помощью более ранней версии SQLite, не поддерживающей генерируемые столбцы.

Эта страница в последний раз обновлялась 2026-03-16 20:07:10Z