怎么理解MySQL 5.7中的Generated Column

本期，边肖将向您介绍如何理解MySQL 5.7中的生成列。文章内容丰富，从专业角度进行分析和描述。希望你看完这篇文章能有所收获。

主体

MySQL 5.7引入了生成列。本文简要介绍了Generated Column的用法和注意事项，为读者了解MySQL 5.7提供了一个快速完整的教程。本文主要关注以下问题：

生成列是MySQL 5.7引入的新功能。所谓的Cenerated Column是指数据库中的这个列是由其他列计算的。我们将在官方参考手册中举例说明。

例如，知道直角三角形的两条直角边需要斜边的长度。显然，斜边的长度可以用两条直角边来计算。然后，此时，只有直角边可以存储在数据库中，斜边使用生成列，如下所示：

创建表格三角形(

sidea DOUBLE，

sideb DOUBLE，

sidec DOUBLE AS(SQRT(sidea * sidea sidb * sidb))；

插入三角形(边带，边带)值(1，1)，(3，4)，(6，8)；

查询结果：

mysqlSELECT*FROM三角形；

- - -

| sidea | sideb | sidec |

- - -

|1|1|1.4142135623730951|

|3|4|5|

|6|8|10|

- - -

这个例子足以说明什么是生成列以及如何使用它们。

Virtual Generated Column与Stored Generated Column的区别

在MySQL 5.7中，支持两种生成列，即虚拟生成列和存储生成列。前者只将生成的列保存在数据字典(表的元数据)中，不将该列的数据保存到磁盘上。后者将生成的列保存到磁盘，而不是每次读取时都进行计算。显然，后者存储的数据是现有数据可以计算的，需要更多的磁盘空间，与Virtual Column相比没有优势。因此，在MySQL 5.7中，没有指定生成列的类型，默认为虚拟列。此外：

存储的生成列性能不佳，请参见此处。

如果需要存储的生成列，在生成列上建立索引可能更合适，如本文第4部分所述。

综上所述，一般使用虚拟生成列，这也是MySQL的默认方式。如果使用存储的生成列，前面的建表语句将如下所示，即还有一个存储的关键字：

class="dp-css none_number list-paddingleft-2">

如果对generated column做一些破坏行为会怎么样? 

我们已经知道了generated column是什么，并且知道了如何使用generated column，为了避免误用，我们先来进行一些实验，以免在具体使用时出现一些未知的情况。 

1. 将generated column定义为 "除以0" 

2. 如果我们将generated column定义为 "x列 / 0"，MySQL并不会直接报错，而是在插入数据时报错，并提示"ERROR 1365 (22012): Division by 0" 

3. mysql> create table t( x int, y int, z int generated always as( x / 0)); 

4. Query OK, 0 rows affected (0.22 sec) 

5. mysql> insert into t(x,y) values(1,1); 

6. ERROR 1365 (22012): Division by 0 

插入恶意数据 

如果我们将generated column定义为 "x列/y列"，在插入数据，如果y列为0的话，同样提示错误，如下所示： 

1. mysql> create table t( x int, y int, z int generated always as( x / y)); 

2. Query OK, 0 rows affected (0.20 sec) 

3. mysql> insert into t(x,y) values(1,0); 

4. ERROR 1365 (22012): Division by 0 

删除源列 

 如果我们将generated column定义为 "x列/y列"，并尝试删除x列或y列，将提示"ERROR 3108 (HY000): Column 'x' has a generated column dependency." 

1. mysql> create table t( x int, y int, z int generated always as( x / y)); 

2. Query OK, 0 rows affected (0.24 sec) 

3. mysql> alter table t drop column x; 

4. ERROR 3108 (HY000): Column 'x' has a generated column dependency. 

定义显然不合法的Generated Column 

 如果我们将generated column定义为 "x列+y列"，很明显，x列或y列都是数值型，如果我们将x列或y列定义(或修改)为字符型（当然，实际使用时应该不会有人傻到这样去做），则预期会报错，然而并没有，如下所示，我们可以正常创建。 

1.  mysql> create table t( x int, y varchar(100), z int generated always as( x + y)); 

2.  Query OK, 0 rows affected (0.13 sec) 

3. 并且插入如下这样的数据也不会出错： 

4. mysql> insert into t(x,y) values(1,'0'); 

5. Query OK, 1 row affected (0.01 sec) 

6. mysql> select * from t; 

7. +------+------+------+ 

8. | x | y | z | 

9. +------+------+------+ 

10. | 1 | 0 | 1 | 

11. +------+------+------+ 

12. 1 row in set (0.00 sec) 

但是对于MySQL无法处理的情况，则会报错： 

1. mysql> insert into t(x,y) values(1,'x'); 

2. ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'x' 

3. Generated Column上创建索引 

4. 同样，我们可以在generated column上建立索引，建立索引以后，能够加快查找速度，如下所示： 

5. mysql> create table t(x int primary key, y int, z int generated always as (x / y), unique key idz(z)); 

6. Query OK, 0 rows affected (0.11 sec) 

7. mysql> show create table t\G 

8. *************************** 1. row *************************** 

9. Table: t 

10. Create Table: CREATE TABLE `t` (

11.   `x` int(11) NOT NULL,

12.   `y` int(11) DEFAULT NULL,

13.   `z` int(11) GENERATED ALWAYS AS (x / y) VIRTUAL,

14.   PRIMARY KEY (`x`),

15.   UNIQUE KEY `idz` (`z`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 

16. 1 row in set (0.01 sec) 

并且，我们可以创建普通索引和唯一索引，如果是唯一索引，在违反了唯一性约束时，进行报错： 

1. mysql> insert into t(x,y) values(1,1); 

2. Query OK, 1 row affected (0.02 sec) 

3. mysql> insert into t(x,y) values(2,2); 

4. ERROR 1062 (23000): Duplicate entry '1' for key 'idz' 

所以，在使用MySQL5.7时，还需要对Generated Column有所了解，才能够解决一些以前没有遇到过的问题。 

1. 索引的限制 

1. 虽然一般情况下都应该使用Virtal Generated Column，但是，目前使用Virtual Generated Column还有很多限制，包括： 

2. 聚集索引不能包含virtual generated column 

3. mysql> create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b), primary key(c)); 

4. ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns. 

5. mysql> create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b) STORED, primary key(c)); 

