MySQL 表结构设计 - MySQL 虚拟列 - 《Java 开发笔记》

定义
Virtual Generated Column 与 Stored Generated Column 的区别
应用案例
异常操作
Generated Column 上创建索引
- Generated Column 上创建索引的限制
- Generated Column 上创建索引与 Oracle 的函数索引的区别
参考资料

定义

虚拟列（Generated Column）是 MySQL 5.7 引入的新特性，所谓 Cenerated Column，就是数据库中这一列由其他列计算而得。

语法：

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

Virtual Generated Column 与 Stored Generated Column 的区别

在 MySQL 5.7 中，支持两种 Generated Column，即 Virtual Generated Column 和 Stored Generated Column。

Virtual Generated Column：只将 Generated Column 保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上。
Stored Generated Column：会将 Generated Column 持久化到磁盘上，而不是每次读取的时候计算所得。

很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与 Virtual Generated Column 相比并没有优势，因此，MySQL 5.7 中，不指定 Generated Column 的类型，默认是 Virtual Generated Column。

一般情况下，都使用 Virtual Generated Column，这也是 MySQL 默认的方式。

应用案例

MySQL 的 JSON 类型，可以通过虚拟列功能实现对 JSON 数据中的部分内容创建索引。

例子参考：MySQL JSON 类型

异常操作

我们已经知道了 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 有所了解，才能够解决一些以前没有遇到过的问题。

Generated Column 上创建索引的限制

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

聚集索引不能包含 Virtal Generated Column。 ```sql 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 版本中有望解决。 **
3. **Virtual Generated Column 不能作为外键。**
4. **创建 generated column（包括 Virtal Generated Column 和 Stored Generated Column）时不能使用非确定性的（不可重复的）函数。**
```sql
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 的函数索引类似，并不需要更多的代价，只是使用方式有点不一样而已。

参考资料

作者：殷建卫链接：https://www.yuque.com/yinjianwei/vyrvkf/eepd02 来源：殷建卫 - 架构笔记著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。