MySQL的`Generated Columns`：如何利用它在不修改表结构的情况下，实现多维数据的实时计算？

MySQL Generated Columns：多维数据实时计算的利器

各位朋友，大家好！今天我们来聊聊MySQL中一个非常强大的特性——Generated Columns（生成列），以及如何利用它在不修改现有表结构的情况下，实现多维数据的实时计算。

什么是 Generated Columns？

Generated Columns，顾名思义，就是其值由其他列计算生成的列。与普通列不同，Generated Columns不存储实际的数据，而是根据预定义的表达式动态计算。当查询涉及 Generated Columns 时，MySQL会自动计算并返回结果。

MySQL 在版本 5.7.6 中引入了 Generated Columns，并在 8.0 版本中得到了进一步的增强。它主要分为两种类型：

• Virtual Generated Columns (虚拟生成列): 虚拟生成列不占用实际的存储空间，每次查询时动态计算。
• Stored 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 表示虚拟生成列，STORED 表示存储生成列。如果省略，默认为 VIRTUAL。

应用场景：多维数据实时计算

Generated Columns 非常适合用于多维数据的实时计算，例如：

• 计算总价： 根据单价和数量计算商品的总价。
• 计算百分比： 计算某个值在总数中的占比。
• 计算日期差： 计算两个日期之间的天数或月数。
• 提取日期信息： 从日期中提取年份、月份、日期等信息。
• 地理位置计算：根据经纬度计算距离。
• 数据聚合：结合JSON数据，提取并计算特定字段。

下面我们通过几个具体的例子来说明如何使用 Generated Columns 实现多维数据的实时计算。

示例 1：计算商品总价

假设我们有一个 orders 表，包含 product_id（商品ID）、quantity（数量）和 unit_price（单价）字段。我们可以使用 Generated Columns 计算每个订单的总价：

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL
);

INSERT INTO orders (product_id, quantity, unit_price) VALUES
(1, 2, 10.50),
(2, 1, 25.00),
(3, 3, 5.75);

SELECT * FROM orders;

查询结果如下：

order_id	product_id	quantity	unit_price	total_price
1	1	2	10.50	21.00
2	2	1	25.00	25.00
3	3	3	5.75	17.25

可以看到，total_price 列的值是根据 quantity 和 unit_price 动态计算的，无需手动维护。

示例 2：计算销售额百分比

假设我们有一个 sales 表，包含 region（地区）和 amount（销售额）字段。我们可以使用 Generated Columns 计算每个地区的销售额占总销售额的百分比。首先，我们需要创建一个辅助表来存储总销售额：

CREATE TABLE total_sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    total_amount DECIMAL(15, 2) NOT NULL
);

INSERT INTO total_sales (total_amount) VALUES (0); -- 先插入一个初始值

CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    region VARCHAR(50) NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    percentage DECIMAL(5, 2) GENERATED ALWAYS AS (amount / (SELECT total_amount FROM total_sales WHERE id = 1) * 100) VIRTUAL
);

INSERT INTO sales (region, amount) VALUES
('North', 10000),
('South', 15000),
('East', 8000),
('West', 12000);

-- 更新 total_sales 表的总销售额
UPDATE total_sales SET total_amount = (SELECT SUM(amount) FROM sales);

SELECT * FROM sales;

查询结果如下：

sale_id	region	amount	percentage
1	North	10000.00	22.22
2	South	15000.00	33.33
3	East	8000.00	17.78
4	West	12000.00	26.67

这里需要注意的是，由于 Generated Columns 依赖于 total_sales 表，因此需要先插入初始值，然后再更新总销售额。同时，由于涉及到子查询，性能可能会受到影响，建议谨慎使用。

示例 3：提取日期信息

假设我们有一个 events 表，包含 event_name（事件名称）和 event_date（事件日期）字段。我们可以使用 Generated Columns 提取事件日期的年份、月份和日期：

CREATE TABLE events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) NOT NULL,
    event_date DATE NOT NULL,
    event_year INT GENERATED ALWAYS AS (YEAR(event_date)) VIRTUAL,
    event_month INT GENERATED ALWAYS AS (MONTH(event_date)) VIRTUAL,
    event_day INT GENERATED ALWAYS AS (DAY(event_date)) VIRTUAL
);

INSERT INTO events (event_name, event_date) VALUES
('Conference', '2023-10-26'),
('Workshop', '2023-11-15'),
('Webinar', '2023-12-05');

SELECT * FROM events;

查询结果如下：

event_id	event_name	event_date	event_year	event_month	event_day
1	Conference	2023-10-26	2023	10	26
2	Workshop	2023-11-15	2023	11	15
3	Webinar	2023-12-05	2023	12	5

这样，我们可以方便地按照年份、月份或日期进行分组和统计。

示例 4：地理位置距离计算 (需要 MySQL 8.0+)

假设我们有一个 locations 表，包含 name (地点名称)，latitude（纬度）和 longitude（经度）字段。我们可以使用 Generated Columns 和 MySQL 8.0 引入的地理空间函数计算两个地点之间的距离。

