MySQL 5.7 虚拟列 （virtual columns）

本文主要是介绍MySQL 5.7 虚拟列 （virtual columns），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

目录

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

这篇关于MySQL 5.7 虚拟列 （virtual columns）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！