MySQL虚拟列的详细讲解

MySQL虚拟列的详细讲解

MySQL 5.7引入了Generated Column，所谓Cenerated 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。

语法：

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

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

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

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

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

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

 

虚拟列的允许：

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

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

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

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

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

虚拟列限制使用条件：

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

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

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

子查询是不允许的。

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

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

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

在MySQL 中，如果表达式求值导致截断或函数输入错误，CREATE TABLE语句将以错误结束，DDL操作将被拒绝。

对于CREATE TABLE ... LIKE,，目标表保留从原始表生成的列信息。

CREATE TABLE triangle1like triangle

SHOW CREATE TABLE triangle1

CREATE TABLE `triangle1` (
  `sidea` INT(11) DEFAULT NULL,
  `sideb` INT(11) DEFAULT NULL,
  `sidec` INT(11) GENERATED ALWAYS AS (SQRT(((`sidea` * `sidea`) + (`sideb` * `sideb`)))) VIRTUAL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

;

可以看到sidec还保留着虚拟列。

对于 CREATE TABLE ... SELECT时，目标表不保留关于所选表中的列是否为生成列的信息。语句的选择部分不能为目标表中生成的列赋值。

如下面应用中的triangle，用它来创建表：

CREATE TABLE triangle1 AS SELECT * FROM triangle；

SHOW CREATE TABLE triangle2

CREATE TABLE `triangle2` (
  `sidea` INT(11) DEFAULT NULL,
  `sideb` INT(11) DEFAULT NULL,
  `sidec` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

可以看到sidec已经不是虚拟列。

应用：

CREATE TABLE triangle 
(   sidea INT,
    sideb INT,
    sidec INT AS (SQRT(sidea * sidea + sideb * sideb))
);

INSERT INTO triangle
(sidea, sideb)
VALUES(1,1),(3,4),(6,8)
;

 

生成的列有以下几个用例:

1. 虚拟生成的列可以作为简化和统一查询的一种方法。可以将复杂的条件定义为生成的列，并从表上的多个查询中引用，以确保所有查询都使用完全相同的条件。

2. 存储的生成的列可以作为一个物化缓存，用于动态计算成本很高的复杂条件。

3. 生成的列可以模拟函数索引:使用生成的列定义函数表达式并对其建立索引。这在处理不能直接索引的类型的列时很有用，比如JSON列;

4. 对于存储生成的列，这种方法的缺点是值需要存储两次;一次作为生成列的值，一次作为索引的值。

5. 如果生成的列被索引，优化器将识别与列定义匹配的查询表达式，并在查询执行期间适当地使用来自列的索引，即使查询没有直接按名称引用列。

 

虚拟列优化例子：

在现实开发中，很多程序员为了方便，喜欢在where条件中应用函数，这样导致该列的索引未被使用，影响查询效率。

SELECT * FROM test_data
 WHERE  DATE_FORMAT(static_date, '%Y-%m-%d') >=  DATE_FORMAT('2021-01-22', '%Y-%m-%d')   
   AND DATE_FORMAT(static_date, '%Y-%m-%d') <=  DATE_FORMAT('2021-01-24', '%Y-%m-%d') 

；

这个查询要0.997秒。

查看查询计划，没有用到索引，查询比较慢。

EXPLAIN
SELECT * FROM test_data
 WHERE  static_date >=  DATE_FORMAT('2021-01-22', '%Y-%m-%d')   
   AND static_date<=  DATE_FORMAT('2021-01-24', '%Y-%m-%d')   

在原表增加个虚拟列和索引：

ALTER TABLE `test_data` ADD vr_time VARCHAR(10) AS (DATE_FORMAT(static_date, '%Y-%m-%d')) STORED, ADD KEY idx_vr_time (vr_time);

再执行上面的查询，可以看到查询变快了很多。

看看查询计划，用到了static_date列的索引。这类似于oracle的函数索引。

 

使用虚拟列为Json数据类型创建索引

   
CREATE TABLE t1
(   id INT AUTO_INCREMENT,
    full_name json,
    PRIMARY KEY(id)
)ENGINE = INNODB;   
   
插入数据

INSERT INTO t1 VALUES (NULL,'{"first_name":"Wukong","last_name":"Sun"}');
INSERT INTO t1 VALUES (NULL,'{"first_name":"Bajie","last_name":"Zhu"}');   
   
   
SELECT full_name FROM t1;   

构建姓名的虚拟列
方法1：
ALTER TABLE t1 ADD v_name VARCHAR(20) generated always AS (JSON_EXTRACT(full_name,'$.first_name'));
方法2：
ALTER TABLE t1 ADD v_name VARCHAR(20) generated always AS (full_name->'$.first_name');

构建索引
ALTER TABLE t1 ADD INDEX idx_firstname(v_name);

使用如下查询语句

SELECT * FROM t1 WHERE v_name='Wukong';

并不能查询到结果，需使用

SELECT * FROM t1 WHERE v_name='"Wukong"';

方可查询到结果。

 可以知道构建的虚拟列中的数据多了 "" ,那么如何去掉呢?    可以这样：ALTER TABLE t1 ADD v_name VARCHAR(20) generated always AS (JSON_UNQUOTE(json_extract(full_name,'$.first_name')));

 

构建last_name的虚拟列并创建索引
构建虚拟列：

ALTER TABLE t1 ADD v_lastname VARCHAR(20) generated always AS (JSON_UNQUOTE(full_name->'$.last_name'));

创建索引：

ALTER TABLE t1 ADD INDEX idx_fullname(v_name,v_lastname);

如此查询语句可以正常使用索引，查询与普通表无异。

此时的表结构由于多了v_name和v_address这两个虚拟列，再插入别的数据需要指明插入列 (不能给虚拟列插入数据)

INSERT INTO t1(full_name) VALUES ('{"first_name":"Wukong","last_name":"Sun"}');

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