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

官网原文：https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

注：以下是我参考官网文档和结合自己的理解写的，因为英文水平有限，不排除有错误，欢迎纠错。

一、说明

Generated Column是MySQL 5.7引入的新特性，所谓Cenerated Column，就是数据库中这一列由其他列计算而得。在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column（虚拟生成的列）和Stored Generated Column（存储生成的列），二者含义如下：

1、Virtual Generated Column（虚拟生成的列）：不存储该列值，即MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，而是当读取该行时，触发触发器对该列进行计算显示。InnoDB支持Virtual Generated Column，具体参考“https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html”
2、Stored Generated Column（存储生成的列）：存储该列值，即该列值在插入或更新行时进行计算和存储。所以相对于Virtual Column列需要更多的磁盘空间，与Virtual Column相比并没有优势。因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column
在表中允许Virtual Column和Stored Column的混合使用
提高效率：由于mysql在普通索引上加函数会造成索引失效，造成查询性能下降，Generated Column（函数索引）刚好可以解决这个问题，可以在Generated Column加上索引来提高效率

二、创建规则

1 col_name data_type [GENERATED ALWAYS] AS (expression)
2   [VIRTUAL | STORED] [NOT NULL | NULL]
3   [UNIQUE [KEY]] [[PRIMARY] KEY]
4   [COMMENT 'string']

三、使用

例如，知道直角三角形的两条直角边，要求斜边的长度。很明显，斜边的长度可以通过两条直角边计算而得，那么，这时候就可以在数据库中只存放直角边，斜边使用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是什么，以及怎么使用了。

注意事项：

1、可以给出其他属性来指示该列是否被索引或可以为null，或提供注释。
2、我们在generated column上建立索引，建立索引以后，能够加快查找速度

索引的限制：

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

1、聚集索引不能包含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)

2、不能在Virtual Generated Column上创建全文索引和空间索引

-- 全文索引

mysql> CREATE TABLE `article` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `message` varchar(200) DEFAULT NULL, `content` text GENERATED ALWAYS AS (concat(`title`,`message`)), PRIMARY KEY (`id`), FULLTEXT KEY `content_idx1` (`content`) ) ; [Err] 3106 - 'Fulltext index on virtual generated column' is not supported for generated columns. -- 全文索引 mysql>CREATE TABLE `article` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `message` varchar(200) DEFAULT NULL, `content` text GENERATED ALWAYS AS (concat(`title`,`message`)) STORED, PRIMARY KEY (`id`), FULLTEXT KEY `content_idx1` (`content`) ) ; Query OK, 0 rows affected (0.11 sec)

3、Virtual Generated Column不能作为外键
4、创建generated column(包括virtual generated column 和stored generated column)时可以使用确定性内置函数和运算符,但不能使用非确定性的（不可重复的）函数。

mysql> ALTER TABLE `score` ADD deal_time DATE GENERATED ALWAYS AS (now()) virtual;
[Err] 3102 - Expression of generated column 'deal_time' contains a disallowed function.

 

mysql> ALTER TABLE `score` ADD deal_time DATE GENERATED ALWAYS AS (now()) stored;

[Err] 3102 - Expression of generated column 'deal_time' 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的函数索引，似乎代价会高很多。但是，我们在上面提过，对于Virtual Generated Column，MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，因此，在MySQL的Virtual Generated Column上建立索引和Oracle的函数索引类似，并不需要更多的代价，只是使用方式有点不一样而已。