MySQL生成列（Generated Columns）

MySQL: 5.7.25-log

目录：

一、生成列（Generated Columns）介绍

二、使用限制

三、适用场景

四、使用案例

五、参考文档

---

一、生成列（Generated Columns）介绍

1.生成列语法：

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

2.生成列支持两种创建方式，分别对应关键词 VIRTUAL 和 STORED：

VIRTUAL：当行记录被访问的时候自动计算得到，不占用存储空间。支持此列上创建二级索引。
STORED：当行记录被插入或更新时候自动计算并保存起来，占用存储空间。支持此列上创建主键和二级索引。
PS: 不指定关键字时默认创建 VIRTUAL 的生成列。

3.其他：

1. 一个表中可以既包含 VIRTUAL 列，又包含 STORED 列。
2. 可指定列是否为NULL，是否主键，是否唯一索引，是否二级索引，是否有COMMENTS。

二、使用限制

1. 支持常量，确定的内置函数及运算符。（确定性函数：对于给定的表里的数据，不同用户多次调用返回相同结果；不确定性函数： CONNECTION_ID(), CURRENT_USER(), NOW() ）
2. 不支持存储函数和UDF（user-defined functions，用户自定义函数）。
3. 不支持存储过程及函数的参数。
4. 不支持使用变量（系统变量，用户定义的变量和存储的程序局部变量）。
5. 不支持子查询。
6. 可以基于生成列再创建生成列，但是被依赖的生成列必须在前；如果基于基础列，则没有先后顺序。
7. 自增（AUTO_INCREMENT ）属性不能用在生成列上。
8. 不能基于自增列创建生成列。
9. 从MySQL 5.7.10开始，如果表达式求值导致截断或向函数提供不正确的输入，则CREATE TABLE语>句将以错误终止并拒绝DDL操作。

其他说明：

1. 如果表达式的计算结果与声明的列类型不同，则根据通常的MySQL类型转换规则，对声明的类型进行隐式强制转换。
2. 对于 CREATE TABLE ... LIKE 建表方式，目标表会保留源表的生成列。
3. 对于 CREATE TABLE ... AS SELECT 建表方式，目标表会复制源表所有字段（包含生成列），但不复制生成列信息。
4. 可以基于生成列进行表分区。
5. 以STORE方式创建的生成列上创建外键约束时，不能将CASCADE，SET NULL或SET DEFAULT用作ON UPDATE引用动作，也不能将SET NULL或SET DEFAULT用作ON DELETE引用动作。
6. 以STORE方式创建的生成列所依赖的基列上创建外键约束时，不能将CASCADE，SET NULL或SET DEFAULT用作ON UPDATE和ON DELETE引用动作。
7. 以VIRTUAL方式创建的生成列不支持外键约束。
8. 触发器不能使用生成列上的 NEW.col_name 和 OLD.col_name 值。
9. 如果在生成列上进行显示的 INSERT, REPLACE和 UPDATE，则唯一允许的值是 DEFAULT 。
10. 视图中也可以显示的更新生成列，但唯一允许的值也只有 DEFAULT 。

三、适用场景

1. VIRTUAL生成列可以用来简化和统一查询。一个复杂的查询条件可以被定义成一个生成列，让涉及到这个表上的多个查询确保使用相同的查询条件。
2. STORED生成列可以被那些快速的复杂计算条件当作物化缓存使用。
3. 生成列可以被当作函数索引：用生成列定义一个函数表达式，然后在上面添加索引；此外，生成列也可用在不能直接添加索引的列上，如 json 类型字段。
   对于STORED生成列，缺点就是值被存储了两次，一次在生成列中，一次在索引中。
4. 如果生成列上有索引，那么优化器将识别查询中的表达式去匹配生成列的定义，然后在查询执行的时候引用生成列，即使你在查询中使用的是表达式，而不是生成列。

四、使用案例

1.不指定关键字时候默认为 VIRTUAL生成列