6. Query OK, 0 rows affected (0.11 sec) 

7. 不能在Virtual Generated Column上创建全文索引和空间索引，这个在之后的MySQL版本中有望解决（Inside君咋记得Stored Column上市可以的呢？）。 

8. Virtual Generated Column不能作为外键 

9. 创建generated column(包括virtual generated column 和stored generated column)时不能使用非确定性的（不可重复的）函数 

10. mysql> ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) virtual; 

11. ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function. 

13. mysql> ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) stored; 

14. ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function. 

1. Generated Column上创建索引与Oracle的函数索引的区别 

1. 介绍完MySQL在Generated Column上的索引，熟悉Oracle的同学这时候可能会想起Oracle的函数索引，在MySQL的Generated Column列上建立索引与Oracle的函数索引比较类似，又有所区别： 

3. 例如有一张表，如下所示： 

5. mysql> CREATE TABLE t1 (first_name VARCHAR(10), last_name VARCHAR(10)); 

6. Query OK, 0 rows affected (0.11 sec) 

8. 假设这时候需要建一个full_name的索引，在Oracle中，我们可以直接在创建索引的时候使用函数，如下所示： 

10. alter table t1 add index full_name_idx(CONCAT(first_name,' ',last_name)); 

11. 但是，上面这条语句在MySQL中就会报错。在MySQL中，我们可以先新建一个Generated Column，然后再在这个Generated Column上建索引，如下所示： 

12. mysql> alter table t1 add column full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)); 

13. mysql> alter table t1 add index full_name_idx(full_name); 

乍一看，MySQL需要在表上增加一列，才能够实现类似Oracle的函数索引，似乎代价会高很多。但是，我们在第2部分说过，对于Virtual Generated Column，MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，因此，在MySQL的Virtual Generated Column上建立索引和Oracle的函数索引类似，并不需要更多的代价，只是使用方式有点不一样而已。 

上述就是小编为大家分享的怎么理解MySQL 5.7中的Generated Column了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注行业资讯频道。