MySQL前沿技术与新特性之：`MySQL`的`Generated Columns`：其在列计算中的应用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL Generated Columns：计算列的应用与实践

各位听众，大家好！今天我们来聊聊MySQL中一个相当实用的特性——Generated Columns，中文又称作“生成列”或“计算列”。它允许我们在表中定义一些列，其值不是直接存储的，而是通过一个表达式计算得到的。这在很多场景下都能极大地简化数据管理、提高查询效率，并保证数据的一致性。

什么是Generated Columns？

简单来说，Generated Columns是一种虚拟列，它的值由其他列或常量通过表达式计算而来。与普通列不同，Generated Columns并不实际存储数据（存储类型的除外），而是每次读取时动态计算。

Generated Columns分为两种类型：

Virtual Generated Columns (虚拟生成列): 这种类型的列只在查询时才计算，不占用存储空间。
Stored Generated Columns (存储生成列): 这种类型的列在插入或更新数据时计算并存储，占用存储空间。

为什么需要Generated Columns？

在没有Generated Columns的情况下，我们需要在应用程序代码中进行计算，或者创建一个额外的视图来显示计算结果。这会带来以下问题：

代码冗余: 同样的计算逻辑可能需要在多个地方重复编写。
数据不一致: 如果计算逻辑发生变化，我们需要修改所有相关的代码或视图，容易出现遗漏，导致数据不一致。
性能问题: 复杂的计算逻辑可能会影响查询性能，尤其是在数据量大的情况下。

Generated Columns可以很好地解决这些问题：

逻辑集中化: 计算逻辑定义在数据库层面，保证了逻辑的统一性。
数据一致性: 只要底层数据发生变化，Generated Columns的值会自动更新，保证了数据的一致性。
查询优化: Stored Generated Columns可以被索引，提高查询效率。

Generated Columns的语法

定义Generated Columns的基本语法如下：

column_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED]

column_name: 生成列的名称。
data_type: 生成列的数据类型。
GENERATED ALWAYS: 必须指定，表示这是一个生成列。
expression: 用于计算生成列值的表达式。可以使用其他列、常量、函数等。
VIRTUAL | STORED: 可选参数，指定生成列的类型。默认为VIRTUAL。

Generated Columns的使用示例

为了更好地理解Generated Columns的用法，我们来看几个具体的例子。

1. 计算全名：

假设我们有一个employees表，包含first_name和last_name两列，我们可以创建一个生成列full_name来显示员工的全名。

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');

SELECT * FROM employees;

查询结果：

id	first_name	last_name	full_name
1	John	Doe	John Doe

这里full_name是一个虚拟生成列，它的值由first_name和last_name拼接而成。

2. 计算订单总价：

假设我们有一个orders表，包含quantity和price两列，我们可以创建一个存储生成列total_price来存储订单的总价。

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * price) STORED
);

INSERT INTO orders (product_id, quantity, price) VALUES (1, 2, 10.50);

SELECT * FROM orders;

查询结果：

id	product_id	quantity	price	total_price
1	1	2	10.50	21.00

这里total_price是一个存储生成列，它的值由quantity和price相乘得到。由于它是存储类型的，因此每次插入或更新数据时，total_price的值都会被计算并存储。

3. 计算年龄：

假设我们有一个users表，包含birth_date列，我们可以创建一个生成列age来显示用户的年龄。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birth_date DATE,
    age INT GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birth_date, CURDATE())) VIRTUAL
);

INSERT INTO users (birth_date) VALUES ('1990-01-01');

SELECT * FROM users;

查询结果 (假设当前年份是2024)：

id	birth_date	age
1	1990-01-01	34

这里age是一个虚拟生成列，它的值由birth_date和当前日期计算而来。

Generated Columns的约束与限制

虽然Generated Columns非常强大，但也存在一些约束和限制：

表达式的限制: 生成列的表达式必须是确定性的，即相同的输入必须产生相同的输出。这意味着不能使用RAND()、UUID()等非确定性函数。
嵌套限制: 生成列不能引用其他的生成列（自 MySQL 5.7.6 开始支持），但是要避免循环引用。
数据类型限制: 生成列的数据类型必须是明确的，不能使用AUTO_INCREMENT等类型。
存储引擎限制: 并非所有存储引擎都支持Generated Columns。InnoDB是支持的。
索引限制: 只有Stored Generated Columns可以被索引。Virtual Generated Columns不能直接被索引，但可以在视图中使用Virtual Generated Columns，然后对视图进行索引。

Generated Columns的应用场景

Generated Columns在很多场景下都能发挥作用，例如：

数据转换和格式化: 将数据转换为不同的格式，例如将日期转换为字符串，将数值转换为货币格式。
数据验证: 检查数据的有效性，例如检查电子邮件地址的格式是否正确。
计算统计信息: 计算数据的统计信息，例如计算订单的总价、平均价格等。
实现复杂业务逻辑: 将复杂的业务逻辑封装在数据库层面，例如根据不同的条件计算不同的价格。
创建索引: 对Stored Generated Columns创建索引，提高查询效率。

