MySQL Generated Column（衍生列）

野性酷女 2022-04-08 10:21 1505阅读 0赞

**一、定义**  
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类型的衍生列，跟普通列创建索引无区别。

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