MySQL生成列（Generated Columns）

2020-12-04 本文已影响0人左轮Lee

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.其他：

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

可指定列是否为NULL，是否主键，是否唯一索引，是否二级索引，是否有COMMENTS。

二、使用限制

支持常量，确定的内置函数及运算符。（确定性函数：对于给定的表里的数据，不同用户多次调用返回相同结果；不确定性函数： CONNECTION_ID(), CURRENT_USER(), NOW() ）

不支持存储函数和UDF（user-defined functions，用户自定义函数）。

不支持存储过程及函数的参数。

不支持使用变量（系统变量，用户定义的变量和存储的程序局部变量）。

不支持子查询。

可以基于生成列再创建生成列，但是被依赖的生成列必须在前；如果基于基础列，则没有先后顺序。

自增（AUTO_INCREMENT ）属性不能用在生成列上。

不能基于自增列创建生成列。

从MySQL 5.7.10开始，如果表达式求值导致截断或向函数提供不正确的输入，则CREATE TABLE语>句将以错误终止并拒绝DDL操作。

其他说明：

如果表达式的计算结果与声明的列类型不同，则根据通常的MySQL类型转换规则，对声明的类型进行隐式强制转换。

对于 CREATE TABLE ... LIKE 建表方式，目标表会保留源表的生成列。

对于 CREATE TABLE ... AS SELECT 建表方式，目标表会复制源表所有字段（包含生成列），但不复制生成列信息。

可以基于生成列进行表分区。

以STORE方式创建的生成列上创建外键约束时，不能将CASCADE，SET NULL或SET DEFAULT用作ON UPDATE引用动作，也不能将SET NULL或SET DEFAULT用作ON DELETE引用动作。

以STORE方式创建的生成列所依赖的基列上创建外键约束时，不能将CASCADE，SET NULL或SET DEFAULT用作ON UPDATE和ON DELETE引用动作。

以VIRTUAL方式创建的生成列不支持外键约束。

触发器不能使用生成列上的 NEW.col_name 和 OLD.col_name 值。

如果在生成列上进行显示的 INSERT, REPLACE和 UPDATE，则唯一允许的值是 DEFAULT 。

视图中也可以显示的更新生成列，但唯一允许的值也只有 DEFAULT 。

三、适用场景

VIRTUAL生成列可以用来简化和统一查询。一个复杂的查询条件可以被定义成一个生成列，让涉及到这个表上的多个查询确保使用相同的查询条件。

STORED生成列可以被那些快速的复杂计算条件当作物化缓存使用。

生成列可以被当作函数索引：用生成列定义一个函数表达式，然后在上面添加索引；此外，生成列也可用在不能直接添加索引的列上，如 json 类型字段。
对于STORED生成列，缺点就是值被存储了两次，一次在生成列中，一次在索引中。

如果生成列上有索引，那么优化器将识别查询中的表达式去匹配生成列的定义，然后在查询执行的时候引用生成列，即使你在查询中使用的是表达式，而不是生成列。

四、使用案例

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

MySQL生成列（Generated Columns）

目录：

一、生成列（Generated Columns）介绍

二、使用限制

三、适用场景

四、使用案例

五、参考文档

一、生成列（Generated Columns）介绍

二、使用限制

三、适用场景

四、使用案例

五、参考文档

猜你喜欢

热点阅读