MySQL 中的虚拟列 Generated Column

什么是虚拟列

虚拟列（Generated Column）是 MySQL 5.7 版本引入的一项重要特性。它允许你在表中定义一个列，其值并非直接存储，而是根据同表中其他列的值通过表达式动态计算得出。虚拟列就像一个“公式”，当你查询这列时，MySQL 会实时执行表达式并返回计算结果。

两种类型：VIRTUAL 与 STORED

虚拟列分为两种存储方式，在定义时必须明确指定：

如果没有显式指定类型，MySQL 默认创建的是 VIRTUAL 列。

创建虚拟列

创建表时直接定义虚拟列的语法如下：

column_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT 'comment']

• GENERATED ALWAYS 关键字可以省略，现代写法通常直接使用 AS (expression)。
• expression 必须使用表中其他列，不能使用子查询、存储函数、用户变量等，且表达式必须是确定性的（给定相同输入永远返回相同输出）。
• 虚拟列可以定义注释、唯一约束，但不能设置默认值（DEFAULT 对它无意义）。

基础示例

假设我们有一个 products 表，包含不含税价格和税率，需要自动计算含税价格。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    tax_rate DECIMAL(4,2),
    total_price DECIMAL(10,2) GENERATED ALWAYS AS (price + (price * tax_rate / 100)) STORED
);

插入数据时，无需也无法为 total_price 提供值：

INSERT INTO products (name, price, tax_rate) VALUES ('Book', 20.00, 5.00);
SELECT * FROM products;

结果中 total_price 会自动显示 21.00。

添加虚拟列到已有表

使用 ALTER TABLE 可以为现有表增加虚拟列：

ALTER TABLE products ADD COLUMN
    discounted_price DECIMAL(10,2) AS (price * 0.9) VIRTUAL;

注意：如果表中已有大量数据，添加 STORED 虚拟列时，MySQL 需要立即计算并存储所有行的值，可能会锁表，大表操作时需谨慎。

虚拟列的使用场景

1. 简化复杂查询

无需在每次查询中重复写计算逻辑。例如，从 users 表中提取姓名全称：

ALTER TABLE users ADD COLUMN full_name VARCHAR(201) AS (CONCAT(first_name, ' ', last_name)) STORED;

之后直接 SELECT full_name FROM users。

2. 配合 JSON 数据

虚拟列可以提取 JSON 字段中的值并建立索引，极大提升 JSON 查询性能。MySQL 5.7 起支持对虚拟列的二级索引。

CREATE TABLE logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data JSON,
    user_id INT AS (data->>'$.user_id') STORED,
    INDEX idx_user_id (user_id)
);

之后 SELECT * FROM logs WHERE user_id = 100 便能高效利用索引。

3. 分区表键

如果需要对表进行分区，但直接依赖于计算值，可以使用虚拟列。虚拟列可以作为分区键（甚至 VIRTUAL 列也支持，但有些限制）。

CREATE TABLE orders (
    id INT,
    created_date DATE,
    order_year INT AS (YEAR(created_date)) VIRTUAL
) PARTITION BY RANGE (order_year) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

4. 为函数索引提供支持

在 MySQL 中无法直接对函数表达式创建索引，但可以通过创建一个 VIRTUAL 虚拟列再为该虚拟列创建索引来间接实现，优化函数包裹列的查询。

ALTER TABLE products ADD COLUMN upper_name VARCHAR(100) AS (UPPER(name)) VIRTUAL;
CREATE INDEX idx_upper_name ON products(upper_name);

原本 SELECT * FROM products WHERE UPPER(name) = 'BOOK' 无法使用索引，现在可以通过查询 upper_name = 'BOOK' 来利用索引。

核心注意事项

• 表达式限制：不允许使用非确定性函数（如 NOW(), UUID()）、子查询、存储函数、用户自定义变量。只能引用本表的列。
• 不能更新虚拟列：无论是 VIRTUAL 还是 STORED，都无法在 UPDATE 语句中直接为虚拟列赋值。错误示例：UPDATE products SET total_price = 50; 会报错。
• 插入时可省略：插入时必须省略虚拟列，或使用 DEFAULT，但不能提供显式值。
• STORED 列占用空间：如果选用 STORED，会增加磁盘 I/O 和备份大小，但换取了读性能。
• 索引限制：VIRTUAL 列上的二级索引在 MySQL 5.7 中存在限制（不支持前缀索引、全文索引等），大部分限制在 8.0 版本中已放宽。生产环境建议确认版本行为。
• 数据类型推导：虚拟列的数据类型必须与表达式返回类型兼容，MySQL 会进行必要的类型转换，但为了安全应显式指定合适的数据类型。
• 与触发器区别：虚拟列完全由数据库自动维护，无需编写和管理触发器代码，不会产生触发器带来的维护开销和陷阱。

查看表的虚拟列

可以通过 SHOW CREATE TABLE 或查询 INFORMATION_SCHEMA 查看虚拟列定义。

SHOW CREATE TABLE products;

SELECT COLUMN_NAME, DATA_TYPE, GENERATION_EXPRESSION, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'products'
  AND GENERATION_EXPRESSION IS NOT NULL;

EXTRA 列会显示 VIRTUAL GENERATED 或 STORED GENERATED。

实践建议

• 优先使用 VIRTUAL：除非有频繁查询且对读性能要求极高，否则默认的 VIRTUAL 列更节省空间，写入性能也更好。
• 利用虚拟列优化 JSON：这是最典型的应用场景，务必结合 STORED + 索引来加速 JSON 查询。
• 分区时首选 VIRTUAL：分区键通常不需要存储，使用虚拟列可以节省空间且满足分区需求。
• 不要滥用：避免在一个表中创建几十个虚拟列，尤其当它们之间相互引用时，可能导致查询计划复杂化。

总结

MySQL 虚拟列提供了一种优雅的方式来自动计算和存储派生数据，避免了重复编写 SQL 逻辑，同时在 JSON 索引、分区优化等场景下有着不可替代的作用。通过合理选择 VIRTUAL 或 STORED，可以在空间与性能之间找到最佳平衡。初学者应从简单的字符串拼接、数学运算开始，逐步掌握这一强大特性。