MySQL 5.7 new features generated column

MySQL 5.7 introduces generated column, which simply introduces the use and considerations of generated column, providing a quick and complete tutorial for the reader about MySQL 5.7. This article focuses on the following issues:

1. What is generated column?
2. The difference between virtual column and stored column
3. What if I did some destructive behavior to generated column?
4. Create an index on generated column
5. The difference between creating an index on generated column and the function index of Oracle

1. What is generated column?

Generated column is a new feature introduced by MySQL 5.7, the so-called generated column, that is, the column in the database is computed by the other columns , and we use the examples in the official reference manual to illustrate.

For example, knowing the two right-angled edges of a right triangle requires the length of the hypotenuse. Obviously, the length of the hypotenuse can be calculated from two right-angled edges, so this time you can store only right-angled edges in the database, with the hypotenuse using generated column, as shown below:

CREATE TABLE triangle (  Sidea double,  sideb double,  sidec double as (SQRT (Sidea * Sidea + sideb * sideb))); INS ERT into triangle (Sidea, Sideb) VALUES (+), (3,4), (6,8);

Query Result:

Mysql> SELECT * from triangle;+-------+-------+--------------------+| Sidea | Sideb | Sidec              |+-------+-------+--------------------+|     1 |     1 | 1.4142135623730951 | |     3 |     4 |                  5 | |     6 |     8 |                 Ten |+-------+-------+--------------------+

2. The difference between virtual generated column and stored generated column

In MySQL 5.7, two generated column, virtual generated column and stored generated column, are supported, the former only generated column is stored in the data dictionary (the table's metadata) and does not persist this column of data to disk, which persists the generated column to disk instead of being computed each time it is read. Obviously, the latter stores data that can be computed from existing data, requires more disk space, and has no advantage over virtual column, so MySQL 5.7 does not specify the type of generated column, which by default is virtual column. In addition

• Stored generated column performance is poor, see here
• If stored generated column is required, it may be more appropriate to build an index on the generated column, as described in section 4 of this article.

In general, use virtual generated column, which is the default way of MySQL, if you use stored generated column, the preceding statement will be the following, that is, one more stored keyword:

Create table:create Table ' triangle ' (  ' Sidea ' double default null,  ' sideb ' double default null,  ' Sidec ' Doub Le GENERATED always as (SQRT (Sidea * Sidea + sideb * sideb)) STORED)

3. What happens if I do some damage to generated column?

We already know what generated column is, and we know how to use generated column, and to avoid misuse, let's start with some experiments to avoid some unknown situations when we use them.

• Define generated column as "divided by 0"

  If we define generated column as "x column/0", MySQL will not directly error, but insert the data times wrong and prompt "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 (n);  ERROR 1365 (22012): Division by 0

• Inserting malicious data

If we define generated column as "x column/y column", when inserting the data, if the Y column is 0, the error is also indicated as follows:

  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 the generated column as "x column/y column" and try to delete the X column or the y column, you will be prompted with "ERROR 3108 (HY000): 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.

MySQL 5.7 new features generated column