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)