MySQL 5.7新特性之generated column

MySQL 5.7引入了generated column，这篇文章简单地介绍了generated column的使用方法和注意事项，为读者了解MySQL 5.7提供一个快速的、完整的教程。这篇文章围绕以下几个问题展开：

1. generated column是什么
2. virtual column与stored column的区别
3. 如果我对generated column做一些破坏行为会怎么样
4. generated column上创建索引
5. 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 |
+-------+-------+--------------------+

 

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.