MySQL虚拟列的具体使用

目录

• 虚拟列的类型
• 1. 生成列的基本概念
• 2. 存储的生成列
• 3. 虚拟的生成列
• 虚拟列的应用场景
• 1. 简化查询
• 2. 优化查询性能
• 3. 维护数据一致性
• 创建和使用虚拟列
• 1. 创建虚拟列
• 2. 使用虚拟列
• 虚拟列表达式的规则
• 1. 表达编程式的确定性
• 2. 参考列的限制
• 3. 子查询的禁止
• 4. 存储函数和过程的禁用
• 5. 限制使用的数据类型
• 6. 性能考量
• 参考链接

在mysql中，虚拟列（也称为生成列）是一种特殊类型的表列，它不像普通列直接存储数据，而是根据其他列中的数据动态生成。虚拟列可以基于一个或多个其他列的值进行计算，计算结果即为虚拟列的值。虚拟列可以是持久的也可以是非持久的，它们的主要作用是简化查询，优化性能，以及维持数据一致性。

虚拟列的类型

1. 生成列的基本概念

生成列分为两种类型：存储的生成列和虚拟的生成列。存储的生成列（STORED GENERATED COLUMN）会在物理存储上占用空间，而虚拟的生成列（VIRTUAL GENERATED COLUMN）在每次查询时动态计算，不占用物理存储空间。

2. 存储的生成列

存储的生成列会将计算结果存储在数据库中，这意味着每次对依赖列进行更新后，生成列的值也会相应更新并存储。这种列的优点是加快了查询速度，因为不需要每次查询时都进行计算。但缺点是增加了存储开销，并可能影响插入和更新的性能。

3. 虚拟的生成列

与存储的生成列相对，虚拟的生成列不会存储计算结果，每次查询时都会动态计算。这减少了存储开销，但可能会导致查询性能略有下降，特别是在涉及大量计算的场景中。

虚拟列的应用场景

1. 简化查询

通过在虚拟列中预先定义计算公式，可以简化复杂的SQL查询语句。例如，如果经常需要计算销售总额（商品数量乘以单价），可以直接创建一个虚拟列来存储这个计算结果。

假设有一个销售数据表，其中包含商品价格 (price) 和python销售数量 (quantity) 两个列。经常需要计算销售总额，即每一条记录的 price * quantity。如果每次查询都写这个计算式，不仅增加了查询的复杂性，还可能导致性能下降。引入一个虚拟列 total_sales，该列直接存储 price * quantity 的计算结果，可以极大简化查询：

CREATE TABLE sales (
    product_id INT,
    price DECIMAL(10,2),
    quantity INT,
    total_sales DECIMAL(10,2) AS (price * quantity) VIRTUAL
);

现在，每次需要获取销售总额时，只需直接查询 total_sales 列。

2. 优化查询性能

虚拟列特别适合用于索引。虽然MySQL不允许直接对非持久的虚拟列创建索引，但可以对持久的虚拟列创建索引，这样可以显著提高基于这些计算结果的查询速度。

在上述销售表中，如果需要经常根据销售总额进行查询或排序，可以将虚拟列 total_sales 编程;设为持久化，并对其建立索引：

CREATE TABLE sales (
    product_id INT,
    price DECIMAL(10,2),
    quantity INT,
    total_sales DECIMAL(10,2) AS (price * quantity) STORED
);

Cwww.devze.comREATE INDEX idx_total_sales ON sales(total_sales);

这样，利用索引，基于销售总额的查询和排序操作将显著提高效率，特别是在数据量较大时。

3. 维护数据一致性

虚拟列确保了数据的动态一致性，因为它们的值是基于其他列动态生成的。这有助于在数据库层面维护数据一致性，避免了应用层进行多余的数据处理。

考虑一个员工表，其中有员工的出生日期 (birth_date) 和年龄 (age) 两个列。虚拟列可以用来实时计算年龄，确保年龄数据始终准确反映当前日期相对于出生日期的差异：

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    birth_date DATE,
    age INT AS (TIMESTAMPDIFF(YEAR, birth_date, CURDATE())) VIRTUAL
);

这样，age 列会动态地根据当前日期和出生日期计算员工的年龄，无需手动更新。

创建和使用虚拟列

1. 创建虚拟列

创建虚拟列的语法如下：

CREATE TABLE my_table (
    column_a INT,
    column_b INT,
    total_c INT AS (column_a + column_b) VIRTUAL
);

2. 使用虚拟列

一旦虚拟列被创建，你可以像使用普通列一样使用它们，包括在SELECT语句中引用它们，或者在WHERE和ORDER BY子句中使用它们。

继续使用上面的销售表，查询某个产品的销售记录，包括计算得到的总销售额：

SELECT product_id, price, quantity, total_sales
FROM sales
WHERE product_id = 101;

这里的 total_sales 是虚拟列，但在查询中它的使用与普通列无异。使用虚拟列，使得查询更加直观和简洁。

虚拟列表达式的规则

在MySQL中使用虚拟列时，需要定义一个表达式来指定如何计算列的值。这些表达式需要遵守一些特定的规则和限制，以确保它们能正确地在数据库中执行。以下是创建和使用虚拟列时必须遵循的几个关键规则：

1. 表达式的确定性

虚拟列的表达式必须是“确定性的”，意味着给定相同的输入，表达式必须产生相同的输出。这确保了无论何时何地计算虚拟列，其结果都是一致的。例如，表达式不应该包含任何随机数生成或调用非确定性函数。

2. 参考列的限制

虚拟列的表达式只能使用同一张表中的其他列作为参考。不能引用其他表的列或进行跨表查询。此外，表达式中引用的列必须在虚拟列声明之前在表定义中出现。

3. 子查询的禁止

虚拟列的表达式不能包含子查询。这是因为子查询可能涉及大量数据处理或外部表的数据，这可能会复杂化虚拟列的计算和维护。

4. 存储函数和过程的禁用

虚拟列不能调用任何存储过程或存储函数。这是为了避免虚拟列的值依赖于可能改变的外部环境或数据库状态，保持计算的纯粹性和高效性。

5. 限制使用的数据类型

虚拟列的表达式中使用的数据类型应该是标准的SQL数据类型，并且要确保表达式结果的数据类型与虚拟列定义的数据类型兼容。例如，如果虚拟列被定义为整数类型，那么表达式也应该产生整数类型的结果。

6. 性能考量

虽然虚拟列不存储物理数据，但复杂的计算表达式可能会在查询时增加计算负担。建议使用尽可能简单的表达式，特别是对于频繁查询的虚拟列。

遵守这些规则可以帮助开发者有效地利用MySQL的虚拟列功能，同时保持数据库的性能和一致性。在设计数据库和查询时，应谨慎选择是否使用虚拟列，以及如何定义它们的计算表达式。

参考链接

• MySQL官方文档关于生成列的介绍：MySQL Generated Columns
• 关于如何优化MySQL查询的更多信息：Optimizing MySQL

到此这篇关于MySQL虚拟列的具体使用的文章就介绍到这了,更多相关MySQL虚拟列内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(ww编程客栈w.cppcns.com)！