MySQL Generated Column（衍生列）

一、定义
Generated Column，就叫衍生列吧，利用已有的列，通过一些运算转换，生成一个新的列，就像繁衍后代一样。看例子就清晰：

mysql> create table test1(
    -> col1 int primary key,
    -> col2 int generated always as (col1+7) stored
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc test1;
+-------+---------+------+-----+---------+------------------+
| Field | Type    | Null | Key | Default | Extra            |
+-------+---------+------+-----+---------+------------------+
| col1  | int(11) | NO   | PRI | NULL    |                  |
| col2  | int(11) | YES  |     | NULL    | STORED GENERATED |
+-------+---------+------+-----+---------+------------------+
2 rows in set (0.00 sec)

col2正是一个衍生列，通过col1+7的依赖关系来生成。既然是衍生列，那就必须是纯自然打造的，无法人工指定。

mysql> insert into test1 values (1,5);
ERROR 3105 (HY000): The value specified for generated column 'col2' in table 'test1' is not allowed.
mysql> insert into test1 values (1,8);
ERROR 3105 (HY000): The value specified for generated column 'col2' in table 'test1' is not allowed.

只能让col1衍生出col2：

mysql> insert into test1(col1) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test1;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    8 |
+------+------+
1 row in set (0.00 sec)

同时，衍生列分为两种类型：stored和virtual。stored即实际存储数据的，而virtual是不存储列上的数据的。

衍生列的定义可以修改，但virtual和stored之间不能相互转换，必要时需要删除重建：

mysql> alter table test1 modify col2 int generated always as (col1+1) stored;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test1(col1) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    2 |    3 |
+------+------+
2 rows in set (0.00 sec)

mysql> alter table test1 modify col2 int generated always as (col1+1) virtual;
ERROR 3106 (HY000): 'Changing the STORED status' is not supported for generated columns.

可以将已存在的普通列转化为stored类型的衍生列，但virtual类型不行：

mysql> create table test2 (
    -> col1 int primary key,
    -> col2 int 
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test2 values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> alter table test2 modify col2 int generated always as (col1+7) stored;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    8 |
+------+------+
1 row in set (0.00 sec)

同样的，可以将stored类型的衍生列转化为普通列，但virtual类型的不行。

二、衍生列和索引
MySQL可以在衍生列上面创建索引。对于stored类型的衍生列，跟普通列创建索引无区别。

mysql> desc test1;
+-------+---------+------+-----+---------+------------------+
| Field | Type    | Null | Key | Default | Extra            |
+-------+---------+------+-----+---------+------------------+
| col1  | int(11) | NO   | PRI | NULL    |                  |
| col2  | int(11) | YES  |     | NULL    | STORED GENERATED |
+-------+---------+------+-----+---------+------------------+

mysql> alter table test1 add key `idx_col2` (col2);   
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test1 |          0 | PRIMARY  |            1 | col1        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| test1 |          1 | idx_col2 |            1 | col2        | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

对于virtual类型的衍生列，创建索引时，会将衍生列值物化到索引键里，即把衍生列的值计算出来，然后存放在索引里。如果衍生列上的索引起到了覆盖索引的作用，那么衍生列的值将直接从覆盖索引里读取，而不再依据衍生定义去计算。
针对virtual类型的衍生列索引，在insert和update操作时会消耗额外的写负载，因为更新衍生列索引时需要将衍生列值计算出来，并物化到索引里。但即使这样，也比stored类型的衍生列好，有索引就避免了每次读取数据行时都需要进行一次衍生计算，同时stored类型衍生列实际存储数据，使得主键索引更大更占空间。
virtual类型的衍生列索引使用MVCC日志，避免在事务rollback或者purge操作时重新进行不必要的衍生计算。