MySQL5.7新特性之Generated Column

MySQL5.7新特性之Generated Column(生成列)，官网文档。

介绍

Generated Column是MySQL 5.7引入的新特性，所谓Cenerated Column，就是数据库中这一列由其他列计算而得，直译生成列。

在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column（虚拟生成的列）和Stored Generated Column（存储生成的列），二者含义如下：

• Virtual Generated Column（虚拟生成的列）：不存储该列值，即MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，而是当读取该行时，触发触发器对该列进行计算显示。InnoDB支持Virtual Generated Column。
• Stored Generated Column（存储生成的列）： 存储该列值，即该列值在插入或更新行时进行计算和存储。所以相对于Virtual Column列需要更多的磁盘空间，与Virtual Column相比并没有优势。因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。

在表中允许Virtual Column和Stored Column的混合使用

创建语句

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

使用

例如，知道直角三角形的两条直角边，要求斜边的长度。很明显，斜边的长度可以通过两条直角边计算而得，那么，这时候就可以在数据库中只存放直角边，斜边使用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 column上建立索引，建立索引以后，能够加快查找速度
• 生成的列不允许人为指定值，否则会引发ERROR 3105的错误

限制：

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

• 聚集索引不能包含virtual generated column
• 不能在Virtual Generated Column上创建全文索引和空间索引
• Virtual Generated Column不能作为外键
• 创建generated column(包括virtual generated column 和stored generated column)时可以使用确定性内置函数和运算符,但不能使用非确定性的（不可重复的）函数（比如NOW ）
• 子查询、参数、变量和用户定义的函数都是不允许的

用法示例

CREATE TABLE test (
    id INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
    chinese DOUBLE NOT NULL DEFAULT '0',
    math DOUBLE NOT NULL DEFAULT '0',
    english DOUBLE NOT NULL DEFAULT '0',
    total_score DOUBLE AS (chinese + math + english),
    PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

如果要在 insert 语句中包含 total_score 字段名，则只能将其值设为 DEFAULT

insert into test(chinese, math, english, total_score) values(33, 44, 55, DEFAULT);

如果表已经存在了，可以通过alter table语句来创建，修改，删除生成列。

alter table test add column times_score double generated always as (chinese * math * english) stored;

修改生成列的数据类型和表达式

alter table test modify column times_score float generated always as (chinese * math * english * 10) stored;

重命名生成的列

alter table test change times_score times_score_new float generated always as (chinese * math * english * 10) stored;

删除生成的列

alter table test drop column times_score_new;

virtual 列不能更改为 stored 的生成列，反之亦然。只能先删除，然后再重新添加

alter table test drop column total_score;

alter table test add column total_score double generated always as (chinese + math + english) stored;

表中的常规字段，可以修改为 stored 生成列，但不能是 virtual 生成列

alter table test modify column chinese double generated always as (math + 1) stored;

stored 生成列可以修改为常规字段，值为生成值

alter table test modify column total_score double;

打赏