mysql> CREATE TABLE `person` (
    ->   `id` int(11) AUTO_INCREMENT PRIMARY KEY,
    ->   `first_name` varchar(50),
    ->   `last_name` varchar(50),
    ->   `full_name` varchar(100) AS (concat(`first_name`,' ',`last_name`))
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show create table person \G
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `full_name` varchar(100) GENERATED ALWAYS AS (concat(`first_name`,' ',`last_name`)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2.VIRTUAL不占用存储空间，STORED占用存储空间

创建三个表：
person0： 包含VIRTUAL生成列 full_name varchar(100) GENERATED ALWAYS AS (concat(first_name,' ',last_name)) VIRTUAL
person1： 与person0 表结构一样
person2： 包含STORED生成列 full_name varchar(100) GENERATED ALWAYS AS (concat(first_name,' ',last_name)) STORED

向三表插入相同的大量数据，然后查询表空间文件大小如下：

mysql>  select name,file_size From information_schema.INNODB_SYS_TABLESPACES where name like '%person%';
+--------------+-----------+
| name         | file_size |
+--------------+-----------+
| test/person0 |  10485760 |
| test/person1 |  10485760 |
| test/person2 |  11534336 |
+--------------+-----------+
person0 与 person1 一致，person2 的STORED 生成列需要更多的空间。

3.一个表中可以既包含 VIRTUAL 列，又包含 STORED 列

CREATE TABLE `person` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `first_name` varchar(50),
  `last_name` varchar(50),
  `full_name1` varchar(100) AS (concat(`first_name`,' ',`last_name`) ) virtual,
  `full_name2` varchar(100) AS (concat(`first_name`,' ',`last_name`) ) stored
)
> OK

4.可指定列是否为NULL，是否唯一索引，是否二级索引，是否有COMMENTS（选择部分测试），但不能使用auto_increment属性

CREATE TABLE `person` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `full_name` varchar(100) GENERATED ALWAYS AS (concat(`first_name`,' ',`last_name`)) VIRTUAL NOT NULL UNIQUE COMMENT '全名'
)
> OK

-- 不支持生成列使用自增属性
报语法错误

-- 不支持基于自增列创建生成列
CREATE TABLE `person` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `id1` varchar(100) GENERATED ALWAYS AS (id+1) 
)
> 3109 - Generated column 'id1' cannot refer to auto-increment column.

5.STORED生成列可指定是否主键，VIRTUAL生成列不支持

CREATE TABLE `person` (
  `id` int(11) ,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `full_name1` varchar(100) GENERATED ALWAYS AS (concat(`first_name`,' ',`last_name`)) STORED NOT NULL PRIMARY KEY COMMENT '全名'
)
> OK

CREATE TABLE `person` (
  `id` int(11) ,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `full_name1` varchar(100) GENERATED ALWAYS AS (concat(`first_name`,' ',`last_name`)) VIRTUAL NOT NULL PRIMARY KEY COMMENT '全名'
)
> 3106 - 'Defining a virtual generated column as primary key' is not supported for generated columns.

6.不支持不确定性函数

CREATE TABLE `person` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `full_name1` varchar(100) GENERATED ALWAYS AS (concat(`first_name`,' ',`last_name`)) VIRTUAL COMMENT '全名',
  `ctime` datetime as (now())
)
> 3102 - Expression of generated column 'ctime' contains a disallowed function.

7.可基于生成列再创建生成列，但有先后顺序，基于基础列便无先后顺序

-- 基于生成列
CREATE TABLE `person` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `full_name` varchar(100) GENERATED ALWAYS AS (concat(`first_name`,' ',`last_name`)) VIRTUAL COMMENT '全名',
  `full_name1` varchar(100) GENERATED ALWAYS AS (`full_name`) VIRTUAL COMMENT '全名1'
)
> OK

CREATE TABLE `person` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `full_name1` varchar(100) GENERATED ALWAYS AS (`full_name`) VIRTUAL COMMENT '全名1',
  `full_name` varchar(100) GENERATED ALWAYS AS (concat(`first_name`,' ',`last_name`)) VIRTUAL COMMENT '全名'
)
> 3107 - Generated column can refer only to generated columns defined prior to it.

