What is Generated Column

MySQL 5.7 introduces a new feature, "Generated Column", which can generate a new column of data via function expression calculation. In the function expression query, we may raise the query efficiency by building a generated column plus index. Also, you may partition the table using the "Generated Column". Since "Generated Column" is virtual by default, it is usually called "Virtual Column".

Basic Syntax

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

The generated column has two types:

• VIRTUAL: type by default, computed column values are not stored, and you are advised to add an index to the computed column.
• STORED: computed column values may be stored, and you are advised to add an index to the computed column.

Usage

MySQL's official documentation example:

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);

mysql> SELECT * FROM triangle;

Application scenarios

Add an index to a virtual column, raising the query efficiency

The instance table structure is as follows, and one table needs to be queried by week:

We want to query the data on the date (Monday) (in the query conditions, the date is converted into week using "dayofweek" function):

We see that this query execution plan does not get through the index created on the "tb01Date" column, leading to a full table scan in this expression query.

We have created a table with a virtual column, and this virtual column's expression is "dayofweek":

Through "explain" we see that the query is still "Full Table Scan":

We add an index to virtual column "tb02Date_dayofweek", and then create a covering index "tb02Date_dayofweek_item";

Through the following experiment we prove that virtual column index may be created together with a common index;

The result is obvious: mysql selects the covering index:

Application of virtual column in table partitioning

In later versions from Mysql5.7, you may apply virtual column in table partitioning:

Conclusions & suggestions

Use limits of Generated Colum:

(1) The target table created by the CREATE TABLE ... SELECT statement does not maintain the virtual columns of the original table;
(2) Outer key constraints cannot reference virtual generated columns;；
(3) Triggers cannot use new.col_name or old.col_name to reference virtual columns;
(4) The auto_increment attribute cannot be used in a generated column definition.

Roles of the Generated Colum:

(1) Virtual column may be used as a functional index to raise the efficiency of querying function types required.
(2) Virtual column is used to reduce the redundant data and raise the write-in performance to some extent.
(3) Virtual column may be applied in table partitioning service.
(4) Virtual column may be used to create an index for different KEYS of JSON type, raising the retrieval speed.

Suggestions for usage of Generated Colum:

Creation of virtual column: VIRTUAL type is created by default, and column data of this kind is not stored on the disk, and they are subjected to the real-time calculation when being read, which thus consumes some CPU resources. If there is actual query need, you are advised to increase the number of virtual columns and related indexes to raise query efficiency. However, you should not use the virtual column at will if there are no necessary needs.