Сгенерированные (виртуальные и постоянные/хранящиеся) столбцы

Синтаксис

<type>  [GENERATED ALWAYS]  AS   ( <expression> )
[VIRTUAL | PERSISTENT | STORED]  [UNIQUE] [UNIQUE KEY] [COMMENT <text>]

Синтаксис сгенерированных столбцов MariaDB разработан в соответствии с синтаксисом вычисляемых столбцов Microsoft SQL Server и виртуальных столбцов Oracle Database. В MariaDB 10.2 и более поздних версиях синтаксис также совместим с синтаксисом сгенерированных столбцов MySQL.

Описание

Сгенерированный столбец — это столбец в таблице, значение которого нельзя явно задать в запросе DML. Вместо этого его значение автоматически генерируется на основе выражения. Это выражение может генерировать значение на основе значений других столбцов в таблице или вызывать встроенные встроенные функции или пользовательские функции (UDF) .

Существует два типа сгенерированных столбцов:

• PERSISTENT (также известный как STORED) : Значение этого типа фактически хранится в таблице.
• VIRTUAL: Значение этого типа не хранится вообще. Вместо этого значение генерируется динамически при запросе таблицы. Этот тип является значением по умолчанию.

Сгенерированные столбцы также иногда называются вычисляемыми столбцами или виртуальными столбцами.

Поддерживаемые возможности

Поддержка типов хранилищ

• Сгенерированные столбцы могут использоваться только с типами хранилищ, которые их поддерживают. Если вы попытаетесь использовать тип хранилища, который их не поддерживает, вы увидите ошибку, похожую на следующую:

ERROR 1910 (HY000): TokuDB storage engine does not support computed columns

• InnoDB, Aria, MyISAM и CONNECT поддерживают сгенерированные столбцы.

• Столбец в таблице MERGE может быть построен на сгенерированном столбце PERSISTENT.
  • Однако столбец в таблице MERGE не может быть определен как VIRTUAL и PERSISTENT сгенерированный столбец.

Поддержка типов данных

• Все типы данных поддерживаются при определении сгенерированных столбцов.

• Использование параметра столбца ZEROFILL поддерживается при определении сгенерированных столбцов.

• Использование параметра столбца AUTO_INCREMENT не поддерживается при определении сгенерированных столбцов. До MariaDB 10.2.25 он поддерживался, но эта поддержка была удалена, потому что она работала бы неправильно. См. MDEV-11117.

Поддержка индексов

• Использование сгенерированного столбца в качестве первичного ключа таблицы не поддерживается. См. MDEV-5590 для получения дополнительной информации. Если вы попытаетесь использовать его в качестве первичного ключа, вы увидите ошибку, похожую на следующую:

ERROR 1903 (HY000): Primary key cannot be defined upon a computed column

• Использование PERSISTENT сгенерированных столбцов в качестве части внешнего ключа поддерживается.

• Ссылка на PERSISTENT сгенерированные столбцы в качестве части внешнего ключа также поддерживается.
  • Однако использование ON UPDATE CASCADE, ON UPDATE SET NULL, или ON DELETE SET NULL операторов не поддерживается. Если вы попытаетесь использовать недопустимый оператор, вы увидите ошибку, похожую на следующую:

ERROR 1905 (HY000): Cannot define foreign key with ON UPDATE SET NULL clause on a computed column

• Определение индексов как на VIRTUAL, так и на PERSISTENT сгенерированных столбцах поддерживается.
  • Если индекс определен на сгенерированном столбце, оптимизатор рассматривает его так же, как и индексы, основанные на «реальных» столбцах.

Поддержка операторов

