一、Generated Column
在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。
如果需要Stored Generated Golumn的话,可能在Virtual Generated Column上建立索引更加合适
综上,一般情况下,都使用Virtual Generated Column,这也是MySQL默认的方式
二、语法
<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]
例子:
三、索引
MySQL可以在衍生列上面创建索引。对于stored类型的衍生列,跟普通列创建索引无区别。
对于virtual类型的衍生列,创建索引时,会将衍生列值物化到索引键里,即把衍生列的值计算出来,然后存放在索引里。如果衍生列上的索引起到了覆盖索引的作用,那么衍生列的值将直接从覆盖索引里读取,而不再依据衍生定义去计算。
针对virtual类型的衍生列索引,在insert和update操作时会消耗额外的写负载,因为更新衍生列索引时需要将衍生列值计算出来,并物化到索引里。但即使这样,virtual类型也比stored类型的衍生列好,有索引就避免了每次读取数据行时都需要进行一次衍生计算,同时stored类型衍生列实际存储数据,使得聚簇索引更大更占空间。
四、使用虚拟列的实际场景
1)优化模糊查询,右后缀匹配的模糊查询,可以生成相应的左前缀虚拟列并添加索引
2)索引 json字段。MySQL从5.7.8起开始支持JSON字段,这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能,至少没有直接对其字段进行索引的方法。可以使用虚拟字段的功能来对JSON对象中的字段进行索引。如三中例子所示,添加json中检索字段的虚拟列,并在虚拟列上添加索引用于检索。
more:欢迎补充~