Mysql虚拟列

1. 介绍MySQL虚拟列

1.1 定义和作用

MySQL虚拟列是一种特殊的列，是mysql-5.7版本引入的一个新特性，它并不存储数据，而是在查询时动态生成数据。这种列的值通常是由其他列的值通过某种表达式计算得出的。虚拟列的主要作用是提高查询效率和数据处理的便利性。它可以使我们在不增加实际存储开销的情况下，对数据进行更高效的查询和管理。

1.2 虚拟列与普通列的区别

虚拟列与普通列在使用上有几个主要的区别：

• 存储方式：普通列的值在插入或更新时会被实际存储在数据库中，而虚拟列的值并不会被存储，而是在查询时动态计算生成。

• 更新方式：普通列的值可以直接通过UPDATE语句进行修改，而虚拟列的值则不能直接修改，它的值是由定义它的表达式决定的。

• 索引应用：虚拟列可以被索引，这使得它在某些情况下可以提高查询性能。虽然虚拟列的值在查询时才生成，但是如果对虚拟列创建了索引，那么索引的值会被存储，从而提高查询效率。

虽然虚拟列在某些方面与普通列不同，但是在SQL查询中，我们可以像使用普通列一样使用虚拟列。

2. MySQL虚拟列的类型

在MySQL中，虚拟列主要分为两种类型：生成列和存储列。

2.1 生成列

生成列是一种特殊的虚拟列，它的值是由其他列的值通过一个表达式生成的。生成列的值不会被实际存储，而是在查询时动态计算生成。生成列可以是基于一个或多个列的任何MySQL合法的表达式。

语法如下

ALTER TABLE users
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name));

2.2 存储列

存储列是生成列的一种特殊形式。与生成列不同，存储列的值在插入或更新数据时会被计算并实际存储在数据库中。这意味着存储列的值不需要在查询时动态计算。

虽然存储列需要占用额外的存储空间，但是它可以提高查询速度，因为它的值在查询时已经被计算并存储好了。存储列特别适用于那些计算成本高，但查询频繁的场景。

需要注意的是，虽然存储列的值被存储在数据库中，但是它的值不能直接被修改，它的值仍然是由定义它的表达式决定的。

ALTER TABLE users
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) STORED;

3. 如何创建和使用MySQL虚拟列

3.1 创建含有虚拟列的表

在创建新表时，你可以在表定义中包含一个或多个虚拟列。下面是一个例子：

CREATE TABLE employees (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(200) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

在MySQL中，GENERATED ALWAYS是用来定义生成列（包括虚拟列和存储列）的关键字。这个关键字指示MySQL，这个列的值不是由用户直接插入或更新的，而是由一个表达式自动生成的。这个表达式可以引用表中的其他列的值。

3.2 更新虚拟列的值

虚拟列的值是由一个表达式计算出来的，这个表达式可以引用表中的其他列的值。因此，你不能直接更新虚拟列的值。相反，当你更新虚拟列所依赖的列的值时，虚拟列的值会自动更新。

例如，假设你更新了一个员工的first_name：

UPDATE employees SET first_name = 'John' WHERE last_name = 'Doe';

在这个例子中，full_name列的值会自动更新，因为它是由first_name和last_name的值拼接而成的。

3.3 查询虚拟列的值

虚拟列的值可以像普通列的值一样查询。例如，下面的查询会返回所有员工的全名：

SELECT full_name FROM employees;

在这个例子中，full_name列的值是在查询时计算的，而不是存储在表中的。这意味着，每次你查询full_name列的值时，MySQL都会重新计算这个值，但如果你使用的存储的虚拟列则不会实时计算

4. MySQL虚拟列的使用场景

虚拟列在MySQL中有许多实用的应用场景，包括优化查询、管理数据冗余、以及进行数据转换和计算。以下是一些具体的使用例子：

4.1 联合索引优化

虚拟列可以用来创建联合索引，以优化查询性能。例如，如果你经常需要在first_name和last_name上进行联合查询，你可以创建一个虚拟列full_name，并在这个列上创建索引：

CREATE TABLE employees (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(200) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    INDEX (full_name)
);

在这个例子中，full_name列的索引可以用来优化查询，比如SELECT * FROM employees WHERE full_name = 'John Doe';。

4.2 数据冗余管理

虚拟列可以用来减少数据冗余。例如，如果你的表中有一列是由其他列的值计算出来的，你可以使用虚拟列，而不是存储这个计算结果。这样，你可以节省存储空间，并确保数据的一致性。

例如，假设你有一个orders表，这个表有quantity和price两列，你可以创建一个虚拟列total_price，它的值是quantity和price的乘积：

CREATE TABLE orders (
    quantity INT,
    price DECIMAL(10, 2),
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * price) VIRTUAL
);

