mysql 生成列

MySQL 的生成列（Generated Column）是一个非常有用的特性，让我来详细介绍：

基本语法

-- 创建表时定义生成列
CREATE TABLE example (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    -- 虚拟列
    full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)),
    -- 存储列
    full_name_stored VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) STORED
);

-- 在现有表中添加生成列
ALTER TABLE example
ADD COLUMN age INT AS (YEAR(CURRENT_DATE) - YEAR(birth_date)) STORED;

两种类型

1. 虚拟列（VIRTUAL）

   -- 默认是 VIRTUAL
   ADD COLUMN total_amount DECIMAL(10,2) AS (quantity * price)
   

   • 不占用实际存储空间
   • 每次查询时实时计算
   • 适合简单计算且不频繁查询的场景
2. 存储列（STORED）

   ADD COLUMN total_amount DECIMAL(10,2) AS (quantity * price) STORED
   

   • 物理存储在表中
   • 在数据变更时计算并存储
   • 适合复杂计算或频繁查询的场景

常见使用场景

1. 日期处理

   CREATE TABLE orders (
    id INT PRIMARY KEY,
    created_at DATETIME,
    -- 年月
    ym VARCHAR(7) AS (DATE_FORMAT(created_at, '%Y-%m')) STORED,
    -- 年份
    year INT AS (YEAR(created_at)) STORED,
    -- 月份
    month INT AS (MONTH(created_at)) STORED,
    -- 季度
    quarter INT AS (QUARTER(created_at)) STORED
   );
   

2. 数据转换

   CREATE TABLE products (
    id INT PRIMARY KEY,
    price_cents INT,
    -- 转换为元
    price_yuan DECIMAL(10,2) AS (price_cents / 100) STORED
   );
   

3. 计算字段

   CREATE TABLE order_items (
    id INT PRIMARY KEY,
    quantity INT,
    unit_price DECIMAL(10,2),
    -- 计算总价
    total_price DECIMAL(10,2) AS (quantity * unit_price) STORED,
    -- 计算税额
    tax_amount DECIMAL(10,2) AS (quantity * unit_price * 0.13) STORED
   );
   

4. 字符串处理

   CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    -- 提取邮箱域名
    email_domain VARCHAR(255) AS (SUBSTRING_INDEX(email, '@', -1)) STORED,
    -- 转换大小写
    email_lower VARCHAR(255) AS (LOWER(email)) STORED
   );
   

注意事项

1. 限制

   -- 生成列不能：
   -- 1. 直接被插入数据
   -- 2. 直接被更新
   -- 3. 包含非确定性函数（如 RAND()）
   -- 4. 包含子查询
   

2. 索引使用

   -- 可以在生成列上创建索引
   CREATE INDEX idx_ym ON orders(ym);
   

3. 性能考虑

   -- 选择 VIRTUAL 还是 STORED：
   -- 1. 频繁查询但数据变更少 -> STORED
   -- 2. 简单计算且查询不频繁 -> VIRTUAL
   

4. 实际应用示例

   CREATE TABLE sales (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2),
    created_at DATETIME,
       
    -- 用于报表统计
    year_month VARCHAR(7) AS (DATE_FORMAT(created_at, '%Y-%m')) STORED,
       
    -- 用于季度分析
    quarter AS (CONCAT(YEAR(created_at), 'Q', QUARTER(created_at))) STORED,
       
    -- 用于金额分类
    amount_level VARCHAR(20) AS (
        CASE 
            WHEN amount < 100 THEN 'low'
            WHEN amount < 1000 THEN 'medium'
            ELSE 'high'
        END
    ) STORED
   );
   

生成列的使用可以：

• 简化查询逻辑
• 提高代码可维护性
• 优化查询性能
• 确保数据一致性

-- 根据datetime 生成索引常用的列
ALTER TABLE factory_affair_record
ADD COLUMN year INT GENERATED ALWAYS AS (YEAR(time_stamp)) STORED,
ADD COLUMN month VARCHAR(10) AS (CONCAT(CAST(YEAR(time_stamp) AS CHAR), '-', LPAD(CAST(MONTH(time_stamp) AS CHAR), 2, '0'))) STORED,
ADD COLUMN week_num INT AS (WEEK(time_stamp, 3)) STORED,
ADD COLUMN day DATE GENERATED ALWAYS AS (DATE(time_stamp)) STORED,
ADD COLUMN year_week INT GENERATED ALWAYS AS (YEARWEEK(time_stamp)) STORED;