• Сгенерированные столбцы используются в запросах DML так же, как и «реальные» столбцы.
  • Однако VIRTUAL и PERSISTENT сгенерированные столбцы отличаются тем, как хранятся их данные.
    • Значения для PERSISTENT сгенерированных столбцов генерируются всякий раз, когда запрос DML вставляет или обновляет строку со специальным значением DEFAULT. Это генерирует значение столбца, и оно хранится в таблице так же, как и другие «реальные» столбцы. Это значение может быть прочитано другими запросами DML так же, как и другие «реальные» столбцы.
    • Значения для VIRTUAL сгенерированных столбцов не хранятся в таблице. Вместо этого значение генерируется динамически всякий раз, когда столбец запрашивается. Если другие столбцы в строке запрашиваются, но VIRTUAL сгенерированный столбец не является одним из запрашиваемых столбцов, то значение столбца не генерируется.

• Оператор SELECT поддерживает сгенерированные столбцы.

• Сгенерированные столбцы могут быть использованы в операторах INSERT, UPDATE и DELETE.
  • Однако VIRTUAL или PERSISTENT сгенерированные столбцы не могут быть явно установлены ни на какие другие значения, кроме NULL или по умолчанию. Если сгенерированный столбец явно устанавливается на любое другое значение, результат зависит от того, включен ли режим строгий режим в sql_mode. Если он не включен, будет выдано предупреждение, и вместо этого будет использовано значение по умолчанию. Если он включен, будет выдана ошибка.

• Оператор CREATE TABLE имеет ограниченную поддержку сгенерированных столбцов.
  • Он поддерживает определение сгенерированных столбцов в новой таблице.
  • Он поддерживает использование сгенерированных столбцов для разбиения таблиц.
  • Он не поддерживает использование операторов версионирования со сгенерированными столбцами.

• Оператор ALTER TABLE имеет ограниченную поддержку сгенерированных столбцов.
  • Он поддерживает операторы MODIFY и CHANGE для PERSISTENT сгенерированных столбцов.
  • Он не поддерживает оператор MODIFY для VIRTUAL сгенерированных столбцов, если ALGORITHM не задан как COPY. См. MDEV-15476 для получения дополнительной информации.
  • Он не поддерживает оператор CHANGE для VIRTUAL сгенерированных столбцов, если ALGORITHM не задан как COPY. См. MDEV-17035 для получения дополнительной информации.
  • Он не поддерживает изменение таблицы, если ALGORITHM не задан как COPY, если в таблице есть VIRTUAL сгенерированный столбец, который индексирован. См. MDEV-14046 для получения дополнительной информации.
  • Он не поддерживает добавление VIRTUAL сгенерированного столбца с оператором ADD, если в том же операторе также добавляются другие столбцы, если ALGORITHM не задан как COPY. См. MDEV-17468 для получения дополнительной информации.
  • Он также не поддерживает изменение существующего столбца в сгенерированный столбец VIRTUAL.
  • Он поддерживает использование сгенерированных столбцов для разбиения таблиц.
  • Он не поддерживает использование операторов версионирования со сгенерированными столбцами.

• Оператор SHOW CREATE TABLE поддерживает сгенерированные столбцы.

• Оператор DESCRIBE может быть использован для проверки наличия сгенерированных столбцов в таблице.
  • Вы можете определить, какие столбцы являются сгенерированными, посмотрев на те, где столбец Extra установлен на VIRTUAL или PERSISTENT. Например:

DESCRIBE table1;
+-------+-------------+------+-----+---------+------------+
| Field | Type        | Null | Key | Default | Extra      |
+-------+-------------+------+-----+---------+------------+
| a     | int(11)     | NO   |     | NULL    |            |
| b     | varchar(32) | YES  |     | NULL    |            |
| c     | int(11)     | YES  |     | NULL    | VIRTUAL    |
| d     | varchar(5)  | YES  |     | NULL    | PERSISTENT |
+-------+-------------+------+-----+---------+------------+

• Сгенерированные столбцы могут быть правильно использованы в строках NEW и OLD в триггерах.

• Процедуры хранения поддерживают сгенерированные столбцы.

• Оператор HANDLER поддерживает сгенерированные столбцы.

Поддержка выражений

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