在这个例子中，total_price列的值会自动更新，当quantity或price的值变化时。

4.3 数据转换和计算

虚拟列可以用来进行数据转换和计算。例如，你可以创建一个虚拟列来存储日期的年份部分，或者计算两列的比例。

例如，假设你有一个sales表，这个表有total_sales和total_costs两列，你可以创建一个虚拟列profit_margin，它的值是total_sales和total_costs的比例：

CREATE TABLE sales (
    total_sales DECIMAL(10, 2),
    total_costs DECIMAL(10, 2),
    profit_margin DECIMAL(10, 2) GENERATED ALWAYS AS (total_sales / total_costs) VIRTUAL
);

在这个例子中，profit_margin列的值会自动更新，当total_sales或total_costs的值变化时。

5. MySQL虚拟列的限制和注意事项

虽然虚拟列在许多情况下都非常有用，但是它们也有一些限制和注意事项。以下是一些主要的限制和注意事项：

5.1 数据类型限制

虚拟列的数据类型必须是可以从生成列表达式的结果类型推导出来的。例如，如果你的表达式是两个整数列的乘积，那么虚拟列的数据类型应该是整数或者是可以包含乘积结果的任何其他类型。

5.2 更新和删除限制

虚拟列的值是由表达式计算出来的，不能直接更新。如果你尝试直接更新虚拟列的值，MySQL将会返回一个错误。同样，你也不能删除虚拟列，除非你同时删除依赖于该列的所有其他对象，如索引和触发器。

5.3 其他注意事项

• 虚拟列的表达式不能引用其他虚拟列的值。
• 虚拟列的表达式不能包含不确定的元素，比如当前时间或者随机数。
• 虚拟列不能有默认值。
• 虚拟列的值在查询时计算，因此，如果虚拟列的表达式非常复杂，或者表中的数据量非常大，查询虚拟列的值可能会比查询存储的列的值慢。
• 虽然虚拟列不占用存储空间，但是在虚拟列上创建的索引会占用存储空间。

在使用虚拟列时，需要考虑到这些限制和注意事项，以确保你的数据库设计和查询能够正确、有效地工作。

6. 实战：使用MySQL虚拟列解决实际问题

6.1 问题描述

假设我们正在开发一个电子商务网站，我们有一个products表，这个表包含了产品的price和discount信息。现在，我们希望能够快速查询出打折后的价格，但我们不希望在表中为每个产品都存储一个打折后的价格字段，因为这会增加数据冗余，并且当price或discount发生变化时，需要手动更新打折后的价格。

6.2 解决方案设计

我们可以使用MySQL的虚拟列来解决这个问题。我们可以在products表中添加一个虚拟列discounted_price，这个列的值是price和discount的乘积。由于虚拟列的值是动态计算的，因此当price或discount发生变化时，discounted_price的值会自动更新。

6.3 实现步骤

ALTER TABLE products 
ADD COLUMN discounted_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * (1 - discount/100)) VIRTUAL;

在这个SQL语句中，我们添加了一个名为discounted_price的虚拟列，它的值是price和discount的乘积。注意，我们假设discount是一个百分比值，比如15表示15%的折扣。

6.4 结果和效果分析

现在，我们可以直接查询discounted_price列来获取打折后的价格，而不需要在应用程序中进行计算。这使得查询更加简单和直观。同时，由于discounted_price列的值是动态计算的，因此当price或discount发生变化时，我们不需要手动更新打折后的价格，减少了数据冗余和维护工作。

需要注意的是，虽然虚拟列不占用存储空间，但是如果虚拟列的表达式非常复杂，或者表中的数据量非常大，查询虚拟列的值可能会比查询存储的列的值慢。因此，在使用虚拟列时，需要根据实际情况进行权衡。