MySQL 5.7 new feature: Generated Column (function index)

MySQL 5.7 new feature: Generated Column (function index)

MySQL 5.7 introduces Generated Column. This article briefly introduces the usage and precautions of Generated Column, and provides a quick and complete tutorial for readers to understand MySQL 5.7. This article focuses on the following issues:

What is Generated Column?
Differences between Virtual Column and Stored Column
What if I do some damage to the Generated Column?
Create an index on the Generated Column
Differences between creating an index on a Generated Column and an Oracle Function Index

What is Generated Column?

Generated Column is a new feature introduced by MySQL 5.7. The so-called Cenerated Column is calculated by other columns in the database. We will explain it using examples in the official reference manual.

For example, to know the two sides of a right triangle, the length of the Oblique Edge is required. Obviously, the length of the Oblique Edge can be calculated using two straight corner edges. At this time, only the straight corner edges can be stored in the database. The oblique edges use Generated Column, as shown below:

Create table triangle (
Sidea DOUBLE,
Sideb DOUBLE,
Sidec double as (SQRT (sidea * sidea + sideb * sideb )));

Insert into triangle (sidea, sideb) VALUES (1, 1), (3, 4), (6, 8 );

Query results:

Mysql> SELECT * FROM triangle;
+ ------- + -------------------- +
| Sidea | sideb | sidec |
+ ------- + -------------------- +
| 1 | 1 | 1.4142135623730951 |
| 3 | 4 | 5 |
| 6 | 8 | 10 |
+ ------- + -------------------- +

This example shows what Generated Columns is and how to use it.

Differences between Virtual Generated Column and Stored Generated Column

MySQL 5.7 supports two types of Generated columns: Virtual Generated Column and Stored Generated Column. The former only saves the Generated Column in the data dictionary (Table metadata ), this Column of data will not be persisted to the disk; the latter will persist the Generated Column to the disk, instead of calculating the result each time the Column is read. Obviously, the latter stores data that can be computed using existing data and requires more disk space, which has no advantage over Virtual Column. Therefore, in MySQL 5.7, the Generated Column type is not specified. The default value is Virtual Column. In addition:
Stored Generated Column has poor performance.
If Stored Generated Golumn is required, it is more appropriate to create an index on the Generated Column. For details, see section 4th.

In summary, generally, Virtual Generated Column is used, which is the default MySQL method. If Stored Generated Column is used, the preceding table creation statement will be as follows, A stored keyword is added:

Create Table: create table 'trigger '(
'Sidea 'double default null,
'Inclub' double default null,
'Sidec 'double generated always as (SQRT (sidea * sidea + sideb * sideb) STORED)
 

What if I do some damage to the generated column?

We already know what the generated column is and how to use the generated column. To avoid misuse, we will first conduct some experiments to avoid some unknown situations during specific use.

Define generated column as "divided by 0"

If we define generated column as "x column/0", MySQL does not directly report an ERROR, but reports an ERROR when inserting data, and prompts "ERROR 1365 (22012): Division by 0"

Mysql> create table t (x int, y int, z int generated always as (x/0 ));
Query OK, 0 rows affected (0.22 sec)

Mysql> insert into t (x, y) values (1, 1 );
ERROR 1365 (22012): Division by 0

Insert malicious data
If we define generated column as "column x/column y" and insert data, if column y is 0, an error is returned, as shown below:

Mysql> create table t (x int, y int, z int generated always as (x/y ));
Query OK, 0 rows affected (0.20 sec)

Mysql> insert into t (x, y) values (1, 0 );
ERROR 1365 (22012): Division by 0
 

Delete source column

If we define generated column as "column x/column y" and try to delete column x or column y, ERROR 3108 (HY000) will be prompted ): column 'X' has a generated column dependency."

Mysql> create table t (x int, y int, z int generated always as (x/y ));
Query OK, 0 rows affected (0.24 sec)

Mysql> alter table t drop column x;
ERROR 3108 (HY000): Column 'X' has a generated column dependency.
 

The specified Generated Column is obviously invalid.

If we define generated column as "column x + column y", it is obvious that column x or column y is Numeric. If we define (or modify) column x or column y) if it is of the stable type (of course, it should not be silly to do so in actual use), an error is expected. However, it is not, as shown below, we can create it normally.

