MySQL 生成列
什么是MySQL生成列？
MySQL生成列（Generated Columns）是MySQL 5.7及以上版本引入的一项高级特性。生成列的值是通过表达式计算得出的，而不是通过手动插入或更新数据。生成列可以分为两种类型：
- 虚拟生成列（VIRTUAL）：列的值在查询时动态计算，不占用存储空间。
- 存储生成列（STORED）：列的值在插入或更新时计算并存储，占用存储空间。
生成列的主要用途是简化查询逻辑、提高查询性能以及确保数据一致性。
生成列的语法
生成列的定义语法如下：
column_name data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
column_name：生成列的名称。data_type：生成列的数据类型。expression：用于计算列值的表达式。VIRTUAL或STORED：指定生成列的类型（默认为VIRTUAL）。
生成列的示例
示例1：虚拟生成列
假设我们有一个 products 表，其中包含 price 和 quantity 两列。我们可以创建一个虚拟生成列 total_price，用于计算每个产品的总价。
CREATE TABLE products (
id INT PRIMARY KEY,
price DECIMAL(10, 2),
quantity INT,
total_price DECIMAL(10, 2) AS (price * quantity) VIRTUAL
);
插入数据并查询：
INSERT INTO products (id, price, quantity) VALUES (1, 10.50, 5);
SELECT * FROM products;
输出：
| id | price | quantity | total_price |
|---|---|---|---|
| 1 | 10.50 | 5 | 52.50 |
虚拟生成列的值在查询时动态计算，因此不会占用额外的存储空间。
示例2：存储生成列
如果我们希望将 total_price 的值存储起来以提高查询性能，可以使用存储生成列。
CREATE TABLE products_stored (
id INT PRIMARY KEY,
price DECIMAL(10, 2),
quantity INT,
total_price DECIMAL(10, 2) AS (price * quantity) STORED
);
插入数据并查询：
INSERT INTO products_stored (id, price, quantity) VALUES (1, 10.50, 5);
SELECT * FROM products_stored;
输出：
| id | price | quantity | total_price |
|---|---|---|---|
| 1 | 10.50 | 5 | 52.50 |
存储生成列的值在插入或更新时计算并存储，因此会占用额外的存储空间。
生成列的实际应用场景
场景1：计算字段
生成列非常适合用于计算字段，例如计算总价、折扣价或税费等。通过生成列，可以避免在每次查询时重复编写复杂的计算逻辑。
场景2：数据一致性
生成列可以确保数据的计算逻辑一致。例如，在 products 表中，total_price 的值始终是 price 和 quantity 的乘积，避免了手动更新时可能出现的错误。
场景3：索引优化
存储生成列可以用于创建索引，从而提高查询性能。例如，如果经常需要根据 total_price 进行查询，可以在存储生成列上创建索引。
CREATE INDEX idx_total_price ON products_stored(total_price);
总结
MySQL生成列是一项强大的功能，能够简化查询逻辑、提高性能并确保数据一致性。通过虚拟生成列和存储生成列，开发者可以根据实际需求选择适合的生成列类型。
在实际开发中，建议优先使用虚拟生成列，除非有明确的性能需求或需要创建索引。
附加资源与练习
练习1
创建一个 employees 表，包含 salary 和 bonus 两列，并添加一个虚拟生成列 total_income，用于计算总收入。
练习2
在 employees 表中添加一个存储生成列 tax，用于计算税费（假设税率为20%），并在该列上创建索引。
进一步学习
- MySQL官方文档：生成列
- 学习如何在生成列中使用复杂的表达式，例如字符串操作或日期计算。