目录

Generated Column

语法

虚拟列的允许

虚拟列限制使用条件

应用

一、为了实现对json数据中部分数据的索引查询

二、表达式计算数据

参考

Generated Column

在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column。
前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；
后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。Stored类型的generated columns可以用作一个物化缓存，用于复杂的条件，这些条件在运行时计算成本很高。这种方法的缺点是存储两次值；一次作为生成列的值，一次作为索引。
很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与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> ]

 AS (expr)表示生成列，并定义用于计算列值的表达式。前面可能会有“generate ALWAYS”，以使生成的列的性质更显式。

VIRTUAL或STORED关键字指示如何存储列值，这对列的使用有影响:

VIRTUAL:不存储列值，但在读取行时，在任何BEFORE触发器之后计算列值。虚拟列不占用任何存储空间。

InnoDB支持在虚拟列上建立二级索引。

STORED:在插入或更新行时计算并存储列值。存储的列确实需要存储空间，并且可以被索引。

虚拟列的允许

允许在一个表中混合使用虚拟列和存储列。

还可以给出其他属性，以指示该列是否被索引，或者可以为空，或者提供注释。

生成的列表达式必须遵守以下规则。如果表达式包含不允许的构造，则会发生错误。

允许使用文字、确定性内置函数和操作符。如果给定表中相同的数据，多个调用独立于连接的用户产生相同的结果，那么一个函数就是确定的。
不确定的函数的示例没有实现此定义:CONNECTION_ID()， CURRENT_USER()， NOW()。

允许按生成的列进行分区。

虚拟列限制使用条件

不允许存储函数和用户定义函数。

不允许使用存储过程和函数参数。

不允许使用变量(系统变量、用户定义变量和存储的程序局部变量)。

子查询是不允许的。

生成的列定义可以引用其他生成的列，但只能引用表定义前面出现的列。生成的列定义可以引用表中的任何基列(非生成的)，无论它的定义是早于还是晚于。

AUTO_INCREMENT属性不能在生成的列定义中使用。

在生成的列定义中，不能将AUTO_INCREMENT列用作基列。

需要注意，不能建立虚拟列和真实列的联合索引。

应用

一、为了实现对json数据中部分数据的索引查询

创建表

create table user(uid int auto_increment,data json,primary key(uid));

构建数据

insert into user values (NULL,'{"name":"wang","address":"shenyang"}');
insert into user values (NULL,'{"name":"zhao","address":"riben"}');

 构建姓名的虚拟列

alter table user add user_name varchar(20) generated always as (data->'$.name');

 构建索引

alter table user add index idx_name(user_name);

使用如下查询语句并不能查询到结果

SELECT * FROM user WHERE user_name='wang';

需使用下面的方可查询到结果。

select * from user where user_name='"wang"';

 可以知道构建的虚拟列中的数据多了 "" ,那么如何去掉呢?    可以这样：

​​​​​​​ALTER TABLE user ADD user_name VARCHAR(20) generated always AS (JSON_UNQUOTE(json_extract(data,'$.name')));

表的结构由于多出了user_name这一虚拟列，再插入别的数据要注意在表后指明插入列（不能给虚拟列插入数据）

insert into user(uid,data) values (NULL,'{"name":"pan","address":"sichuan"}');

二、表达式计算数据

有这么一张表，如下图：其中DISCOUNT表示折扣，-1代表不打折，88表示88折，现在要求加一列，显示折扣后的单价。

ALTER TABLE sheet1 ADD discount_price DECIMAL(9,2) as
 (IF(DISCOUNT!=-1,PRICE*DISCOUNT/100,PRICE)) VIRTUAL;

参考

https://www.cnblogs.com/raichen/p/5227449.html

https://zhuanlan.zhihu.com/p/148873608

https://blog.csdn.net/xudajian/article/details/113118060

https://dev.mysql.com/doc/refman/8.0/en/generated-column-index-optimizations.html

https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index