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的混合使用

创建语句

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

使用

例如,知道直角三角形的两条直角边,要求斜边的长度。很明显,斜边的长度可以通过两条直角边计算而得,那么,这时候就可以在数据库中只存放直角边,斜边使用Generated Column,如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
-- 创建表
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 )
  • 子查询、参数、变量和用户定义的函数都是不允许的

用法示例

1
2
3
4
5
6
7
8
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

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

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

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

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

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

重命名生成的列

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

删除生成的列

1
alter table test drop column times_score_new;

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

1
2
3
alter table test drop column total_score;

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

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

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

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

1
alter table test modify column total_score double;
打赏

请我喝杯咖啡吧~

支付宝
微信