Генеровані поля MySQL 8.0

Однією з найбільш корисних функцій (крім типу даних JSON) в MySQL 8.0 виявилися генеровані поля. У генерованому полі значення формується автоматично, що дає можливість у деяких випадках спростити SQL-запити, а в деяких — позбутися тригерів. Наприклад, у вас є таблиця з переліком товарів у рахунку, в якій є поля з ціною (price) і кількістю товару (quantity) та поле із сумою (total), значення якого обчислюється в тригері. Поле із сумою займає місце на диску і вимагає використання тригера. З появою MySQL 8.0 поле 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=''