Мы используем cookie-файлы
Для оптимизации работы нашего сайта мы используем cookie-файлы. Продолжая использовать сайт, Вы соглашаетесь с использованием cookie-файлов.
Панель управления
  • Русский
  • Українська
  • UAH
  • USD
  • RUB
  • EUR
  • +38(044) 392-74-33  Киев
  • +38(057) 728-39-00  Харьков
  • +38(056) 794-38-31  Днепр
  • +38(032) 229-58-93  Львов
  • +38(048) 738-57-70  Одесса
  • +38(093) 170-15-42  Life
  • +38(067) 400-88-44  Киевстар
  • +38(095) 630-90-82  Vodafone
  • +1(888)393-24-51  USA, Toll free
  • +44(131)507-01-14  Great Britain
  • +7(499) 348-28-61  Москва

Генерируемые поля MySQL 8.0

Одной из самых полезных функций (кроме типа данных JSON) в MySQL 8.0 оказались генерируемые поля. В генерируемом поле значение формируется автоматически это дает возможность в некоторых случаях упростить SQL запросы, а в некоторых - избавиться от триггеров. Например у вас есть таблица с перечнем товаров в счете, в которой есть поля с ценой (price) и количеством товара (quantity) и поле с суммой (total), значение поля total вычисляется в триггере. Поле с суммой занимает место на диске и требует использования триггера. С появлением MySQL 8 поле total можно сделать генерируемым. Оно не будет занимать место и всегда будет актуальным.

Пример создания таблицы с генерируемым полем:

CREATE TABLE invoice_item (
    `price` decimal(10,2) unsigned not null default 0,
    `quantity` decimal(10,4) unsigned not null default 0,
    `total` decimal(10,2) unsigned generated always as (round(quantity * price, 2)) VIRTUAL
)

Формула, которая указывается для генерируемых полей может содержать математические операторы и функции MySQL. С учетом появления типа данных JSON, в генерируемые поля можно выносить значения параметров из данных JSON, по которым в дальнейшем производить поиск. Например у вас есть таблица, в которой хранятся транзакции от разных платежных систем. Каждая система при получении платежа возвращает свой набор данных, который редко используется, но нужен поиск по номеру банковской карты:

 
CREATE TABLE transactions (
    `response` JSON,
    `card` VARCHAR(20) GENERATED ALWAYS AS (response->"$.card_mask") STORED
    INDEX card_idx (card)
)

Для генерируемого поля указывать NOT NULL нужно после определения GENERATED ALWAYS, а не перед ним.

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

Добавление генерируемого поля в больших таблицах происходит очень быстро, так как не требует полного пересоздания таблицы, как это происходит с обычными полями. Поэтому смело можете добавлять такие поляна огромных таблицах. Пример добавления генерируемого поля:

ALTER TABLE table_name
ADD COLUMN `card` DECIMAL(10,2) GENERATED ALWAYS AS (ROUND(price * quantity, 2)) VIRTUAL;

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

SELECT column_name, generation_expression, extra
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name='$table_name' and
    table_schema='$db_name'
ORDER BY ordinal_position

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

CREATE TABLE `transactions_backup` LIKE `transactions`;

INSERT INTO `transactions_backup`
SELECT * FROM `transactions`;

Вернет ошибку «The value specified for generated column 'response' in table 'transactions' is not allowed».

Для таких тблиц приходится прописывать список полей, которые не являются генерируемыми. Помогает это сделать запрос к базе INFORMATION_SCHEMA:

SELECT GROUP_CONCAT('`', column_name, '`' ORDER BY ordinal_position SEPARATOR ', ' )
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name='$table_name' and
    table_schema='$db_name' and
    generation_expression=''

 

Кликай и подписывайся!


Только зарегистрированные пользователи могут оставлять комментарии

473

530

398

Подпишитесь на рассылку

Будем присылать анонсы новых статей и полезные советы раз в неделю

Присоединяйтесь к нам в соцсетях