• Большинство встроенных функций поддерживаются в выражениях для сгенерированных столбцов.
  • Однако некоторые встроенные функции не могут быть поддержаны по техническим причинам. Например, если вы попытаетесь использовать недопустимую функцию в выражении, будет сгенерирована ошибка, похожая на следующую:

ERROR 1901 (HY000): Function or expression 'dayname()' cannot be used in the GENERATED ALWAYS AS clause of `v`

• Подзапросы не поддерживаются в выражениях для сгенерированных столбцов, потому что данные в них могут измениться.

• Использование чего-либо, зависящего от данных вне строки, не поддерживается в выражениях для сгенерированных столбцов.

• Функции хранения не поддерживаются в выражениях для сгенерированных столбцов. См. MDEV-17587 для получения дополнительной информации.

• Недетерминированные встроенные функции поддерживаются в выражениях для неиндексированных VIRTUAL сгенерированных столбцов.

• Недетерминированные встроенные функции не поддерживаются в выражениях для PERSISTENT или индексированных VIRTUAL сгенерированных столбцов.

• Пользовательские функции (UDFs) поддерживаются в выражениях для сгенерированных столбцов.
  • Однако MariaDB не может проверить, является ли UDF детерминированной, поэтому пользователю необходимо убедиться, что они не используют недетерминированные UDF с VIRTUAL сгенерированными столбцами.

• Определение сгенерированного столбца на основе других сгенерированных столбцов, определенных перед ним в определении таблицы, поддерживается. Например:

CREATE TABLE t1 (a int as (1), b int as (a));

• Однако определение сгенерированного столбца на основе других сгенерированных столбцов, определенных после него в определении таблицы, не поддерживается в выражениях для сгенерированных столбцов, потому что сгенерированные столбцы рассчитываются в порядке их определения.

• Использование выражения, длина которого превышает 255 символов, поддерживается в выражениях для сгенерированных столбцов. Новый предел для всего определения таблицы, включая все выражения для сгенерированных столбцов, составляет 65 535 байт.

• Использование константных выражений поддерживается в выражениях для сгенерированных столбцов. Например:

CREATE TABLE t1 (a int as (1));

Согласование сохраненных значений

Когда сгенерированный столбец PERSISTENT или индексирован, значение выражения должно быть согласованным независимо от флагов режима SQL в текущей сессии. Если это не так, то таблица будет считаться поврежденной, когда значение, которое фактически должно возвращаться вычисляемым выражением, и значение, которое было ранее сохранены и/или индексированы с использованием другого параметра sql_mode, не совпадают.

В настоящее время существует два класса несогласованностей: выравнивание символов и вычитание без знака:

• Для VARCHAR или TEXT сгенерированного столбца длина возвращаемого значения может изменяться в зависимости от флага sql_mode PAD_CHAR_TO_FULL_LENGTH. Чтобы сделать значение согласованным, создайте сгенерированный столбец, используя функцию RTRIM() или RPAD(). В качестве альтернативы, создайте сгенерированный столбец как CHAR столбец, чтобы данные всегда были полностью выровнены.

• Если сгенерированный столбец SIGNED основан на вычитании UNSIGNED значения, результирующее значение может изменяться в зависимости от величины значения и флага sql_mode NO_UNSIGNED_SUBTRACTION. Чтобы сделать значение согласованным, используйте CAST(), чтобы убедиться, что каждый UNSIGNED операнд SIGNED перед вычитанием.

Вот пример двух таблиц, которые будут отклонены в MariaDB 10.5 и о которых будут выдаваться предупреждения в других перечисленных версиях:

CREATE TABLE bad_pad (
  txt CHAR(5),
  -- CHAR -> VARCHAR or CHAR -> TEXT can't be persistent or indexed:
  vtxt VARCHAR(5) AS (txt) PERSISTENT
);

