怎么理解MySQL 5.7中的Generated Column

这期内容当中小编将会给大家带来有关怎么理解MySQL 5.7中的Generated Column，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
正文
MySQL 5.7引入了Generated Column，这篇文章简单地介绍了Generated Column的使用方法和注意事项，为读者了解MySQL 5.7提供一个快速的、完整的教程。这篇文章围绕以下几个问题展开：
Generated Column是MySQL 5.7引入的新特性，所谓Cenerated Column，就是数据库中这一列由其他列计算而得，我们以官方参考手册中的例子予以说明。例如，知道直角三角形的两条直角边，要求斜边的长度。很明显，斜边的长度可以通过两条直角边计算而得，那么，这时候就可以在数据库中只存放直角边，斜边使用Generated Column，如下所示：

CREATE TABLE triangle(
sidea DOUBLE,
sideb DOUBLE,
sidec DOUBLE AS(SQRT(sidea*sidea+sideb*sideb)));

INSERTINTOtriangle(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 Gener开发云主机域名ated 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(xint,yint,zintgenerated always as(x / 0));
Query OK,0 rows affected(0.22 sec)

mysql>insertintot(x,y)values(1,1);
ERROR 1365(22012):Division by 0

插入恶意数据如果我们将generated column定义为”x列/y列”，在插入数据，如果y列为0的话，同样提示错误，如下所示：
mysql>create table t(xint,yint,zintgenerated always as(x / y));
Query OK,0 rows affected(0.20 sec)

mysql>insertintot(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(xint,yint,zintgenerated 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(xint,y varchar(100),zintgenerated always as(x+y));
Query OK,0 rows affected(0.13 sec)

并且插入如下这样的数据也不会出错：

mysql>insertintot(x,y)values(1,’0′);
Query OK,1 row affected(0.01 sec)

mysql>select*from t;
+——+——+——+
|x|y|z|
+——+——+——+
|1|0|1|
+——+——+——+
1 rowinset(0.00 sec)
但是对于MySQL无法处理的情况，则会报错：
mysql>insertintot(x,y)values(1,’x’);
ERROR 1292(22007):Truncated incorrect DOUBLE value:’x’

Generated Column上创建索引

同样，我们可以在generated column上建立索引，建立索引以后，能够加快查找速度，如下所示：

mysql>create table t(xintprimary key,yint,zintgenerated 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)NOTNULL,
`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 rowinset(0.01 sec)

并且，我们可以创建普通索引和唯一索引，如果是唯一索引，在违反了唯一性约束时，进行报错：
mysql>insertintot(x,y)values(1,1);
Query OK,1 row affected(0.02 sec)

mysql>insertintot(x,y)values(2,2);
ERROR 1062(23000):Duplicate entry’1’forkey’idz’
所以，在使用MySQL5.7时，还需要对Generated Column有所了解，才能够解决一些以前没有遇到过的问题。

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

聚集索引不能包含virtual generated column

mysql>create table t1(aint,bint,cintGENERATED ALWAYS AS(a / b),primary key(c));
ERROR 3106(HY000):’Defining a virtual generated column as primary key’isnotsupportedforgenerated columns.

mysql>create table t1(aint,bint,cintGENERATED 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`ADDp3 DATE GENERATED ALWAYS AS(curtime())virtual;
ERROR 3102(HY000):Expressionofgenerated column’p3’contains a disallowed function.

mysql>ALTER TABLE `t1`ADDp3 DATE GENERATED ALWAYS AS(curtime())stored;
ERROR 3102(HY000):Expressionofgenerated 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 t1addindex full_name_idx(CONCAT(first_name,’ ‘,last_name));
但是，上面这条语句在MySQL中就会报错。在MySQL中，我们可以先新建一个Generated Column，然后再在这个Generated Column上建索引，如下所示：

mysql>alter table t1addcolumn full_name VARCHAR(255)GENERATED ALWAYS AS(CONCAT(first_name,’ ‘,last_name));

mysql>alter table t1addindex full_name_idx(full_name);
乍一看，MySQL需要在表上增加一列，才能够实现类似Oracle的函数索引，似乎代价会高很多。但是，我们在第2部分说过，对于Virtual Generated Column，MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，因此，在MySQL的Virtual Generated Column上建立索引和Oracle的函数索引类似，并不需要更多的代价，只是使用方式有点不一样而已。上述就是小编为大家分享的怎么理解MySQL 5.7中的Generated Column了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注开发云行业资讯频道。

相关推荐: mysql如何查看数据库中表的行数及进行排序

下文内容主要给大家带来mysql如何查看数据库中表的行数及进行排序，所讲到的知识，与书籍略有不同，都是开发云专业技术人员在与用户接触过程中，总结出来的，具有一定的经验分享价值，希望给广大读者带来帮助。mysql查看数据库中所有表的行数，并进行排序：进行数据库迁…