MySQL虚拟列详解

在MySQL中，虚拟列（也称为生成列）是一种特殊类型的表列，它不像普通列直接存储数据，而是根据其他列中的数据动态生成。虚拟列可以基于一个或多个其他列的值进行计算，计算结果即为虚拟列的值。虚拟列可以是持久的也可以是非持久的，它们的主要作用是简化查询，优化性能，以及维持数据一致性。

虚拟列的类型

1. 生成列的基本概念

生成列分为两种类型：存储的生成列和虚拟的生成列。存储的生成列（STORED GENERATED COLUMN）会在物理存储上占用空间，而虚拟的生成列（VIRTUAL GENERATED COLUMN）在每次查询时动态计算，不占用物理存储空间。

2. 存储的生成列

存储的生成列会将计算结果存储在数据库中，这意味着每次对依赖列进行更新后，生成列的值也会相应更新并存储。这种列的优点是加快了查询速度，因为不需要每次查询时都进行计算。但缺点是增加了存储开销，并可能影响插入和更新的性能。

3. 虚拟的生成列

与存储的生成列相对，虚拟的生成列不会存储计算结果，每次查询时都会动态计算。这减少了存储开销，但可能会导致查询性能略有下降，特别是在涉及大量计算的场景中。

虚拟列的应用场景

1. 简化查询

通过在虚拟列中预先定义计算公式，可以简化复杂的SQL查询语句。例如，如果经常需要计算销售总额（商品数量乘以单价），可以直接创建一个虚拟列来存储这个计算结果。

假设有一个销售数据表，其中包含商品价格 (price) 和销售数量 (quantity) 两个列。经常需要计算销售总额，即每一条记录的 price * quantity。如果每次查询都写这个计算式，不仅增加了查询的复杂性，还可能导致性能下降。引入一个虚拟列 total_sales，该列直接存储 price * quantity 的计算结果，可以极大简化查询：

CREATE TABLE sales (
    product_id INT,
    price DECIMAL(10,2),
    quantity INT,
    total_sales DECIMAL(10,2) AS (price * quantity) VIRTUAL
);

现在，每次需要获取销售总额时，只需直接查询 total_sales 列。

2. 优化查询性能

虚拟列特别适合用于索引。虽然MySQL不允许直接对非持久的虚拟列创建索引，但可以对持久的虚拟列创建索引，这样可以显著提高基于这些计算结果的查询速度。

在上述销售表中，如果需要经常根据销售总额进行查询或排序，可以将虚拟列 total_sales 设为持久化，并对其建立索引：

CREATE TABLE sales (
    product_id INT,
    price DECIMAL(10,2),
    quantity INT,
    total_sales DECIMAL(10,2) AS (price * quantity) STORED
);

CREATE INDEX idx_total_sales ON sales(total_sales);

这样，利用索引，基于销售总额的查询和排序操作将显著提高效率，特别是在数据量较大时。

3. 维护数据一致性

虚拟列确保了数据的动态一致性，因为它们的值是基于其他列动态生成的。这有助于在数据库层面维护数据一致性，避免了应用层进行多余的数据处理。

考虑一个员工表，其中有员工的出生日期 (birth_date) 和年龄 (age) 两个列。虚拟列可以用来实时计算年龄，确保年龄数据始终准确反映当前日期相对于出生日期的差异：

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    birth_date DATE,
    age INT AS (TIMESTAMPDIFF(YEAR, birth_date, CURDATE())) VIRTUAL
);

这样，age 列会动态地根据当前日期和出生日期计算员工的年龄，无需手动更新。

创建和使用虚拟列

1. 创建虚拟列

创建虚拟列的语法如下：

CREATE TABLE my_table (
    column_a INT,
    column_b INT,
    total_c INT AS (column_a + column_b) VIRTUAL
);

2. 使用虚拟列

一旦虚拟列被创建，你可以像使用普通列一样使用它们，包括在SELECT语句中引用它们，或者在WHERE和ORDER BY子句中使用它们。

继续使用上面的销售表，查询某个产品的销售记录，包括计算得到的总销售额：

SELECT product_id, price, quantity, total_sales
FROM sales
WHERE product_id = 101;

这里的 total_sales 是虚拟列，但在查询中它的使用与普通列无异。使用虚拟列，使得查询更加直观和简洁。

虚拟列表达式的规则

在MySQL中使用虚拟列时，需要定义一个表达式来指定如何计算列的值。这些表达式需要遵守一些特定的规则和限制，以确保它们能正确地在数据库中执行。以下是创建和使用虚拟列时必须遵循的几个关键规则：

1. 表达式的确定性

虚拟列的表达式必须是“确定性的”，意味着给定相同的输入，表达式必须产生相同的输出。这确保了无论何时何地计算虚拟列，其结果都是一致的。例如，表达式不应该包含任何随机数生成或调用非确定性函数。

2. 参考列的限制

虚拟列的表达式只能使用同一张表中的其他列作为参考。不能引用其他表的列或进行跨表查询。此外，表达式中引用的列必须在虚拟列声明之前在表定义中出现。

3. 子查询的禁止

虚拟列的表达式不能包含子查询。这是因为子查询可能涉及大量数据处理或外部表的数据，这可能会复杂化虚拟列的计算和维护。

4. 存储函数和过程的禁用

虚拟列不能调用任何存储过程或存储函数。这是为了避免虚拟列的值依赖于可能改变的外部环境或数据库状态，保持计算的纯粹性和高效性。

5. 限制使用的数据类型

虚拟列的表达式中使用的数据类型应该是标准的SQL数据类型，并且要确保表达式结果的数据类型与虚拟列定义的数据类型兼容。例如，如果虚拟列被定义为整数类型，那么表达式也应该产生整数类型的结果。

6. 性能考量

虽然虚拟列不存储物理数据，但复杂的计算表达式可能会在查询时增加计算负担。建议使用尽可能简单的表达式，特别是对于频繁查询的虚拟列。

遵守这些规则可以帮助开发者有效地利用MySQL的虚拟列功能，同时保持数据库的性能和一致性。在设计数据库和查询时，应谨慎选择是否使用虚拟列，以及如何定义它们的计算表达式。