一文搞懂MySQL虚拟列用法、选型与避坑

做MySQL开发的同学，有没有遇到过这样的场景：

查询时反复写相同的计算逻辑（比如订单单价×数量算小计、手机号截取后4位），不仅代码冗余，还可能因为每个人写的逻辑不一致导致bug；想给这些计算结果建索引加速查询，却不知道从何下手。

其实，MySQL的虚拟列就能完美解决这个问题。它相当于给表加了一个“自动计算的公式列”，无需手动维护，还能配合索引提升查询效率。

今天就结合实操示例介绍虚拟列的两种类型（STORED/VIRTUAL）、用法、差异，还有新手最容易踩的坑。

一、什么是MySQL虚拟列？

虚拟列，顾名思义，就是：该列的值不是手动插入，而是由表内其他列计算生成的列。

举个例子：就像Excel里的公式列，你只要输入“单价”和“数量”，它会自动算出“小计”，你修改单价或数量，小计会实时同步更新。MySQL虚拟列的逻辑完全一样，且具有如下主要特点：

• 无需手动插入、更新值，依赖的列变化时，虚拟列自动同步
• 表达式必须是确定性函数（输入相同，结果必相同，比如拼接、加减乘除，不能用now()之类的动态函数，后面会重点说）
• 分两种类型：STORED（存储型）和VIRTUAL（虚拟型），用法和场景完全不同

二、实操案例

下面我们就通过创建表、表中加列的方式来介绍虚拟列的用法。

1. 创建表，且表中包含STORED类型的虚拟列

mysql> CREATE TABLE order_item (
    ->     id INT PRIMARY KEY AUTO_INCREMENT,
    ->     order_id INT NOT NULL COMMENT '订单ID',
    ->     product_id INT NOT NULL COMMENT '商品ID',
    ->     price DECIMAL(10,2) NOT NULL COMMENT '商品单价（元）',
    ->     quantity INT NOT NULL COMMENT '购买数量',
    ->     -- STORED虚拟列：小计金额 = 单价 × 数量（确定性运算，适合存储）
    ->     subtotal DECIMAL(10,2) 
    ->         GENERATED ALWAYS AS (price * quantity) 
    ->         STORED COMMENT '小计金额（物理存储）'
    -> );
Query OK, 0 rows affected (0.22 sec)

在插入数据

mysql> INSERT INTO order_item (order_id, product_id, price, quantity)
    -> VALUES 
    -> (1001, 5001, 99.90, 2),   
    -> (1001, 5002, 159.00, 1), 
    -> (1002, 5003, 29.90, 5);   
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

此时查看结果如下：

可见，subtotal列的内容自动进行了计算。

2. 在现有表上加VIRTUAL类型的列

mysql> ALTER TABLE   order_item ADD COLUMN    tax_subtotal DECIMAL(10,2) 
    -> GENERATED ALWAYS AS (price * quantity * 1.13)  VIRTUAL COMMENT '含13%税小计（实时计算）';
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

此时在查询表的内容，结果如下：

可见，两种类型的虚拟列都正常根据规则进行展示。

3. 更新依赖列，看两种虚拟列的同步逻辑

修改的ID=1的商品单价，观察两个虚拟列的变化（都会同步更新，但底层逻辑不同）

mysql> UPDATE order_item SET price = 89.90 WHERE id = 1;
Query OK, 1 row affected, 2 warnings (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 2

两者都自动同步，无需手动修改。

三、STORED vs VIRTUAL区别

很多同学分不清两种类型的区别，其实核心差异就3点：存储方式、性能开销、灵活性。结合上面的示例，一张表讲明白：

四、常见踩坑点

虚拟列用法不难，但很容易踩坑，尤其是这3个，结合之前的踩坑经历给大家提醒。

1.使用非确定性函数

虚拟列的表达式必须是「确定性函数」，比如CONCAT、RIGHT、加减乘除，而CURDATE()、NOW()、RAND()等非确定性函数（输入相同，结果可能变）会直接报错。

比如之前有人想做一个“自动计算年龄”的虚拟列，用了CURDATE()，直接报错：

-- 错误示例：含非确定性函数CURDATE()
age INT GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birth_date, CURDATE())) STORED;
-- 报错：ERROR 3763 (HY000): Expression of generated column 'age' contains a disallowed function: curdate.

解决方案：年龄这类随时间变化的值，优先在查询时实时计算（而非虚拟列）。

2. 虚拟列依赖其他表的列

虚拟列的计算逻辑，只能依赖「本表的其他列」，不能使用子查询、存储过程，也不能引用其他表的列，否则会报错。

3. VIRTUAL类型不能当主键/唯一键

MySQL限制VIRTUAL类型的虚拟列，不能作为主键的唯一组成部分；但STORED类型可以（和普通列无区别）

结合以往经验，给大家一个简单直接的选型原则：

• 优先选VIRTUAL类型：大部分场景下，VIRTUAL足够用，不占磁盘、灵活性高，更新时无额外开销，MySQL8.0+还支持给VIRTUAL列建索引，加速查询
• 仅在以下场景用STORED：计算逻辑非常复杂（比如多列嵌套运算）、查询频率极高（比如每秒上千次查询）、更新频率极低（比如商品基础信息）
• 避免过度使用：虚拟列虽好，但也会增加查询时的计算开销（VIRTUAL）或存储开销（STORED），没必要的计算逻辑，不要强行用虚拟列

五、 总结

MySQL虚拟列的核心价值，就是简化重复计算、提升查询效率。STORED类型是空间换时间，VIRTUAL类型是时间换空间。

计算逻辑是确定性的，且需要重复使用，可以考虑用虚拟列。且优先选VIRTUAL，特殊场景用STORED；避开非确定性函数的坑；如非必要，不要强行用虚拟列。

你在项目中用过虚拟列吗？有没有遇到过其他踩坑场景？欢迎留言交流～