共计 5662 个字符，预计需要花费 15 分钟才能阅读完成。
这期内容当中丸趣 TV 小编将会给大家带来有关怎么理解 MySQL 5.7 中的 Generated Column，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
正文
MySQL 5.7 引入了 Generated Column，这篇文章简单地介绍了 Generated Column 的使用方法和注意事项，为读者了解 MySQL 5.7 提供一个快速的、完整的教程。这篇文章围绕以下几个问题展开：
Generated Column 是 MySQL 5.7 引入的新特性，所谓 Cenerated 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 是什么，以及怎么使用用了。
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 Golumn 的话，可能在 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)
如果对 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
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 版本中有望解决（Inside 君咋记得 Stored Column 上市可以的呢？）。
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.
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 的函数索引类似，并不需要更多的代价，只是使用方式有点不一样而已。
上述就是丸趣 TV 小编为大家分享的怎么理解 MySQL 5.7 中的 Generated Column 了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。
