MySQL 虚拟列详解

在 MySQL 中，虚拟列（也称为生成列或计算列）是从一个或多个其他列派生的列，但不实际存储在数据库表中。虚拟列在数据库中仅在查询时计算，这对于提高查询效率非常有用，尤其是在需要对数据进行复杂计算或格式化时。

比如数据表中有单价：price 和数量：count，要计算总价：total_amount。以前我都是在程序端通过代码计算总价，这样还要多写一段代码。使用 MySQL 虚拟列就非常简单了，MySQL 会在查询时自动计算并返回结果。

创建虚拟列

要创建一个虚拟列，你可以在创建表时或者在现有表上使用 ALTER TABLE 语句。

创建表时添加虚拟列

ALTER TABLE your_table_name
ADD COLUMN virtual_column_name INT AS (expression) STORED;

在这个例子中，full_name 是一个虚拟列，它通过连接 first_name 和 last_name 字段来生成。注意，MySQL 5.7 及之前的版本不支持虚拟列，但从 MySQL 8.0 开始支持生成列（包括虚拟列）。

在现有表上添加虚拟列

-- 假设原来的表达式是 (column1 + column2)
-- 新的表达式是 (column1 * column2)
 
-- 首先删除旧的虚拟列（如果它实际上是一个真实的物理列，这将不起作用）
ALTER TABLE your_table_name DROP COLUMN virtual_column_name;
 
-- 然后添加新的虚拟列
ALTER TABLE your_table_name
ADD COLUMN total_amount INT AS (price * count) STORED;

虚拟列的类型

在 MySQL 8.0 中，有两种类型的生成列：

  1. STORED：存储生成的列。这意味着生成的列值会被存储在磁盘上，类似于普通列。但是，它们仍然只在查询时计算。
  2. VIRTUAL：虚拟生成的列。这类列不会在磁盘上存储其值，而是在查询时根据基础列动态计算得出。这对于节省存储空间和提高查询效率非常有用。

创建虚拟列的示例（使用 VIRTUAL）

ALTERTABLE employees ADD full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL;

使用虚拟列进行查询

无论你是使用 STORED 还是 VIRTUAL 类型的生成列，你都可以像查询普通列一样查询它们：

SELECT id, full_name FROM employees;

注意事项

  • 存储空间：虽然虚拟列不占用额外的存储空间（除了索引的开销），但在某些情况下（例如使用 STORED 类型），它们可能会占用额外的存储空间以保存计算后的值。对于频繁更新的数据，这可能会导致性能问题。
  • 索引：你可以为生成的列创建索引，这在某些情况下可以提高查询性能。但是，对于 VIRTUAL 类型的生成列，索引实际上是基于其基础列的，而不是直接基于生成列本身。这意味着查询优化器可能会选择不同的执行计划。
  • 兼容性：确保你的 MySQL 版本支持生成列（MySQL 8.0 及更高版本）。如果你使用的是较旧的版本，你将无法使用这一特性。

通过合理使用虚拟列，你可以有效地减少数据冗余并提高数据处理的灵活性。

相关文章