CREATE TABLE bad_sub (
  num1 BIGINT UNSIGNED,
  num2 BIGINT UNSIGNED,
  -- The resulting value can vary for some large values
  vnum BIGINT AS (num1 - num2) VIRTUAL,
  KEY(vnum)
);

Предупреждения для вышеуказанных таблиц выглядят следующим образом:

Warning (Code 1901): Function or expression '`txt`' cannot be used in the GENERATED ALWAYS AS clause of `vtxt`
Warning (Code 1105): Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH

Warning (Code 1901): Function or expression '`num1` - `num2`' cannot be used in the GENERATED ALWAYS AS clause of `vnum`
Warning (Code 1105): Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION

Чтобы обойти эту проблему, принудительно задайте выравнивание или тип, чтобы выражение сгенерированного столбца возвращало согласованное значение. Например:

CREATE TABLE good_pad (
  txt CHAR(5),
  -- Using RTRIM() or RPAD() makes the value consistent:
  vtxt VARCHAR(5) AS (RTRIM(txt)) PERSISTENT,
  -- When not persistent or indexed, it is OK for the value to vary by mode:
  vtxt2 VARCHAR(5) AS (txt) VIRTUAL,
  -- CHAR -> CHAR is always OK:
  txt2 CHAR(5) AS (txt) PERSISTENT
);

CREATE TABLE good_sub (
  num1 BIGINT UNSIGNED,
  num2 BIGINT UNSIGNED,
  -- The indexed value will always be consistent in this expression:
  vnum BIGINT AS (CAST(num1 AS SIGNED) - CAST(num2 AS SIGNED)) VIRTUAL,
  KEY(vnum)
);

Поддержка совместимости MySQL

• Ключевое слово STORED поддерживается в качестве псевдонима для ключевого слова PERSISTENT.

• Таблицы, созданные с MySQL 5.7 или более поздними версиями, которые содержат сгенерированные столбцы MySQL, могут быть импортированы в MariaDB без создания дампов и восстановления.

Отличия в реализации

Сгенерированные столбцы подвержены различным ограничениям в других СУБД, которых нет в реализации MariaDB. Сгенерированные столбцы также могут называться вычисляемыми столбцами или виртуальными столбцами в разных реализациях. Подробные сведения о конкретной реализации можно найти в документации для каждой конкретной СУБД.

Отличия в реализации по сравнению с Microsoft SQL Server

Реализация сгенерированных столбцов в MariaDB не налагает следующих ограничений, которые присутствуют в реализации вычисляемых столбцов Microsoft SQL Server:

• MariaDB разрешает использование переменных сервера в выражениях сгенерированных столбцов, включая те, которые динамически изменяются, такие как warning_count.
• MariaDB разрешает вызов функции CONVERT_TZ() с указанным именем часового пояса в качестве аргумента, даже если имена часовых поясов и смещения времени являются настраиваемыми.
• MariaDB разрешает использование функции CAST() с не-unicode наборами символов, даже если наборы символов настраиваются и отличаются между бинарными версиями/версиями.
• MariaDB разрешает использование выражений FLOAT в сгенерированных столбцах. Microsoft SQL Server считает эти выражения "неточными" из-за потенциальных различий в реализации и точности с плавающей запятой на разных платформах.
• Microsoft SQL Server требует установки режима ARITHABORT, чтобы деление на ноль возвращало ошибку, а не NULL.
• Microsoft SQL Server требует установки QUOTED_IDENTIFIER в sql_mode. В MariaDB, если данные вставляются без ANSI_QUOTES в sql_mode, они будут обрабатываться и храниться по-разному в сгенерированном столбце, содержащем цитируемые идентификаторы.

Microsoft SQL Server налагает вышеуказанные ограничения, выполняя одно из следующих действий:

• Отказ от создания вычисляемых столбцов.
• Отказ от разрешения обновлений таблицы, содержащей их.
• Отказ от использования индекса над таким столбцом, если нельзя гарантировать, что выражение полностью детерминировано.

