MySQL 5.7新特性之Generated Column（函数索引）

MySQL 5.7引入了Generated Column，这篇文章简单地介绍了Generated Column的使用方法和注意事项，为读者了解MySQL 5.7提供一个快速的、完整的教程。这篇文章围绕以下几个问题展开： Generated Column是什么
Virtual Column与Stored Column的区别
如果我对Generated Column做一些破坏行为会怎么样
Generated Column上创建索引
Generated Column上创建索引与Oracle的函数索引的区别 Generated Column是什么 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 tG
*************************** 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的函数索引类似，并不需要更多的代价，只是使用方式有点不一样而已。 本文永久更新链接地址