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)设置此生成列的表达式。 -
VIRTUAL或STORED关键字指示了是否存储列值。这是可选的。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
生成列有两种类型： VIRTUAL 和 STORED。他们之间有些不同：
- 虚拟生成列不需要存储空间；存储生成列需要存储空间。
- 虚拟生成列的值在每次读操作的时候都会被重新计算；存储生成列的值在插入行或者修改行的时候被计算。
如果数据经常发生变动，请考虑使用虚拟生成列；如果数据在创建后不经常变动，请考虑使用存储生成列。
像上面的例子中，一个订单被创建后一般不会再改动，这里很适合使用存储生成列。
结论
在本文中，我们学习了 MySQL 生成列的用法。以下是本文的要点内容：
- 生成列是一个其值可以自动被计算得出的列。
- 生成列有两种类型：
VIRTUAL和STORED。 - 生成列的值不能被写入或者修改。