Generated Columns与索引

Stored Generated Columns 可以被索引，这在某些场景下可以显著提高查询效率。例如，假设我们有一个products表，包含name和description两列，我们希望能够根据name和description的组合进行搜索。我们可以创建一个存储生成列full_text，并将name和description拼接在一起，然后对full_text创建全文索引。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    description TEXT,
    full_text TEXT GENERATED ALWAYS AS (CONCAT(name, ' ', description)) STORED,
    FULLTEXT INDEX idx_full_text (full_text)
);

INSERT INTO products (name, description) VALUES ('Apple iPhone', 'The latest iPhone model');
INSERT INTO products (name, description) VALUES ('Samsung Galaxy', 'A powerful Android phone');

SELECT * FROM products WHERE MATCH(full_text) AGAINST('iPhone');

在这个例子中，我们对full_text创建了全文索引，可以快速地搜索包含特定关键词的产品。

Stored Generated Columns 与 Virtual Generated Columns 的选择

选择使用 Stored 还是 Virtual Generated Columns 取决于具体的应用场景和性能需求。

特性	Stored Generated Columns	Virtual Generated Columns
存储空间	占用存储空间	不占用存储空间
计算时间	插入/更新时计算	查询时计算
索引	可以被索引	不能直接被索引
适用场景	频繁查询，需要高性能	数据量小，查询不频繁
数据一致性要求	高	较低

一般来说，如果需要频繁查询生成列，或者需要对生成列创建索引，那么应该选择 Stored Generated Columns。如果生成列的计算逻辑比较简单，或者数据量比较小，查询不频繁，那么可以选择 Virtual Generated Columns。

Generated Columns的注意事项

性能影响: 虽然Generated Columns可以提高查询效率，但也会带来一定的性能开销。特别是Stored Generated Columns，在插入和更新数据时需要进行计算，可能会影响写入性能。因此，在使用Generated Columns时需要进行充分的测试和评估。
可维护性: Generated Columns的表达式应该尽可能简单明了，避免过于复杂的逻辑，以提高代码的可维护性。
版本兼容性: Generated Columns是MySQL 5.7.6及以上版本才支持的特性，因此在使用时需要注意版本兼容性。

Generated Columns 与触发器（Trigger）的比较

Generated Columns 和触发器都可以实现类似的功能，例如在插入或更新数据时自动计算某些列的值。但是，它们之间存在一些区别：

特性	Generated Columns	触发器
实现方式	声明式的，定义在表结构中	过程式的，需要编写SQL代码
性能	通常比触发器更高效	可能会影响性能，特别是复杂的触发器
可维护性	更易于维护，逻辑集中在表结构中	需要单独维护触发器代码
功能	只能用于计算列的值	可以执行更复杂的操作，例如更新其他表、发送通知等
适用场景	简单的列计算，保证数据一致性	复杂的业务逻辑，需要在数据变更时执行其他操作

总的来说，如果只需要进行简单的列计算，并且希望保证数据的一致性，那么应该优先选择Generated Columns。如果需要执行更复杂的操作，或者需要在数据变更时执行其他操作，那么可以使用触发器。

一些最佳实践

选择合适的类型: 根据具体的应用场景选择合适的Generated Columns类型（Virtual或Stored）。
简化表达式: 尽量简化生成列的表达式，避免过于复杂的逻辑。
创建索引: 对Stored Generated Columns创建索引，提高查询效率。
测试和评估: 在生产环境中使用Generated Columns之前，进行充分的测试和评估，确保其性能和稳定性。
文档化: 详细记录Generated Columns的用途和计算逻辑，方便维护和理解。

使用示例：JSON 列的便捷访问

假设我们有一个 products 表，其中有一个 properties 列存储 JSON 数据，例如：

{
  "color": "red",
  "size": "M",
  "material": "cotton"
}

我们可以创建一个 Virtual Generated Column 来方便地访问 JSON 数据：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    properties JSON,
    color VARCHAR(50) GENERATED ALWAYS AS (properties ->> '$.color') VIRTUAL,
    size VARCHAR(50) GENERATED ALWAYS AS (properties ->> '$.size') VIRTUAL
);

INSERT INTO products (name, properties) VALUES ('T-shirt', '{"color": "red", "size": "M", "material": "cotton"}');

SELECT name, color, size FROM products WHERE color = 'red';

这样，我们可以直接通过 color 和 size 列来查询 JSON 数据，而不需要每次都使用 JSON 函数。

总结：Generated Columns 的价值

Generated Columns 是 MySQL 提供的一个强大的工具，它通过在数据库层面定义计算逻辑，实现了数据一致性、简化了代码、并提高了查询效率。掌握 Generated Columns 的使用，能帮助我们更好地设计数据库结构，优化查询性能，并构建更健壮的应用。

希望今天的分享对大家有所帮助，谢谢！

MySQL Generated Columns：计算列的应用与实践

发表回复 取消回复

发表回复取消回复