Генеруються поля 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=''