MySQL GENERATED COLUMNS 有哪些不同的类型？

在 MySQL 8.0 版本中，我们可以使用 GENERATED COLUMNS 关键字定义指定列的值。这些值可以是表达式、函数或者序列等等。 GENERATED COLUMNS 提供了一种便捷的方式，可以使列自动更新或生成。MySQL 提供了多种 GENERATED COLUMNS 的类型，让我们来一一了解。

阅读更多：MySQL 教程

存储生成列

存储生成列是指当数据插入到表中时，会自动计算该列的值并将其存储在表中，也就是说，存储生成列的值只有在插入数据时才会计算出来。

我们可以定义存储生成列的类型为 VIRTUAL 或者 PERSISTENT。当我们使用 VIRTUAL 类型时，该列的值只会在查询时被计算，而且该列的值不会被存储在表中；而 PERSISTENT 类型的生成列则会在插入数据时被计算，之后生成列的值会被存储在表中。

我们来看一个示例：

CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    birth_year YEAR AS (YEAR(CURRENT_DATE) - age) VIRTUAL
);

在上面这个表定义中，我们定义了一个存储生成列 birth_year，其 VIRTUAL 类型的表达式是 YEAR(CURRENT_DATE) – age。它的值只在查询时被计算，我们可以使用以下语句检索：

SELECT name, age, birth_year FROM student;

通过这个语句，我们可以在结果集中查看自动生成列的值，但是这个值不会被存储在表中。

虚拟生成列

虚拟生成列是指由一个表达式计算出所有值，而不依赖于表中的任何数据。在插入或更新行时，不能指定或设置虚拟生成列的值。

我们来看一个示例：

CREATE TABLE product (
    name VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    discount DECIMAL(8,2) GENERATED ALWAYS AS (price * 0.9) VIRTUAL,
    final_price DECIMAL(8,2) GENERATED ALWAYS AS (price - discount) VIRTUAL
);

在上述例子中，discount 和 final_price 虚拟生成列的类型均为 VIRTUAL。它们的值由表达式 price * 0.9 和 price – discount 计算而来。

持久化生成列

持久化生成列是指在插入行时，由 MySQL 自动生成生成列的值，并将该值存储在表中。之后在任何时候修改插入行时，值都不会更改。

我们来看一个示例：

CREATE TABLE sale (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(8,2) NOT NULL,
    discount DECIMAL(8,2) NOT NULL,
    final_price DECIMAL(8,2) GENERATED ALWAYS AS (price - discount) STORED
);

在上述例子中，final_price 是一个持久化生成列，其类型为 STORED。它的值为 price – discount，在插入行时计算出，且将其存储在表中。

存储序列

存储序列是指 MySQL 会保持当前自动增加的值，即便是行被删除或回滚。存储序列可以用于计算表中的某些列。

比如，每个用户注册时都会有一个唯一标识，我们就可以使用存储序列来保证注册时获得一个唯一的 ID。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    email VARCHAR(50) NOT NULL,
    reg_date DATE NOT NULL,
    registration_number INT AS (YEAR(reg_date) * 10000 + id) STORED UNIQUE
);

在表定义中，我们定义了一个 STORED 类型的 GENERATED COLUMNS，即 registration_number。它的值是由年份和自动增加的 ID 组合计算而来的。注意，我们使用 UNIQUE 约束来确保每个用户的 registration_number 值是唯一的。