MySQL 5.7 新特性：Generated Column

前言

不仅是 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 数据库也会变得越来越易于管理和维护，整体的性能也会不断提升，在这个时代，数据库也要开始慢慢习惯定期更新版本，使用新功能来更好的支撑开发，运维人员的工作了。