Mysql> create table t (x int, y varchar (100), z int generated always as (x + y ));
Query OK, 0 rows affected (0.13 sec)

Insert the following data without errors:

Mysql> insert into t (x, y) values (1, '0 ');
Query OK, 1 row affected (0.01 sec)

Mysql> select * from t;
+ ------ +
| X | y | z |
+ ------ +
| 1 | 0 | 1 |
+ ------ +
1 row in set (0.00 sec)

However, if MySQL cannot be processed, an error is returned:

Mysql> insert into t (x, y) values (1, 'x ');
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'X'

Create an index on the Generated Column

Similarly, we can create an index on the generated column to accelerate the search speed after the index is created, as shown below:

Mysql> create table t (x int primary key, y int, z int generated always as (x/y), unique key idz (z ));
Query OK, 0 rows affected (0.11 sec)

Mysql> show create table t \ G
* *************************** 1. row ***************************
Table: t
Create Table: create table 'T '(
'X' int (11) not null,
'Y' int (11) default null,
'Z' int (11) generated always as (x/y) VIRTUAL,
Primary key ('x '),
Unique key 'idz' ('Z') ENGINE = InnoDB default charset = latin1
1 row in set (0.01 sec)

In addition, we can create a common index and a unique index. If it is a unique index, an error is returned when the uniqueness constraint is violated:

Mysql> insert into t (x, y) values (1, 1 );
Query OK, 1 row affected (0.02 sec)

Mysql> insert into t (x, y) values (2, 2 );
ERROR 1062 (23000): Duplicate entry '1' for key 'idz'

Therefore, when using MySQL5.7, you still need to understand the Generated Column to solve some problems that have not been encountered before.
 
Index restrictions
Although Virtal Generated Column should be used in general, there are still many restrictions on using Virtual Generated Column, including:

Clustered index cannot contain virtual generated column

Mysql> create table t1 (a int, B int, c int generated always as (a/B), primary key (c ));
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.

Mysql> create table t1 (a int, B int, c int generated always as (a/B) STORED, primary key (c ));
Query OK, 0 rows affected (0.11 sec)

Full-text indexes and spatial indexes cannot be created on Virtual Generated columns. This is expected to be solved in later MySQL versions. (How can I remember that Stored Column can be listed ?).

Virtual Generated Column cannot be used as a foreign key

When creating a generated column (including virtual generated column and stored generated column), you cannot use non-deterministic (non-repeated) functions.

Mysql> alter table 't1' ADD p3 date generated always as (curtime () virtual;
ERROR 3102 (HY000): Expression of generated column 'p3 'contains a disallowed function.

Mysql> alter table 't1' ADD p3 date generated always as (curtime () stored;
ERROR 3102 (HY000): Expression of generated column 'p3 'contains a disallowed function.

 

Differences between creating an index on a Generated Column and an Oracle Function Index
After introducing the index of MySQL on the Generated Column, those familiar with Oracle may think of the Oracle function index. The index on the Generated Column of MySQL is similar to the function index of Oracle, there are also differences:

For example, a table is shown as follows:

Mysql> create table t1 (first_name VARCHAR (10), last_name VARCHAR (10 ));
Query OK, 0 rows affected (0.11 sec)

Suppose we need to create a full_name index at this time. in Oracle, we can directly use the function when creating the index, as shown below:

Alter table t1 add index full_name_idx (CONCAT (first_name, '', last_name ));

However, the preceding statement returns an error in MySQL. In MySQL, we can create a new Generated Column, and then create an index on this Generated Column, as shown below:

Mysql> alter table t1 add column full_name VARCHAR (255) generated always as (CONCAT (first_name, '', last_name ));

Mysql> alter table t1 add index full_name_idx (full_name );

At first glance, MySQL needs to add a column to the table to implement function indexes similar to Oracle, which seems to be much more costly. However, we have said in part 1 that for Virtual Generated Column, MySQL only saves the metadata of this Column in the data dictionary and does not persistently store this Column of data to the disk, therefore, creating an index on the MySQL Virtual Generated Column is similar to that of the Oracle function index, and does not require more costs, but the usage is a little different.

This article permanently updates the link address: