Generated Column是什么

MySQL 在 5.7 版本中新增了生成列（Generated Column）的功能，它可以通过函数表达式计算获得新的一列数据，我们可以在函数表达式查询中通过构建生成列加索引的方式提高查询效率、分区表可使用生成列进行分区等，由于生成列默认是虚拟类型，因此通常称之为虚拟列。

基本语法

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

生成列提供了两种类型：

• VIRTUAL：默认类型，计算列值不会存储，建议在计算列上加索引。
• STORED：计算列值会被存储，建议在计算列上加索引。

用法

MySQL官方文档示例：

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;

使用场景

虚拟列加索引提升查询效率

实例表结构如下，有一个表需要按照星期查询：

我们想要查询日期是星期一对应的数据有哪些（在查询条件中使用dayofweek函数将日期转换为周）：

我们看到该查询执行计划并不会走tb01Date列建的索引，导致该表达式查询全表扫描：

我们创建一个有虚拟列的表，该虚拟列的表达式是dayofweek：

通过explain看到查询还是全表扫：

我们给虚拟列tb02Date_dayofweek加一个索引，再建一个覆盖索引tb02Date_dayofweek_item。

通过以下实验证明虚拟列索引是可以和普通索引一起创建的。结果很显然，mysql选择了覆盖索引：

虚拟列在分区表中的应用

MySQL 5.7 及以上版本，可以把虚拟列运用在表分区中：

总结建议

Generated Colum的使用限制：

(1) CREATE TABLE ... SELECT语句创建的目标表不会维护原表的虚拟列。
(2) 外键约束不能引用virtual类型的生成列。
(3) 触发器不能使用new.col_name或使用old.col_name引用虚拟列。
(4) 在生成列定义中不能使用auto_increment属性。

Generated Colum的作用：

（1）虚拟列可以当作函数索引使用，提高需要函数类查询的效率。
（2）虚拟列可以一定程度减少冗余数据，提高写入性能。
（3）可应用到分区表业务场景。
（4）虚拟列可以对 JSON 类型不同的 KEY 来建索引，提高检索速度。

Generated Colum使用建议：

虚拟列创建默认创建VIRTUAL类型，这种列数据并不实际存储在磁盘上，只有读取时才做实时计算，对CPU资源有一定消耗；当有实际查询需求时可以增加虚拟列及相关索引来提高查询效率，但没有必要需求时不建议随意使用虚拟列。