当前位置：首页 > news >正文

mysql 虚拟列，可以简化 SQL 逻辑、提升查询效率

news 2025/9/19 16:30:32

MySQL 5.7 及以上版本支持生成列（Generated Columns），也称为 “虚拟列”。虚拟列的值不是手动插入的，而是由表中其他列的值通过表达式计算得出，类似于 “自动计算的字段”。
虚拟列的两种类型
虚拟生成列（Virtual Generated Columns）
不实际存储在磁盘上，每次查询时动态计算
不占用存储空间，但查询时会消耗计算资源
存储生成列（Stored Generated Columns）
计算结果会存储在磁盘上，与普通列一样占用空间
查询时无需重新计算，性能更好，但更新依赖列时会额外消耗资源
基本语法
创建表时定义虚拟列：

sql
CREATE TABLE 表名 (
普通列定义,
虚拟列名数据类型 GENERATED ALWAYS AS (表达式) [VIRTUAL|STORED]
);

GENERATED ALWAYS AS (表达式)：定义计算规则
VIRTUAL：虚拟类型（默认，可省略）
STORED：存储类型，需显式指定
示例

基础用法（计算商品总价）
sql
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
price DECIMAL(10,2) NOT NULL, -- 单价
quantity INT NOT NULL, -- 数量
total DECIMAL(12,2) GENERATED ALWAYS AS (price * quantity) STORED -- 总价（存储型）
);

插入数据时，无需指定虚拟列的值：

sql
INSERT INTO products (price, quantity) VALUES (99.99, 2); -- 自动计算 total = 99.99*2 = 199.98

查询结果：

sql
SELECT * FROM products;
-- 结果：id=1, price=99.99, quantity=2, total=199.98
2. 虚拟列结合字符串函数（提取邮箱域名）
sql
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100) NOT NULL,
email_domain VARCHAR(50) GENERATED ALWAYS AS (
SUBSTRING_INDEX(email, '@', -1) -- 从@后截取域名
) VIRTUAL -- 虚拟类型，不存储
);

查询时自动提取域名：

sql
INSERT INTO users (email) VALUES ('user@example.com'), ('admin@gmail.com');

SELECT email, email_domain FROM users;
-- 结果：
-- email | email_domain
-- user@example.com | example.com
-- admin@gmail.com | gmail.com
3. 修改表添加虚拟列
sql
-- 给已存在的表添加虚拟列
ALTER TABLE orders
ADD COLUMN tax DECIMAL(10,2) GENERATED ALWAYS AS (total * 0.1) STORED; -- 计算税费（10%）
虚拟列的使用场景
简化查询：将复杂计算逻辑固化在表结构中，避免每次查询重复写表达式
创建索引：虚拟列支持创建索引（尤其是存储型），可加速基于计算结果的查询
sql
-- 给邮箱域名虚拟列创建索引
CREATE INDEX idx_email_domain ON users(email_domain);

数据校验：结合约束实现复杂验证（如 CHECK 约束）
sql
CREATE TABLE temperatures (
id INT PRIMARY KEY,
celsius DECIMAL(5,2) NOT NULL,
fahrenheit DECIMAL(5,2) GENERATED ALWAYS AS (celsius * 9/5 + 32) VIRTUAL,
CHECK (celsius BETWEEN -273.15 AND 1000) -- 校验摄氏度范围
);

注意事项
虚拟列的表达式只能依赖本表的其他列，不能使用其他表的列或函数（如 NOW()、RAND() 等）
虚拟列的值会随依赖列的更新自动更新，无需手动维护
存储型虚拟列会增加写入和更新的开销，需平衡存储空间和查询性能
虚拟列不能作为 AUTO_INCREMENT 列，也不能设置默认值（值由表达式决定）

以上就是关于mysql 虚拟列的介绍。还有一款非常便捷的MYSQL导出、导入备份工具也运用的很不错，“80KM-mysql备份工具”。可定时备份、异地备份，MYSQL导出导入。可本地连接LINUX里的MYSQL，简单便捷。