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发生变化时，我们不需要手动更新打折后的价格，减少了数据冗余和维护工作。

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

到此这篇关于Mysql虚拟列的实现示例的文章就介绍到这了,更多相关Mysql虚拟列内容请搜索编程网(www.lsjlt.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.lsjlt.com)！

阅读原文内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

历年真题答案解析
备考技巧名师总结
高频考点精准押题

资料下载
历年真题

2024上半年软考中级软件测评师考试基础知识真题
193.9 KB下载数265
2024上半年软考中级软件设计师考试基础知识真题
191.63 KB下载数245
2023下半年-系统集成项目管理工程师-真题考点汇总（完整版）
143.91 KB下载数1148
2023年下半年系统集成项目管理工程师第一、二、三批次真题考点整理(考友回忆版)
183.71 KB下载数642
2023年上半年软考中级《系统集成项目管理工程师》-基础知识-考试真题及答案
644.84 KB下载数2756

2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）
难度 813人已做
查看
【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析
难度 354人已做
查看
【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析
难度 318人已做
查看
2024年上半年软考高项第一、二批次真题考点汇总（完整版）
难度 435人已做
查看
2024年上半年系统架构设计师考试综合知识真题
难度 224人已做
查看

发现更多好内容

猜你喜欢

AI推送时光机