Сгенерированные столбцы

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» (первичный ключ), «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. ВИРТУАЛЬНЫЕ и ХРАНИМЫЕ столбцы

Сгенерированные столбцы могут быть либо ВИРТУАЛЬНЫМИ, либо ХРАНИМЫМИ. Значение ВИРТУАЛЬНОГО столбца вычисляется при чтении, тогда как значение ХРАНИМОГО столбца вычисляется при записи строки. ХРАНИМЫЕ столбцы занимают место в файле базы данных, а ВИРТУАЛЬНЫЕ столбцы используют больше циклов ЦП при чтении.

С точки зрения SQL, ХРАНИМЫЕ и ВИРТУАЛЬНЫЕ столбцы почти идентичны. Запросы к любому классу сгенерированного столбца дают одинаковые результаты. Единственное функциональное различие заключается в том, что нельзя добавлять новые ХРАНИМЫЕ столбцы с помощью команды ALTER TABLE ADD COLUMN. Только ВИРТУАЛЬНЫЕ столбцы могут быть добавлены с помощью ALTER TABLE.

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

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

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

3. Сгенерированные столбцы могут участвовать в индексах, как и обычные столбцы.

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

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

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

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

2. Сгенерированные столбцы не могут использоваться в качестве части первичного ключа. (В будущих версиях SQLite это ограничение может быть ослаблено для ХРАНИМЫХ столбцов.)

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

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

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

6. Каждая таблица должна иметь хотя бы один не-сгенерированный столбец.

7. Невозможно ALTER TABLE ADD COLUMN ХРАНИМЫЙ столбец. Однако можно добавить ВИРТУАЛЬНЫЙ столбец.

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, которая не понимает сгенерированные столбцы.

Эта страница была в последний раз изменена 2022-11-09 20:11:26 UTC