MySQL生成列：让派生字段自己维护

  1.  Home
  2. Text  
    3. Share to：

接手的项目里，内容表往往一开始只有几个字段，两年之后变成三十多个字段，其中十几个是「本来可以算出来、但为了查询方便就存下来了」的冗余。这些字段随着业务变化慢慢和源数据对不上，最终变成一堆谁也说不清哪边是对的脏数据。

MySQL 从 5.7 开始提供的生成列（Generated Column）就是解决这个问题的原语：让数据库自己维护派生字段，写入方不需要操心，查询方照常按索引查。这一篇讲生成列的两种类型、索引与限制、真实可用的三个场景，以及升级前必须验证的兼容性检查。

一、虚拟列与存储列的区别

生成列的语法是在列定义后面加 AS (表达式)，分两种：

CREATE TABLE article (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title       VARCHAR(200)  NOT NULL,
  slug        VARCHAR(200)  NOT NULL,
  tags        VARCHAR(500)  DEFAULT NULL,           -- 逗号分隔，历史遗留
  price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  qty         INT           NOT NULL DEFAULT 0,
  -- 虚拟列：不占存储，读时计算
  amount      DECIMAL(12,2) AS (price * qty) VIRTUAL,
  -- 存储列：写入时计算并落盘
  title_len   INT AS (CHAR_LENGTH(title)) STORED,
  full_slug   VARCHAR(255) AS (CONCAT(id, '-', slug)) STORED,
  PRIMARY KEY (id),
  KEY idx_amount (amount),
  KEY idx_title_len (title_len)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

VIRTUAL 不占磁盘，每次读的时候按表达式算一遍。好处是表大小不变、改表达式只需要改元数据（ALTER TABLE ... MODIFY COLUMN 重建索引即可）；坏处是每次读都有计算成本，而且不能参与某些类型的索引（下面细说）。

STORED 真实占用磁盘，写入（INSERT / UPDATE）时算一次。好处是可以建普通索引、可以被全文索引使用；坏处是每次写入都多一次计算，表变大了。

选哪个的判断很简单：这个列主要用来过滤和排序 → STORED + 索引；只是偶尔读出来给人看 → VIRTUAL。不要为了省磁盘给高频查询字段用 VIRTUAL，那个计算成本最后会以 QPS 下降的形式还回来。

二、索引与那些不能用的场景

生成列能不能建索引，是它和「视图」最大的区别。规则是：

STORED 列可以建任何索引，包括主键（虽然没意义）、唯一索引、前缀索引、全文索引、空间索引。

VIRTUAL 列可以建二级索引（InnoDB 支持，本质是把虚拟值物化到索引里），但不能做主键，也不能做外键的一部分。这一点很关键 —— 虚拟列的数据本身不落盘，主键必须是真实的字节。

表达式的限制要提前知道，不然建表就会报错：

只能用确定性函数。NOW()、UUID()、RAND()、CURRENT_USER() 全部不行，因为同一个输入两次计算的结果不一样。想记录「创建时间」还是老老实实写 DEFAULT CURRENT_TIMESTAMP。

不能引用 AUTO_INCREMENT 列以外的生成列之外的其它生成列？可以引用，但引用链不能成环，而且被引用的列必须先定义。

不能用子查询。生成列只能基于本行的列计算，想引用其它表的数据只能靠触发器或者应用层。

不能引用其它表的列，也不能用聚合函数。SUM、COUNT、AVG 一律报错。

还有一个容易踩的：表达式的结果类型由表达式本身决定，不是由列定义决定。写 AS (price * qty) 时如果不加 CAST，精度可能和预期不同。涉及金额计算时建议显式写 CAST(price * qty AS DECIMAL(12,2))，避免隐式转换带来的四舍五入差异。

三、场景一：把 JSON 字段变成可索引的列

现在越来越多的表用 JSON 列存扩展属性：商品规格、用户偏好、事件参数。JSON 列查询慢是通病 —— 就算有函数索引，写 WHERE JSON_EXTRACT(attrs, '$.color') = 'red' 这种条件，优化器也经常识别不了。

生成列可以把热门的 JSON 路径「抬」成一级列：

ALTER TABLE product
  ADD COLUMN color VARCHAR(32)
    AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.color'))) STORED,
  ADD COLUMN weight_g INT
    AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.weight')) AS SIGNED)) STORED,
  ADD INDEX idx_color (color),
  ADD INDEX idx_weight (weight_g);

加完之后，查询就从 WHERE JSON_EXTRACT(...) = 'red' 变成 WHERE color = 'red'，走普通 B+ 树索引，执行计划干净，EXPLAIN 里的 type 从 ALL 变成 ref。

注意 JSON_UNQUOTE 是必需的。JSON 里的字符串带引号，JSON_EXTRACT 返回的是 "red" 而不是 red，直接比较永远不相等 —— 这个坑我在字符集与排序规则那篇里也提过类似的隐式转换问题，本质都是「看着一样实际不等」。

用 STORED 而不是 VIRTUAL 的原因：JSON 解析比普通列读取贵得多，每次查询都解析一遍不划算；而且 STORED 之后 ALTER TABLE 重写一次数据，之后的读就变回普通列。

四、场景二：用生成列当唯一约束的基础

业务里经常需要「部分唯一」：用户表的邮箱唯一，但历史数据里停用账户的邮箱允许重复。MySQL 不支持部分索引，常见的绕法是加一个 deleted_at 加 email 的复合唯一索引 —— 问题是 NULL 在唯一索引里不参与判重，(email, NULL) 插两条不会报错，但如果 deleted_at 用 0 而不是 NULL 就撞了。

干净的做法是加一个生成列，把「未删除」映射成邮箱本身、「已删除」映射成 NULL，然后在这个列上建唯一索引：

ALTER TABLE users
  ADD COLUMN active_email VARCHAR(190)
    AS (IF(status = 'active', email, NULL)) STORED,
  ADD UNIQUE KEY uk_active_email (active_email);

效果是：正常账户的邮箱必须唯一；软删除的账户全部是 NULL，MySQL 允许唯一索引里有多个 NULL，天然放行。比在应用层加锁查重可靠得多，也不会因为并发漏判。

这个模式很好用，可以推广到「每个用户只能有一条默认地址」「同一订单号在同一商户下唯一」这类需求上——本质是用 NULL 来构造「不参与唯一性检查」的语义。

五、场景三：查询时区与大小写归一

日志表按 UTC 存时间戳是标准做法，但运营查数据永远按本地时区：

ALTER TABLE access_log
  ADD COLUMN local_day DATE
    AS (DATE(CONVERT_TZ(created_at, '+00:00', '+08:00'))) STORED,
  ADD INDEX idx_local_day (local_day);

这样一来，按天出的报表直接 WHERE local_day BETWEEN ? AND ?，不用在 SQL 里做时区转换，范围扫描走索引，几百毫秒出结果。以前常见的写法 WHERE DATE(CONVERT_TZ(created_at, ...)) = '2026-11-02' 会让索引完全失效，只能全表扫。

另一个常见需求是大小写的归一键。用 utf8mb4_general_ci 排序规则的列本身不区分大小写，但 utf8mb4_bin 或 0900_ai_ci 混用的时候就不一定了。要保证「邮箱大小写不敏感地唯一」，可以建：

ALTER TABLE users
  ADD COLUMN email_lc VARCHAR(190)
    AS (LOWER(email)) STORED,
  ADD UNIQUE KEY uk_email_lc (email_lc);

这里有个容易忽略的点：LOWER() 对中文和大部分非 ASCII 字符是安全的，但它依赖字符集。如果列是 utf8mb3，某些拉丁扩展字符会转换错误。统一用 utf8mb4 再建这类列。

六、升级与改表达式时的小心点

生成列的 ALTER 有两个不同后果，必须分清：

改表达式（MODIFY COLUMN）：MySQL 8.0 需要重建该列的数据和相关索引，对大表是长时间的 COPY 操作，期间会有元数据锁。线上必须用 pt-online-schema-change 或者 gh-ost。

加索引：8.0 支持 ALGORITHM=INPLACE, LOCK=NONE，可以在线做。但如果是 VIRTUAL 列加索引，等价于物化一份数据到二级索引里，磁盘会明显增长。

还有一个老版本兼容性问题，如果是从 5.7 升到 8.0 或者要回滚，需要提前检查：

-- 列出所有含生成列的表
SELECT table_schema, table_name, column_name, extra
FROM information_schema.columns
WHERE extra LIKE '%VIRTUAL GENERATED%'
   OR extra LIKE '%STORED GENERATED%';

-- 检查是否有生成列引用了不推荐/已废弃的函数
SELECT table_schema, table_name, column_name, generation_expression
FROM information_schema.columns
WHERE generation_expression IS NOT NULL
  AND (generation_expression LIKE '%CURDATE%'
    OR generation_expression LIKE '%FROM_UNIXTIME%'
    OR generation_expression LIKE '%WEEK(%');

WEEK()、FROM_UNIXTIME() 这类函数在不同版本的默认行为不一样（WEEK() 受 default_week_format 影响），一旦函数行为变了，STORED 列的历史数据和重新计算的值会对不上。升级前把这类列全部改写成显式参数版本，比如 WEEK(d, 3)。

另外，生成的备份要注意。mysqldump 默认会把生成列的定义一起导出，但导入到低版本 MySQL 或者 MariaDB 时可能不识别语法。做跨版本迁移时先用 --no-data 导结构在目标版本试一遍。

七、什么情况下不该用生成列

生成列不是万能的，三种情况我会避开：

第一，表达式依赖外部数据（比如要用别的表的汇率算金额）。生成列做不到，勉强用触发器会带来写入放大和维护困难，不如在应用层算好存普通列。

第二，写入极度频繁且表达式很重。STORED 列每次写都要算一次，CONCAT + MD5 + SUBSTRING 这种组合在 5000 TPS 的表上会明显拖慢写入。这时候不如异步算好了写进来。

第三，只是为了让 SELECT 短一点。如果查询频率很低，写一个视图或者直接写完整 SQL 就够了，为了少敲几个字引入 DDL 变更不值当。

八、小结

生成列解决的核心问题是「派生数据没有唯一可信来源」。冗余字段最大的风险不是浪费空间，而是它会被写错、会忘记更新、会在多份代码里各算一遍。把它交给数据库，写入方就只剩下修改原始字段这一个动作。

三件事记住就行：VIRTUAL 省空间、STORED 能建完整索引，高频过滤字段一律选 STORED；JSON 路径抬成生成列是当前最划算的用法，记得配 JSON_UNQUOTE；改表达式等于重写列，线上必须走在线 DDL 工具。先把这几条落实，再去看那些存了三年、谁也不知道对不对的冗余字段，该删的删、该抬成生成列的抬。

Last modification：November 2nd, 2026 at 01:25 pm
© 允许规范转载

相关文章

Leave a Comment