什么是mysql的虚拟列以及应用实例

简介

MySQL的虚拟列是一种新特性，可以在查询时直接使用虚拟列(代替视图) 存储虚拟列可以用作实例化缓存，以用于动态计算成本高昂的复杂条件。虚拟列可以在创建表时定义，并在查询时像普通列一样使用。虚拟列通常用于计算或汇总数据，例如计算总价、计算平均值等。

定义MySQL虚拟列(generated-columns)是MySQL 5.7加入的新特性。从名字来讲，“生成的字段”，并不是主动插入的值。MySQL的文档，是这么解释虚拟列的：There are two kinds of Generated Columns: virtual (default) and store-generated (non-default). Virtual columns are generated on the fly as part of an INSERT or UPDATE statement, and they are not stored in the table. Stored-generated columns are calculated based on the values of other columns and are stored in the table.

虚拟列的分类

在MySQL中，虚拟列有两种类型：STORED和VIRTUAL，分别表示存储列和虚拟列。

• 存储列（STORED）：表示虚拟列的值实际上是被计算出来并存储在表中的，可以在查询时直接使用。存储列的优点是它们可以被索引，这意味着它们可以更快地被搜索，但是它们也会占用更多的存储空间。存储列的值在数据插入或更新时动态计算，并存储在表中。

其中的公式为：

alter table table_name
    modify column_name int as (表达式) stored;

AS (表达式)：定义该列为虚拟列，并指定其值的计算方式。表达式是一个SQL表达式，用于计算虚拟列的值。

STORED：表示该虚拟列的值会被计算出来并存储在表中。

• 虚拟列（VIRTUAL）：表示虚拟列的值不会存储在表中，而是在查询时动态计算。虚拟列的优点是它们不会占用额外的存储空间，但是它们不能被索引，因此在大型表中使用虚拟列时需要谨慎，否则可能会影响查询性能。 在上述SQL语句中，使用了STORED关键字表示该虚拟列的值会被计算出来并存储在表中。因此，每次查询表时，MySQL都会返回存储在表中的虚拟列值，而不是在查询时计算虚拟列的值。

其中公式为：

alter table table_name
    add column_name int as (表达式);

AS (表达式)：定义该列为虚拟列，并指定其值的计算方式。表达式是一个SQL表达式，用于计算虚拟列的值。

虚拟列使用示例

1.创建数据库mydb，创建用户表并插入数据

create database mydb;
use mydb;
create table t_user
(
    id          bigint auto_increment
        primary key,
    name        varchar(255)  null,
    age         int default 0 null
);
INSERT INTO mydb.t_user (name, age) VALUES ('小明', 12)
INSERT INTO mydb.t_user (name, age) VALUES ('小红', 18)

插入两条数据。

2.创建虚拟存储列

alter table mydb.t_user
    add store_age int as (age + 9) stored;

创建了store_age的虚拟存储列，以age为标准加9.

展示效果

id	name	age	store_age
1	小明	12	21
2	小红	18	27

可以看到store_age的数据都是在age的基础上加了9

3.创建虚拟列

alter table mydb.t_user
    add virtual_age int as (age + 87);

创建了virtual_age的虚拟列，以age为标准加87.

展示效果

id	name	age	store_age	virtual_age
1	小明	12	21	99
2	小红	18	27	105

可以看到virtual_age的数据都是在age的基础上加了87.