MySQL 5.7引入了generated column，这篇文章简单地介绍了generated column的使用方法和注意事项，为读者了解MySQL 5.7提供一个快速的、完整的教程。这篇文章围绕以下几个问题展开：
- generated column是什么
- virtual column与stored column的区别
- 如果我对generated column做一些破坏行为会怎么样
- generated column上创建索引
- generated column上创建索引与Oracle的函数索引的区别
1. generated column是什么
generated column是MySQL 5.7引入的新特性，所谓generated column，就是数据库中这一列由其他列计算而得，我们以官方参考手册中的例子予以说明。
例如，知道直角三角形的两条直角边，要求斜边的长度。很明显，斜边的长度可以通过两条直角边计算而得，那么，这时候就可以在数据库中只存放直角边，斜边使用generated column，如下所示：
CREATE TABLE triangle (
sidea DOUBLE,
sideb DOUBLE,
sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
查询结果：
mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec |
+-------+-------+--------------------+
| 1 | 1 | 1.4142135623730951 |
| 3 | 4 | 5 |
| 6 | 8 | 10 |
+-------+-------+--------------------+
这个例子就足以说明generated columns是什么、怎么用了。
2. virtual generated column与stored generated column的区别
在MySQL 5.7中，支持两种generated column，即virtual generated column和stored generated column，前者只将generated column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将generated column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与virtual column相比并没有优势，因此，MySQL 5.7中，不指定generated column的类型，默认是virtual column。此外：
- stored generated column性能较差，见这里
- 如果需要stored generated column的话，可能在generated column上建立索引更加合适，见本文第4部分的介绍
综上，一般情况下，都使用virtual generated column，这也是MySQL默认的方式，如果使用stored generated column，前面的建表语句将会是下面这样，即多了一个stored关键字：
Create Table: CREATE TABLE `triangle` (
`sidea` double DEFAULT NULL,
`sideb` double DEFAULT NULL,
`sidec` double GENERATED ALWAYS AS (SQRT(sidea * sidea + sideb * sideb)) STORED
)
3. 如果我对generated column做一些破坏行为会怎么样
我们已经知道了generated column是什么，并且知道了如何使用generated column，为了避免误用，我们先来进行一些实验，以免在具体使用时出现一些未知的情况。
-
将generated column定义为 “除以0”
如果我们将generated column定义为 “x列 / 0”，MySQL并不会直接报错，而是在插入数据时报错，并提示”ERROR 1365 (22012): Division by 0”
mysql> create table t( x int, y int, z int generated always as( x / 0)); Query OK, 0 rows affected (0.22 sec) mysql> insert into t(x,y) values(1,1); ERROR 1365 (22012): Division by 0 -
插入恶意数据
如果我们将generated column定义为 “x列/y列”，在插入数据，如果y列为0的话，同样提示错误，如下所示：
mysql> create table t( x int, y int, z int generated always as( x / y)); Query OK, 0 rows affected (0.20 sec) mysql> insert into t(x,y) values(1,0); ERROR 1365 (22012): Division by 0 -
删除源列
如果我们将generated column定义为 “x列/y列”，并尝试删除x列或y列，将提示”ERROR 3108 (HY000): Column ‘x’ has a generated column dependency.”
mysql> create table t( x int, y int, z int generated always as( x / y)); Query OK, 0 rows affected (0.24 sec) mysql> alter table t drop column x; ERROR 3108 (HY000): Column 'x' has a generated column dependency. -
定义显然不合法的generated column
如果我们将generated column定义为 “x列+y列”，很明显，x列或y列都是数值型，如果我们将x列或y列定义(或修改)为字符型（当然，实际使用时应该不会有人傻到这样去做），则预期会报错，然而并没有，如下所示，我们可以正常创建。
mysql> create table t( x int, y varchar(100), z int generated always as( x + y)); Query OK, 0 rows affected (0.13 sec)并且插入如下这样的数据也不会出错：
mysql> insert into t(x,y) values(1,'0'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +------+------+------+ | x | y | z | +------+------+------+ | 1 | 0 | 1 | +------+------+------+ 1 row in set (0.00 sec)但是对于MySQL无法处理的情况，则会报错：
mysql> insert into t(x,y) values(1,'x'); ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'x'
4. generated column上创建索引
同样，我们可以在generated column上建立索引，建立索引以后，能够加快查找速度，如下所示：
mysql> create table t(x int primary key, y int, z int generated always as (x / y), unique key idz(z));
Query OK, 0 rows affected (0.11 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`x` int(11) NOT NULL,
`y` int(11) DEFAULT NULL,
`z` int(11) GENERATED ALWAYS AS (x / y) VIRTUAL,
PRIMARY KEY (`x`),
UNIQUE KEY `idz` (`z`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
并且，我们可以创建普通索引和唯一索引，如果是唯一索引，在违反了唯一性约束时，进行报错：
mysql> insert into t(x,y) values(1,1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t(x,y) values(2,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'idz'
所以，在使用MySQL5.7时，还需要对generated column有所了解，才能够解决一些以前没有遇到过的问题。
索引的限制：
虽然一般情况下，我们都应该使用virtal generated column，但是，目前使用virtual generated column还有很多限制，包括：
-
聚集索引不能包含virtual generated column
mysql> create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b), primary key(c)); ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns. mysql> create table t1(a int, b int , c int GENERATED ALWAYS AS (a / b) STORED, primary key(c)); Query OK, 0 rows affected (0.11 sec) - 不能在virtual generated column上创建全文索引和空间索引，这个在之后的MySQL版本中有望解决，见这里。
- virtual generated column不能作为外键
-
创建generated column(包括virtual generated column 和stored generated column)时不能使用非确定性的（不可重复的）函数
mysql> ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) virtual; ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function. mysql> ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime()) stored; ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function.
5. generated column上创建索引与Oracle的函数索引的区别
介绍完MySQL在generated column上的索引，熟悉Oracle的同学这时候可能会想起Oracle的函数索引，在MySQL的generated column列上建立索引与Oracle的函数索引比较类似，又有所区别：
例如，我们有一张表，如下所示：
mysql> CREATE TABLE t1 (first_name VARCHAR(10), last_name VARCHAR(10));
Query OK, 0 rows affected (0.11 sec)
假设这时候需要建一个full_name的索引，在Oracle中，我们可以直接在创建索引的时候使用函数，如下所示：
alter table t1 add index full_name_idx(CONCAT(first_name,' ',last_name));
但是，上面这条语句在MySQL中就会报错。在MySQL中，我们可以先新建一个generated column，然后再在这个generated column上建索引，如下所示：
mysql> alter table t1 add column full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name));
mysql> alter table t1 add index full_name_idx(full_name);
乍一看，MySQL需要在表上增加一列，才能够实现类似Oracle的函数索引，似乎代价会高很多。但是，我们在第2部分说过，对于virtual generated column，MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，因此，在MySQL的virtual generated column上建立索引和Oracle的函数索引类似，并不需要更多的代价，只是使用方式有点不一样而已。