В MariaDB, пока sql_mode, язык и другие настройки, которые действовали во время CREATE TABLE, остаются неизменными, выражение сгенерированного столбца всегда будет оцениваться одинаково. Если что-либо из этого изменяется, то следует учитывать, что выражение сгенерированного столбца может быть оценено не так, как это было ранее.

При попытке обновить виртуальный столбец, вы получите ошибку, если по умолчанию включен режим строгого режима в sql_mode, или предупреждение в противном случае.

История разработки

Сгенерированные столбцы были первоначально разработаны Андреем Жаковым. Затем они были изменены Санджа Бьелкин и Игорем Бабаевым в Monty Program для включения в MariaDB. Monty выполнил работу над MariaDB 10.2, чтобы устранить некоторые старые ограничения.

Примеры

Вот пример таблицы, которая использует как VIRTUAL , так и PERSISTENT виртуальные столбцы:

USE TEST;

CREATE TABLE table1 (
     a INT NOT NULL,
     b VARCHAR(32),
     c INT AS (a mod 10) VIRTUAL,
     d VARCHAR(5) AS (left(b,5)) PERSISTENT);

Если вы опишите таблицу, вы легко сможете увидеть, какие столбцы являются виртуальными, взглянув на столбец "Extra":

DESCRIBE table1;
+-------+-------------+------+-----+---------+------------+
| Field | Type        | Null | Key | Default | Extra      |
+-------+-------------+------+-----+---------+------------+
| a     | int(11)     | NO   |     | NULL    |            |
| b     | varchar(32) | YES  |     | NULL    |            |
| c     | int(11)     | YES  |     | NULL    | VIRTUAL    |
| d     | varchar(5)  | YES  |     | NULL    | PERSISTENT |
+-------+-------------+------+-----+---------+------------+

Чтобы узнать, какая функция(и) генерирует значение виртуального столбца, вы можете использовать SHOW CREATE TABLE:

SHOW CREATE TABLE table1;

| table1 | CREATE TABLE `table1` (
  `a` int(11) NOT NULL,
  `b` varchar(32) DEFAULT NULL,
  `c` int(11) AS (a mod 10) VIRTUAL,
  `d` varchar(5) AS (left(b,5)) PERSISTENT
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

Если вы попытаетесь вставить значения, отличные от значений по умолчанию, в виртуальный столбец, вы получите предупреждение, и то, что вы попытались вставить, будет проигнорировано, и вместо него будет вставлено выведенное значение:

WARNINGS;
Show warnings enabled.

INSERT INTO table1 VALUES (1, 'some text',default,default);
Query OK, 1 row affected (0.00 sec)

INSERT INTO table1 VALUES (2, 'more text',5,default);
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1645): The value specified for computed column 'c' in table 'table1' has been ignored.

INSERT INTO table1 VALUES (123, 'even more text',default,'something');
Query OK, 1 row affected, 2 warnings (0.00 sec)

Warning (Code 1645): The value specified for computed column 'd' in table 'table1' has been ignored.
Warning (Code 1265): Data truncated for column 'd' at row 1

SELECT * FROM table1;
+-----+----------------+------+-------+
| a   | b              | c    | d     |
+-----+----------------+------+-------+
|   1 | some text      |    1 | some  |
|   2 | more text      |    2 | more  |
| 123 | even more text |    3 | even  |
+-----+----------------+------+-------+
3 rows in set (0.00 sec)

Если указан ZEROFILL запрос, он должен быть помещен непосредственно после определения типа, перед AS (<expression>):

CREATE TABLE table2 (a INT, b INT ZEROFILL AS (a*2) VIRTUAL);
INSERT INTO table2 (a) VALUES (1);

SELECT * FROM table2;
+------+------------+
| a    | b          |
+------+------------+
|    1 | 0000000002 |
+------+------------+
1 row in set (0.00 sec)

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

См. также

• Использование виртуальных столбцов на практике на блоге mariadb.com.