MySQL 5.7 中虚拟列必须声明为 STORED 才能建索引,VIRTUAL 列不支持索引;JSON 字段提取需用 ->> 并配合 STORED 列与显式列名查询,否则无法命中索引。

MySQL 5.7 虚拟列必须用 STORED 才能建索引
直接对 GENERATED ALWAYS AS (expr) 默认创建的 VIRTUAL 列执行 CREATE INDEX,一定会报错:ERROR 3107 (HY000): Generated column can only be indexed if it is STORED。这不是配置或权限问题,是 MySQL 5.7 的硬性限制——优化器不支持在索引中引用实时计算的值。
解决方法只有一条:显式声明 STORED。例如:
ALTER TABLE orders ADD COLUMN month_created TINYINT GENERATED ALWAYS AS (MONTH(created_at)) STORED;
之后才能加索引:CREATE INDEX idx_month_created ON orders(month_created);
-
STORED关键字不能省略,哪怕你 ALTER MODIFY 也得重写整条定义 - VIRTUAL 列无法通过任何 DDL 绕过限制“升级”为可索引列,只能删掉重建
- 表达式必须确定(deterministic):禁止
NOW()、RAND()、UUID()等非确定函数
JSON 字段提取必须走 STORED + 明确路径 + 去引号
想加速 WHERE json_col->>"$.user.email" 这类查询,不能直接在 JSON 字段上建索引,也不能用 VIRTUAL 列模拟——因为 ->> 返回的是字符串值,但 VIRTUAL 列无法被索引,而 STORED 列又要求路径表达式完全确定且结果为标量。
正确写法是:
ALTER TABLE users ADD COLUMN user_email VARCHAR(255) GENERATED ALWAYS AS (json_col->>"$.user.email") STORED;
然后立即建索引:CREATE INDEX idx_user_email ON users(user_email);
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 必须用
->>(去引号提取),不能用->,否则生成列值带双引号,导致WHERE user_email = 'a@b.c'匹配失败 - 路径字符串大小写、空格、点号都敏感,
$.User.Email和$.user.email是两个不同路径 - 如果 JSON 中 email 可能超长,
VARCHAR(255)得提前调大,截断后索引查不到数据 - 数组字段(如
"phones": ["157", "153"])无法用此方式索引——json_col->>"$.phones"返回的是字符串["157","153"],不是单值,不满足标量要求
查询时必须直接引用虚拟列名,不能回退到原表达式
建好 STORED 列和索引后,如果还在 WHERE 条件里写 json_col->>"$.user.email" = 'a@b.c' 或 MONTH(created_at) = 6,EXPLAIN 依然显示 type: ALL,全表扫描照旧。优化器不会自动把表达式重写为虚拟列引用。
你必须改写 SQL,显式使用虚拟列名:
- 原来:
SELECT * FROM users WHERE json_col->>"$.user.email" = 'a@b.c'; - 必须改成:
SELECT * FROM users WHERE user_email = 'a@b.c'; - 原来:
SELECT * FROM orders WHERE YEAR(created_at) = 2024; - 必须改成:
SELECT * FROM orders WHERE year_created = 2024;(假设你建了year_created SMALLINT GENERATED ALWAYS AS (YEAR(created_at)) STORED)
验证是否生效:用 EXPLAIN SELECT ... 看 key 列是否命中索引名,type 是否为 ref 或 range。
STORED 虚拟列的写入开销常被低估
很多人只看到“能加索引”,却忽略 STORED 列在 INSERT/UPDATE 时强制计算并落盘带来的副作用:
- 每行写入都要多一次表达式计算 + 多写一个字段,INSERT 性能下降明显,尤其表达式含函数嵌套或长字符串操作时
- 磁盘空间按行数 × 字段平均长度增长,千万级表加一个
VARCHAR(500)可能多占 1–2 GB - UPDATE 原始列(如
created_at)会连带重算并写入month_created,相当于扩大了锁范围和 binlog 体积 - 索引本身也要维护,B+ 树分裂频率随数据量和更新频次升高
上线前务必用真实负载压测写入吞吐,别只看单条 SELECT 快了多少。真正卡住系统的,往往是那 5% 的写操作变慢引发的连锁延迟。










