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