MySQL 存储的生成列与 MySQL 虚拟生成列有何不同?

MySQL 存储的生成列与 MySQL 虚拟生成列有何不同?

MySQL 存储的生成列(Stored Generated Columns)和 MySQL 虚拟生成列(Virtual Generated Columns)是 MySQL8 引入的两个功能。

1. MySQL 存储的生成列

MySQL 存储的生成列是指创建表时,在列定义中使用表达式定义出来的新列,这个新列并不存储在表中,而是通过表达式计算出来的。可以通过已经存在的列或常量,进行加、减、乘、除、位运算等操作得到新列。存储的生成列可以创建普通索引,而且当给存储的生成列赋值时,MySQL会自动计算表达式的值来进行插入。

下面用一个例子来具体说明,我们创建一个表 student 并定义了三个列,分别表示学生的姓名、年龄、出生日期:

CREATE TABLE student (

name char(20),

age tinyint unsigned,

birthdate date

);

现在我们想要计算出学生的出生时间,可以使用存储的生成列来实现:

ALTER TABLE student ADD COLUMN birthtime TIMESTAMP AS (CONCAT(birthdate, " ", "00:00:00"));

这里我们使用 AS 关键字加上表达式来定义存储的生成列,它将 birthdate 和一个固定的时间串连接起来,生成一个新的 TIMESTAMP 类型的列。

2. MySQL 虚拟生成列

MySQL 虚拟生成列和存储的生成列不同的是,它在表中是存在的,但其值并不存储在磁盘上,每次使用它的时候都会动态计算出它的值。虚拟生成列存在于表中,也可以订阅更改,对于应用程序而言也是透明的。

下面还是用一个例子来说明,我们定义一个表 books,其中包含了书名、作者和出版日期三个字段:

CREATE TABLE books (

name VARCHAR(50),

author VARCHAR(20),

publish_date DATE

);

现在,我们需要在表中添加一个虚拟列 book_age,表示该书已经出版多少年。可以使用下面的语句来创建虚拟生成列:

ALTER TABLE books ADD COLUMN book_age INT AS (YEAR(CURDATE()) - YEAR(publish_date));

这里我们使用了 YEAR 和 CURDATE 函数计算书的年龄。在查询表时,虚拟生成列 book_age 可以参与查询条件、group by 和 order by 等操作,而且每次使用它的时候都会动态计算出它的值。

3. 存储的生成列和虚拟生成列的区别

存储的生成列和虚拟生成列主要区别在两个方面:

存储的生成列并不存储在磁盘上,只在查询时动态计算出来,而虚拟生成列的值在表中是实时计算出的,并存储在磁盘上。

存储的生成列可以创建索引,而虚拟生成列不能创建索引。

4. 两种生成列的使用场景

根据两种生成列的区别,我们可以有针对性地选择使用它们。存储的生成列主要适用于:

需要基于多个字段计算出新的列值,这个计算过程较为复杂的情况。

希望能够使用索引优化一些查询,提升查询效率的情况。

虚拟生成列适用于:

需要动态计算的列,且这些计算不是很复杂的情况。

由于计算量较大或者某些计算无法用存储的生成列实现的情况。

不需要使用索引优化的情况。

结语

MySQL8 引入的存储的生成列和虚拟生成列是非常实用的功能,可以让我们能够更方便、更灵活地处理不同场景下的计算需求。在使用时,我们需要注意它们的差异,选择合适的方式来实现我们的需求,以达到最佳的查询效率和性能表现。

免责声明:本文来自互联网,本站所有信息(包括但不限于文字、视频、音频、数据及图表),不保证该信息的准确性、真实性、完整性、有效性、及时性、原创性等,版权归属于原作者,如无意侵犯媒体或个人知识产权,请来电或致函告之,本站将在第一时间处理。猿码集站发布此文目的在于促进信息交流,此文观点与本站立场无关,不承担任何责任。

数据库标签