One of the most useful features (besides the JSON data type) in MySQL 8.0 turned out to be generated fields. In the generated field, the value is generated automatically, which makes it possible in some cases to simplify SQL queries, and in some cases to get rid of triggers. For example, you have a table with a list of goods in an invoice, which has fields with price and quantity of goods (quantity) and a field with the amount (total), the value of the total field is calculated in the trigger. The sum field takes up disk space and requires the use of a trigger. With the advent of MySQL 8, the total field can be made generateable. It will not take up space and will always be relevant.
An example of creating a table with a generated field:
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 )
The formula that is specified for the generated fields can contain mathematical operators and MySQL functions. Given the appearance of the JSON data type, the generated fields can be filled with parameter values from the JSON data, which can be used for further search. For example, you have a table that stores transactions from different payment systems. Each system, when receiving a payment, returns its own set of data, which is rarely used, but a search by bank card number is needed:
CREATE TABLE transactions ( `response` JSON, `card` VARCHAR(20) GENERATED ALWAYS AS (response->"$.card_mask") STORED INDEX card_idx (card) )
For a generated field, you must specify NOT NULL after the definition of GENERATED ALWAYS, not before it.
Despite the fact that InnoDB supports indexing virtual generated fields, I would recommend using in such cases storing the calculated field in a table, for this the VIRTUAL keyword is changed to STORED.
Adding a generated field in large tables is very fast, since it does not require a complete re-creation of the table, as is the case with ordinary fields. Therefore, you can safely add such glades to huge tables. An example of adding a generated field:
ALTER TABLE table_name ADD COLUMN `card` DECIMAL(10,2) GENERATED ALWAYS AS (ROUND(price * quantity, 2)) VIRTUAL;
The formula and type of field that is used for generation can be viewed in the INFORMATION table_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
The disadvantage of using generated fields is the inability to quickly create copies of tables. For example a query:
CREATE TABLE `transactions_backup` LIKE `transactions`; INSERT INTO `transactions_backup` SELECT * FROM `transactions`;
Will return the error "The value specified for generated column 'response' in table 'transactions' is not allowed".
For such tables, you have to write a list of fields that are not generated. Helps to make a query to the INFORMATION database_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=''