mysql 虚拟列，可以简化查询逻辑、确保数据一致性
时间：2025-09-26 17:01
MySQL 虚拟列（Generated Column）是一种特殊的列，它的值不是由用户直接插入或更新的，而是根据表中其他列的计算结果自动生成的。虚拟列可以简化查询逻辑、确保数据一致性，并提高查询性能。
虚拟列的两种类型
-
存储型（STORED）：
- 计算结果会被物理存储在磁盘上
- 更新时会自动重新计算
- 可以创建索引
- 占用存储空间
-
虚拟型（VIRTUAL）：
- 不存储实际数据，只在查询时计算
- 不占用存储空间
- MySQL 8.0.13 及以上版本支持为虚拟型虚拟列创建索引
- 默认类型
基本语法
创建表时定义虚拟列：
sql
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
generated_column data_type GENERATED ALWAYS AS (expression)
[VIRTUAL | STORED] [UNIQUE] [COMMENT 'comment']
);
示例
- 创建包含虚拟列的表：
sql
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
price DECIMAL(10,2) NOT NULL,
tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.08,
-- 计算含税价格的虚拟列
price_with_tax DECIMAL(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED,
-- 计算价格等级的虚拟列
price_level VARCHAR(10) GENERATED ALWAYS AS (
CASE
WHEN price < 10 THEN 'cheap'
WHEN price < 100 THEN 'medium'
ELSE 'expensive'
END
) VIRTUAL
);
- 向表中插入数据（不需要指定虚拟列的值）：
sql
INSERT INTO products (price, tax_rate) VALUES (50, 0.1);
- 查询数据（可以像普通列一样使用虚拟列）：
sql
SELECT id, price, tax_rate, price_with_tax, price_level FROM products;
虚拟列的使用场景
- 数据转换和格式化（如将日期转换为特定格式）
- 计算派生值（如总价、折扣后价格）
- 数据分类（如根据数值范围划分等级）
- 简化复杂查询条件
- 作为索引列提高查询性能
注意事项
- 虚拟列的表达式只能使用表中已存在的列
- 表达式不能使用其他虚拟列（MySQL 8.0.22+ 允许使用）
- 表达式不能包含子查询、存储函数或用户变量
- ALTER TABLE 可以添加、修改或删除虚拟列
- 虚拟列可以像普通列一样用于 SELECT、WHERE、ORDER BY 等子句
使用虚拟列可以使数据库设计更加灵活，同时保持数据的一致性和准确性，是 MySQL 中一个非常实用的功能。
