生成列（Generated Columns）概述

生成列（Generated Columns）是数据库表中的一种特殊列，它的值不是直接存储的，而是根据其他列的值计算得出的。生成列可以是虚拟列（Virtual Column）或存储列（Stored Column），根据实际需求来选择。生成列在某些场景下可以简化数据处理逻辑并提高查询效率，特别是当需要频繁计算某个值时。

生成列的类型

1. 虚拟列（Virtual Column）：

   • 特点：虚拟列的值不会实际存储在数据库中，而是每次查询时动态计算。
   • 优点：节省存储空间，因为它不占用额外的磁盘空间。
   • 缺点：每次查询时需要实时计算，可能影响查询性能，特别是在大数据量的情况下。

2. 存储列（Stored Column）：

   • 特点：存储列的值会在插入或更新时计算并保存到数据库中，因此查询时无需重新计算。
   • 优点：提高查询性能，因为值是预先计算好的，查询时直接读取。
   • 缺点：占用额外的存储空间，且每次插入或更新数据时需要额外的计算时间。

使用生成列的示例

假设我们有一个电商平台的 products 表，存储了商品的一些基础信息和属性数据，其中有些属性数据存储在 JSON 字段中。我们希望能够通过生成列来简化查询操作，提高查询性能。

表结构示例：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    category VARCHAR(50),
    attributes JSON,
    screen_size DECIMAL GENERATED ALWAYS AS (CAST(attributes->>'$.屏幕尺寸' AS DECIMAL)) STORED,
    battery_capacity DECIMAL GENERATED ALWAYS AS (CAST(attributes->>'$.电池容量' AS DECIMAL)) STORED
);

解释：

• screen_size 和 battery_capacity 是生成列，它们的值分别通过 CAST(attributes->>'$.屏幕尺寸' AS DECIMAL) 和 CAST(attributes->>'$.电池容量' AS DECIMAL) 计算得出。
• GENERATED ALWAYS AS 定义了生成列的计算方式。
• STORED 关键字表示这些生成列的值会被存储在数据库中，而不是每次查询时动态计算。

查询示例

现在我们可以通过生成列直接查询满足条件的商品，而无需每次都在查询语句中进行计算：

SELECT product_id, product_name, screen_size, battery_capacity
FROM products
WHERE screen_size > 6
AND battery_capacity > 3000;

优势：

• 简化查询：生成列让查询语句变得更加简洁，因为计算逻辑已经在列定义时处理好了。
• 提高性能：由于 screen_size 和 battery_capacity 是存储列，查询时不需要再执行 JSON 提取和数据类型转换操作，直接读取预先计算好的值，大大提高了查询性能。

注意事项

• 存储开销：存储列虽然提高了查询性能，但会增加数据库的存储需求，因为这些列的数据需要被实际存储。
• 更新成本：每次插入或更新涉及到基础列（例如 JSON 字段）时，生成列的值也需要重新计算并更新，这会增加写操作的成本。
• 适用场景：生成列特别适用于那些需要频繁查询计算结果且计算过程较为复杂的场景。在这种情况下，通过预先计算并存储结果可以大幅度提高系统的查询效率。

结论

生成列提供了一种灵活的方式来处理数据库表中的计算需求，尤其是在涉及到 JSON 字段或其他复杂计算的场景中。通过合理地使用存储列，开发人员可以有效提高查询性能，同时保持数据库设计的简洁性。