怎么理解MySQL 5.7中的Generated Column

51次阅读

没有评论

共计 5662 个字符，预计需要花费 15 分钟才能阅读完成。

这期内容当中丸趣 TV 小编将会给大家带来有关怎么理解 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)));

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)

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

索引的限制

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

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

不能在 Virtual Generated Column 上创建全文索引和空间索引，这个在之后的 MySQL 版本中有望解决（Inside 君咋记得 Stored Column 上市可以的呢？）。

Virtual Generated Column 不能作为外键

创建 generated column(包括 virtual generated column 和 stored generated column)时不能使用非确定性的（不可重复的）函数

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 的函数索引类似，并不需要更多的代价，只是使用方式有点不一样而已。

上述就是丸趣 TV 小编为大家分享的怎么理解 MySQL 5.7 中的 Generated Column 了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。

正文完