CREATE TABLE locations (
    location_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    latitude DECIMAL(10, 7) NOT NULL,
    longitude DECIMAL(10, 7) NOT NULL,
    location POINT SRID 4326 GENERATED ALWAYS AS (POINT(latitude, longitude)) STORED
);

INSERT INTO locations (name, latitude, longitude) VALUES
('New York', 40.7128, -74.0060),
('Los Angeles', 34.0522, -118.2437),
('Chicago', 41.8781, -87.6298);

--  计算 New York 和 Los Angeles 之间的距离（单位：米）
SELECT
    ST_Distance_Sphere(
        (SELECT location FROM locations WHERE name = 'New York'),
        (SELECT location FROM locations WHERE name = 'Los Angeles')
    ) AS distance_meters;

这里，我们首先创建了一个 location 列，其数据类型为 POINT，用于存储地理坐标。然后，使用 ST_Distance_Sphere 函数计算两个地点之间的距离。注意，这里使用了 STORED 类型的 Generated Columns，因为地理空间计算的成本通常比较高，预先存储可以提高查询性能。 SRID 4326 是 WGS 84 坐标系的标准 SRID。

示例 5：JSON数据提取与计算 (需要 MySQL 5.7.22+)

假设我们有一个 products 表，其中包含一个 details 列，该列存储 JSON 格式的商品详细信息，例如价格、库存等。我们可以使用 Generated Columns 提取 JSON 数据中的特定字段，并进行计算。

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    details JSON NOT NULL,
    price DECIMAL(10, 2) GENERATED ALWAYS AS (details->>'$.price') VIRTUAL,
    stock INT GENERATED ALWAYS AS (details->>'$.stock') VIRTUAL
);

INSERT INTO products (product_name, details) VALUES
('Laptop', '{"price": 1200.00, "stock": 10}'),
('Mouse', '{"price": 25.00, "stock": 50}'),
('Keyboard', '{"price": 75.00, "stock": 20}');

SELECT product_name, price, stock FROM products;

查询结果如下：

product_name	price	stock
Laptop	1200.00	10
Mouse	25.00	50
Keyboard	75.00	20

这里，我们使用 ->> 运算符提取 JSON 数据中的 price 和 stock 字段，并将它们转换为 DECIMAL 和 INT 类型。这样，我们可以方便地对这些字段进行查询和计算。

Generated Columns 的限制

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

• 不能引用自身： Generated Columns 的表达式不能引用自身。
• 不能引用其他 Generated Columns： 在 MySQL 5.7 中，Generated Columns 的表达式不能引用其他 Generated Columns。但在 MySQL 8.0 中，这个限制被取消了。
• 不能引用系统变量或用户自定义变量： Generated Columns 的表达式不能引用系统变量或用户自定义变量。
• 不能包含不确定性函数： Generated Columns 的表达式不能包含不确定性函数，例如 RAND()、NOW() 等。
• 存储 Generated Columns 的开销： 存储 Generated Columns 会占用额外的存储空间，需要根据实际情况权衡。
• 循环依赖： 不允许出现循环依赖的情况，例如 A 列依赖 B 列，B 列又依赖 A 列。
• FULLTEXT 索引： 在 MySQL 5.7 中，不能在 Generated Columns 上创建 FULLTEXT 索引。但在 MySQL 8.0 中，这个限制被取消了。

注意事项

• 数据类型： Generated Columns 的数据类型必须与表达式的结果类型兼容。
• 性能： 虚拟生成列的性能取决于表达式的复杂程度。存储生成列可以提高查询性能，但会增加存储空间。
• 维护： 修改表达式可能会影响已有的数据，需要谨慎操作。

如何选择 Virtual 还是 Stored？

特性	Virtual Generated Column	Stored Generated Column
存储空间	不占用存储空间	占用存储空间
计算时间	每次查询时动态计算	在数据插入/更新时计算并存储
性能	查询时可能较慢，取决于表达式的复杂度	查询时通常更快，因为数据已经预先计算好
适用场景	计算成本较低，对存储空间敏感，数据更新频率较低	计算成本较高，需要频繁查询，对查询性能要求较高
修改表达式影响	修改表达式后，下次查询时生效	修改表达式后，需要手动更新数据才能生效

总结

Generated Columns 是 MySQL 中一个非常有用的特性，可以用于实现多维数据的实时计算，简化查询，优化性能，减少冗余数据。 通过Virtual 和 Stored 两种类型，可以灵活应对不同的场景。 在使用 Generated Columns 时，需要注意其限制和注意事项，并根据实际情况选择合适的类型。 掌握 Generated Columns 的使用方法，可以大大提高数据处理和分析的效率。

掌握 Generated Columns 的使用，提升数据处理和分析效率

通过上述讲解和示例，相信大家对 MySQL 的 Generated Columns 已经有了更深入的了解。 它不仅能帮助我们进行实时计算，简化查询，还能在不修改现有表结构的情况下，扩展数据处理能力。 灵活运用 Generated Columns，将能显著提升你的数据处理和分析效率。