-- 基于基础列
CREATE TABLE `person` (
  `id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `full_name` varchar(100) GENERATED ALWAYS AS (concat(`first_name`,' ',`last_name`)) VIRTUAL COMMENT '全名',
  `first_name` varchar(50),
  `last_name`  varchar(50)
)
> OK

8.CREATE TABLE ... LIKE 建表，目标表会保留源表的生成列信息

CREATE TABLE `person` (
  `id` int(11) ,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `full_name` varchar(100)  AS (concat(`first_name`,' ',`last_name`))   COMMENT '全名'
);
> OK

create table person_copy like person;
> OK

-- 查看 person_copy 表结构，验证
show create table person_copy;
CREATE TABLE `person_copy` (
  `id` int(11) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `full_name` varchar(100) GENERATED ALWAYS AS (concat(`first_name`,' ',`last_name`)) VIRTUAL COMMENT '全名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

9.CREATE TABLE ... AS SELECT 建表，不复制生成列信息

CREATE TABLE `person` (
  `id` int(11) ,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `full_name` varchar(100)  AS (concat(`first_name`,' ',`last_name`))   COMMENT '全名'
);
> OK

create table person_copy as select * from person;
> OK

-- 查看 person_copy 表结构，验证
show create table person_copy;
CREATE TABLE `person_copy` (
  `id` int(11) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `full_name` varchar(100) DEFAULT NULL COMMENT '全名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

10.支持基于生成列创建分区表

CREATE TABLE `person` (
  `id` int(11) ,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `p_id` int  AS (id*2)
)
PARTITION BY RANGE (p_id) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999)
)
> OK

11.不论是表还是视图，显示更新生成列时仅支持值 DEFAULT

CREATE TABLE `person` (
  `id` int(11) AUTO_INCREMENT key ,
  `first_name` varchar(50),
  `last_name`  varchar(50),
  `full_name` varchar(100)  AS (concat(`first_name`,' ',`last_name`))   COMMENT '全名'
)
> OK

insert into person (FIRST_name ,last_name ) values('Xingxing','Zhou')
> Affected rows: 1

-- 仅支持 DEFAULT 值
update person set full_name=DEFAULT where id=1
> Affected rows: 0

-- 否则会报错
update person set full_name='Stephen Chow' where id=1
> 3105 - The value specified for generated column 'full_name' in table 'person' is not allowed.

11.基于json字段建 生成列+索引

CREATE TABLE `person` (
  `info` json ,
  `id` int as (info->"$.id")  UNIQUE
)
> OK

INSERT INTO person (info) VALUES
 ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
 ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}')

mysql> explain select * from person where id =3 ;
+----+-------------+--------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | person | NULL       | const | id            | id   | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------+---------+-------+------+----------+-------+

12.使用生成列的表达式进行查询亦可走索引，但条件苛刻

CREATE TABLE `person` (
  `id` int ,
    `num` int as ( id + 1) ,
    index (num)
)
> OK

INSERT INTO person (id) VALUES (1), (2)
> Affected rows: 2

-- 用带索引的生成列查询，可走索引
mysql> explain select * from person where num = 3 ; 
+----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+
|  1 | SIMPLE      | person | NULL       | ref  | num           | num | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+
1 row in set (0.02 sec)

-- 用生成列对应的表达式查询，可走索引
mysql> explain select * from person where id + 1 = 3 ; 
+----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+
|  1 | SIMPLE      | person | NULL       | ref  | num           | num | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+
1 row in set (0.02 sec)

-- 用生成列对应的表达式查询，但匹配的是字符串，相当于有隐式转换，不走索引
mysql> explain select * from person where id + 1 = '3' ; 
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | person | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)

-- 用稍作修改的生成列表达式查询，不走索引（需与表达式完全一致才能走索引）
mysql> explain select * from person where 1 + id = 3 ; 
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | person | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.02 sec)

还有一些限制及特性，就不一一列举了。

五、参考文档

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html
https://dev.mysql.com/doc/refman/5.7/en/generated-column-index-optimizations.html