MySQL 5.7 新特性:Generated Column–云惠网

avatar
avatar
云惠网小编
1563
文章
0
评论
2020年9月1日17:03:56 评论 14 次浏览 2004字阅读6分40秒
摘要

不仅是 MySQL 8.0,5.7 版本其实也推出不过少新功能,Generated Column 在 5.7 就算是一个比较实用的功能,能以巧妙的方式应对一些比较棘手而紧急的需求。

前言

不仅是 MySQL 8.0,5.7 版本其实也推出不过少新功能,Generated Column 在 5.7 就算是一个比较实用的功能,能以巧妙的方式应对一些比较棘手而紧急的需求。

原理简介

顾名思义,Generated Column 的效果就是一个依据某些规则“生成”出来的列,并非表中的常规列。“生成”的列的有两种类型:VIRTUAL 和 STORED。

  • VIRTUAL:列中的值并没有实际存储在磁盘中,而是在需要的时候实时计算,计算过程发生在触发 trigger 的 BEFORE 关键字之前。这是 MySQL 默认使用的类型。
  • STORED:列中的值在发生变化的时候都会实时计算,并存储在磁盘之中。

Generated Column 的使用方式与技巧,主要在于进行计算时依据的“某些规则”。这些规则目前有如下的需要注意的地方:

  • 支持 MySQL 内建的各种函数,包括 MIN,MAX,SUBSTRING 等函数。
    • 不支持 CONNECTION_ID(),NOW() 这一类函数,因为这些函数的返回值会随时变化。
  • 不支持存储过程和用户自定义的函数。
  • 不支持子查询。
  • 可以针对普通列的数据进行计算,也可以使用其他 Generated Column 的数据进行计算,生成新的列。
  • 不可以基于自增列来生成新的列。
  • SQL_MODE 的设置可能会影响计算的结果,使用时需要注意。
  • CREATE TABLE ... LIKE ... 生成的新表会拷贝 Generated Column 的定义。
  • 可以使用 Generated Column 作为分区表的分区列。
  • 可以使用 Generated Column 作为外键。
  • 可以使用 Generated Column 创建索引。

测试一下

实际动手试一下比看文字要直观很多,这里使用腾讯云数据库 MySQL 作为测试环境,来试试这个特性。

测试用的 SQL 语句:

CREATE TABLE `stu` (
  `id` int(11) NOT NULL,
  `sname` varchar(16) NOT NULL,
  `cname` varchar(8) DEFAULT NULL,
  `math` int(11) NOT NULL,
  `eng` int(11) DEFAULT NULL,
  `his` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into stu(id,sname,cname,math,eng,his) values(100,'Dave',1,100,80,77);
insert into stu(id,sname,cname,math,eng,his) values(101,'Lily',1,87,91,99);
insert into stu(id,sname,cname,math,eng,his) values(102,'Wolf',3,82,99,89);
insert into stu(id,sname,cname,math,eng,his) values(103,'Tom',2,69,74,75);

到目前为止,这个测试表算是常见的 MySQL 信息表。

这里创建两个 Generated Column,用来计算总分和显示学生的班级归属:

ALTER TABLE stu ADD COLUMN total INT GENERATED ALWAYS AS (math+eng+his);
ALTER TABLE stu ADD COLUMN class_info VARCHAR(32) GENERATED ALWAYS AS (concat(sname,' is in classe ',cname)) stored;

STORED 和 VIRTUAL 的差别在 ALTER 的时候就能看出来了:

效果对比

可以看到 STORED 的时候,是有数据变更的,但是 VIRTUAL 的时候没有,因此在使用 STORED 的时候要注意,大表上的操作可能会持续很久,产生的影响和大表 ALTER 操作基本一致

现在再 insert 几行数据,会发现新添加的行会自动计算出 Generated Column 的内容:

查看结果

PS:这里要注意,insert 如果不带列会报错:Column count doesn't match value count at row 1,SQL 规范的中 insert 带列名是正规的做法,手写 SQL 的时候要留意一下,框架一般会自动带上。

在 Generated Column 上创建索引也是可以的:

索引效果

如果需要利用 Generated Column 来查找数据,那么就可以创建索引,加快查询效率。

总结一下

Generated Column 通过函数等规则生成的列,配合上索引,分区表的分区键等功能,可以完成一些以前比较难处理的问题和需求,在之后的案例挖掘会陆陆续续的介绍。

随着新功能的不断发布,MySQL 数据库也会变得越来越易于管理和维护,整体的性能也会不断提升,在这个时代,数据库也要开始慢慢习惯定期更新版本,使用新功能来更好的支撑开发,运维人员的工作了。

腾讯云618
avatar
腾讯云618
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: