概念

从 MySQL5.7 开始，引入了一项名为"虚拟字段"（Virtual Columns）的功能。虚拟字段允许你在表中创建一个列，该列的值是通过一个表达式计算得出的，而不是实际存储在表中的数据。这个表达式可以使用表中其他列的值，甚至是一些内置函数和操作符来计算。

虚拟字段具有以下基本概念：

计算表达式（Expression）： 虚拟字段的值是通过一个计算表达式生成的，这个表达式可以由函数、运算符、常量和表中其他列的值组成。
不存储实际数据： 虚拟字段并不实际存储数据，它的值是根据计算表达式在查询时动态计算出来的。这可以节省存储空间，同时允许你从已有的列派生出更多信息，而无需实际存储这些信息，（VIRTUAL存储模式）
语法： 创建虚拟字段的语法如下：（VIRTUAL存储模式）

ALTER TABLE table_name
ADD COLUMN virtual_column_name [column_type] GENERATED ALWAYS AS (expression) [VIRTUAL];

table_name 是你要添加虚拟字段的表名。
virtual_column_name 是你给虚拟字段起的名字。
column_type 是虚拟字段的数据类型。
expression 是计算虚拟字段值的表达式。
GENERATED ALWAYS： 这是一个选项，表示虚拟字段的值是始终自动生成的，而不需要手动更新。
VIRTUAL： 这也是一个选项，表示这是一个虚拟字段。

虚拟字段的一个常见用途是根据表中其他列的值生成某种衍生数据，比如计算合计、拼接字符串、进行数学运算等等。它们在查询时的计算是基于存储在表中的实际数据，因此在性能方面需要考虑查询的复杂性和效率。

示例：

1、先来一个订单表：

CREATE TABLE `orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `price` decimal(10,2) unsigned DEFAULT NULL COMMENT '单价',
  `quantity` int unsigned NOT NULL DEFAULT '0' COMMENT '数量',
  `total_price` decimal(10,2) GENERATED ALWAYS AS ((`price` * `quantity`)) VIRTUAL COMMENT '总价',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单表';

total_price 为虚拟字段。

2、插入一条数据，SQL:

INSERT INTO `orders` (`price`, `quantity`) VALUES (5.00, 2);

注意：仅插入了price、quantity 字段，没有插入total_price字段。

3、查看数据：

select * from orders;

如图可见，自动计算填充了total_price字段的值，以提供的`price` * `quantity`表达式为基准，准确生成了值。

需要注意的是，虚拟字段在一些情况下可能不适用，例如对于需要频繁更新的数据，或者涉及到复杂计算的场景。在这些情况下，使用实际存储数据的列可能更为合适。

两种存储方式

MySQL 在处理虚拟列存储问题时，提供了两种不同的存储方式，这些方式可以在创建虚拟列时进行选择。这两种方式分别是：存储和非存储（Stored and Not Stored）。

一、存储（Stored）： 如果选择存储方式，虚拟列的计算结果会被实际存储在表中，类似于普通的物理列。这样，在查询时不需要再重新计算虚拟列的值，因为它们已经预先计算并存储在表中。这可以提高查询性能，尤其是在大数据表中。

创建存储虚拟列的示例：

ALTER TABLE table_name
ADD COLUMN virtual_column_name [column_type] GENERATED ALWAYS AS (expression) STORED;

二、非存储（Not Stored）： 如果选择非存储方式，虚拟列的计算结果不会被实际存储在表中，而是在查询时根据表达式计算得出。这可能会在插入或更新数据时带来性能上的优势，因为不需要实时计算虚拟列的值。然而，查询时的性能可能会受到影响，因为需要在每次查询时计算虚拟列的值。

创建非存储虚拟列的示例：

ALTER TABLE table_name
ADD COLUMN virtual_column_name [column_type] GENERATED ALWAYS AS (expression) VIRTUAL;

选择存储方式还是非存储方式取决于你的具体需求和查询模式。如果查询频率高但数据变化不频繁，存储方式可能更有利于提高查询性能。如果数据变化频繁，而且查询性能相对次要，非存储方式可能更合适。

实际应用场景使用

虚拟字段在很多实际应用场景中都能展现出强大的威力。以下是一些常见的使用案例：

计算字段： 虚拟字段可以用于计算各种数值，如价格总额、百分比等，使得查询结果更加直观。
全文搜索： 通过虚拟字段，你可以将文本字段中的数据进行格式化或提取关键信息，从而在全文搜索时提高匹配效率。
数据转换： 虚拟字段可以将不同数据类型之间进行转换，例如日期格式转换、单位转换等。
数据约束： 你可以利用虚拟字段来实现数据约束，确保某些计算结果或条件满足特定要求。

优劣点比较

虚拟字段作为新特性，当然有着自己的优势和劣势：

优点：

灵活性： 虚拟字段使得复杂的计算变得更加简单，提供了更灵活的数据处理方式。
性能优化： 虚拟字段不占用物理存储空间，不会增加数据表的大小，从而不会影响查询性能。
数据一致性： 由于虚拟字段是根据公式生成的，它能够确保计算结果始终与基础数据保持一致。

劣势：

计算开销： 虚拟字段的计算是动态进行的，可能会在某些情况下增加查询的计算开销。
不可索引： 虚拟字段通常不支持索引，这可能会限制在某些查询条件下的优化。

注意事项

在使用虚拟字段时，有一些需要注意的地方：

性能测试： 在引入虚拟字段之前，务必进行性能测试，确保它不会对查询性能产生不良影响。
表设计： 虚拟字段应该在表设计初期就考虑，避免后期需要对表结构进行大幅度的修改。
数据类型： 虚拟字段的数据类型应该与计算结果一致，避免因为类型不匹配而出现意外的结果。

总结：

json列+虚拟列配合提取字段比较方便，神仙组合。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_data JSON,
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(order_data, '$.total_price')) AS DECIMAL(10, 2))) VIRTUAL
);

INSERT INTO orders (order_id, order_data) VALUES
(1, '{"total_price": 150.00, "items": [{"product": "Product A", "quantity": 2}, {"product": "Product B", "quantity": 3}]}'),
(2, '{"total_price": 75.00, "items": [{"product": "Product C", "quantity": 1}]}');

查询结果：

我为人人，人人为我，美美与共，天下大同。

玖叶教程网

前端编程开发入门

MySQL:虚拟字段

概念

示例：

两种存储方式

实际应用场景使用

优劣点比较

优点：

劣势：

注意事项

总结：