MySQL GENERATED COLUMN 生成列

在本文中,我们要介绍 MySQL 生成列的用法。

什么是生成列

在 MySQL 中,生成列(GENERATED COLUMN)是一个特殊的列,它的值会根据列定义中的表达式自动计算得出。并且,你不能直接写入或更新生成列的值。

生成列有 2 种类型:

  • 虚拟生成列:列值不会被存储下来。当读取该列时,MySQL 自动计算该列的值。
  • 存储生成列:当插入或修改数据时,MySQL 自动计算该列的值并存储在磁盘上。

生成列的语法

要创建生成列,请使用以下语法:

col_name data_type
  [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
  [NOT NULL | NULL]
  [UNIQUE [KEY]]
  [[PRIMARY] KEY]
  [COMMENT 'string']

请注意第二行的 [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]

  • GENERATED ALWAYS 关键字指示此列是一个生成列。它是可选的。

  • AS (expr) 设置此生成列的表达式。

  • VIRTUALSTORED 关键字指示了是否存储列值。这是可选的。

    • VIRTUAL: 列值不会被存储。当读取该列时,MySQL 自动计算该列的值。它是默认值。
    • STORED: 当插入或修改数据时,MySQL 自动计算该列的值并存储下来。

你可以在通过 CREATE TABLE 语句创建表时定义生成列,或者通过 ALTER TABLE 语句添加一个生成列。

生成列实例

假设,你有个订单明细表,具有如下结构:

CREATE TABLE order_item (
  order_item_id INT AUTO_INCREMENT PRIMARY KEY,
  goods VARCHAR(45) NOT NULL,
  price DECIMAL NOT NULL,
  quantity INT NOT NULL
);

让我们插入几行测试数据:

INSERT INTO order_item (goods, price, quantity)
VALUES ('Apple', 5, 3), ('Peach', 4, 4);

让我们看一下 order_item 表中的所有数据:

SELECT * FROM order_item;
+----------+-------+-------+----------+
| order_id | goods | price | quantity |
+----------+-------+-------+----------+
|        1 | Apple |     5 |        3 |
|        2 | Peach |     4 |        4 |
+----------+-------+-------+----------+
2 rows in set (0.00 sec)

现在,我们使用如下 SQL 查询每个订单项目的总金额:

SELECT
  goods,
  price,
  quantity,
  (price * quantity) AS total_amount
FROM order_item;

这里,我们通过 price * quantity 计算总金额,并使用了别名 total_amount

+-------+-------+----------+--------------+
| goods | price | quantity | total_amount |
+-------+-------+----------+--------------+
| Apple |     5 |        3 |           15 |
| Peach |     4 |        4 |           16 |
+-------+-------+----------+--------------+
2 rows in set (0.00 sec)

毫无疑问,这是正确的。

MySQL 生成列可以简化我们的工作,让你不用写这么复杂的 SELECT 语句。现在我们要通过以下语句添加一个生成列:

ALTER TABLE order_item
ADD COLUMN total_amount DECIMAL
  GENERATED ALWAYS AS (price * quantity) STORED;

这里,我们添加了一个 total_amount 列。它是一个生成列,它的计算表达式是 price * quantity

现在看一下 order_item 的结构:

DESC order_item;
+--------------+---------------+------+-----+---------+------------------+
| Field        | Type          | Null | Key | Default | Extra            |
+--------------+---------------+------+-----+---------+------------------+
| order_id     | int           | NO   | PRI | NULL    | auto_increment   |
| goods        | varchar(45)   | NO   |     | NULL    |                  |
| price        | decimal(10,0) | NO   |     | NULL    |                  |
| quantity     | int           | NO   |     | NULL    |                  |
| total_amount | decimal(10,0) | YES  |     | NULL    | STORED GENERATED |
+--------------+---------------+------+-----+---------+------------------+

在这里,我们发现,total_amount 是一个存储的生成列。

我们通过以语句查询表中的数据:

SELECT
  goods,
  price,
  quantity,
  total_amount
FROM order_item;
+-------+-------+----------+--------------+
| goods | price | quantity | total_amount |
+-------+-------+----------+--------------+
| Apple |     5 |        3 |           15 |
| Peach |     4 |        4 |           16 |
+-------+-------+----------+--------------+
2 rows in set (0.00 sec)

现在,我们轻松多了。我们再也不需要写那么复杂的 SQL 语句了。

更新生成列

你不能直接写入或者更新生成列的值。这会引发错误。我们尝试一下,看看究竟会发生什么。

让我们先试着插入一个带有生成列值的数据:

INSERT INTO order_item (goods, price, quantity, total_amount)
VALUES ('Banana', 6, 4, 24);

这返回了错误: ERROR 3105 (HY000): The value specified for generated column 'total_amount' in table 'order_item' is not allowed.

让我们再尝试修改生成列的值:

UPDATE order_item
SET total_amount = 30
WHERE goods = 'Apple';

这返回了相同的错误: ERROR 3105 (HY000): The value specified for generated column 'total_amount' in table 'order_item' is not allowed.

VIRTUAL vs STORED

生成列有两种类型: VIRTUALSTORED。他们之间有些不同:

  • 虚拟生成列不需要存储空间;存储生成列需要存储空间。
  • 虚拟生成列的值在每次读操作的时候都会被重新计算;存储生成列的值在插入行或者修改行的时候被计算。

如果数据经常发生变动,请考虑使用虚拟生成列;如果数据在创建后不经常变动,请考虑使用存储生成列。

像上面的例子中,一个订单被创建后一般不会再改动,这里很适合使用存储生成列。

结论

在本文中,我们学习了 MySQL 生成列的用法。以下是本文的要点内容:

  • 生成列是一个其值可以自动被计算得出的列。
  • 生成列有两种类型:VIRTUALSTORED
  • 生成列的值不能被写入或者修改。