MySQL generated column用生成列承接 JSON 路径索引的实现方法

MySQL 表里把订单扩展字段放进 JSON 很方便，但固定路径一多，WHERE data->>'$.customer.level' = 'gold' 就可能反复做 JSON 提取。实用做法是把这个路径提取成 generated column，再给生成列建立普通索引。关键不在“多加一列”，而在表达式、目标 SQL 类型和查询写法保持一致。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/

先把 JSON 路径和目标类型定下来

假设订单表的 data 保存客户等级、客户编号和金额。先取固定标量路径，再决定生成列是字符串、整数还是定点数。类型一旦确定，后续索引长度、比较方式和排序规则都会跟着确定。

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  data JSON NOT NULL,
  customer_level VARCHAR(32)
    GENERATED ALWAYS AS (
      JSON_UNQUOTE(JSON_EXTRACT(data, '$.customer.level'))
    ) VIRTUAL,
  customer_no BIGINT
    GENERATED ALWAYS AS (
      CAST(JSON_UNQUOTE(JSON_EXTRACT(data, '$.customer.no')) AS UNSIGNED)
    ) VIRTUAL
);

-- 先让查询直接落在生成列，便于单独确认索引是否可用
CREATE INDEX idx_orders_customer_level ON orders (customer_level);
CREATE INDEX idx_orders_customer_no ON orders (customer_no);

字符串路径用 VARCHAR 承接，数字路径用 UNSIGNED 或 DECIMAL 承接。不要把所有路径都当成字符串，否则数字比较和排序可能出现“10 排在 2 前面”的业务误解。

用 CAST 解决 JSON 表达式的可索引类型

JSON 运算符返回的结果并不总是适合直接做索引。MySQL 文档特别提醒，->> 等价于 JSON_UNQUOTE(JSON_EXTRACT())，结果可能被推断为 LONGTEXT；而没有前缀长度的 LONGTEXT 不能直接成为普通索引键。因此生成列应显式声明可控的字符串长度。

ALTER TABLE orders
  ADD COLUMN customer_level_bin VARCHAR(32)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
    GENERATED ALWAYS AS (
      CAST(
        JSON_UNQUOTE(JSON_EXTRACT(data, '$.customer.level'))
        AS CHAR(32)
      )
    ) VIRTUAL,
  ADD INDEX idx_orders_level_bin (customer_level_bin);

-- 生产查询直接使用同一生成列，避免隐式排序规则差异
SELECT id, data
FROM orders
WHERE customer_level_bin = 'gold';

这里的 utf8mb4_bin 只是示例：如果业务需要大小写不敏感，就应选择与业务比较规则一致的排序规则。索引表达式和查询表达式一旦使用不同 collation，结果可能正确但索引不命中，或者大小写相同的值被合并。

VIRTUAL 和 STORED 按读写成本选择

生成列默认是 VIRTUAL。如果 JSON 路径提取很简单，先用 VIRTUAL 加索引即可；若读多写少且表达式成本明显，再评估 STORED。索引本身始终要占空间，STORED 还会多保存一份生成结果，不能只看查询是否变快。

让查询表达式和索引定义保持同一条逻辑

最稳的迁移方式是先把生成列当作业务字段使用：

EXPLAIN
SELECT id
FROM orders
WHERE customer_level_bin = 'gold';

-- 重点看 possible_keys、key 和 type，而不是只看 rows
-- key 应出现 idx_orders_level_bin；type 通常应优于全表扫描
SHOW INDEX FROM orders;

如果应用暂时不能改成生成列名，再测试等价的 JSON 表达式。但不要只把 CAST 放在建表语句里，查询却使用另一套字符集或排序规则。对需要强一致解释的业务，直接引用生成列更容易审查，也更不容易因隐式转换改变结果。

上线前用四项边界检查收尾

1. 路径缺失：确认缺失路径得到 NULL，是否符合业务过滤语义；不要用空字符串偷偷代替 NULL。
2. 超长值：检查 VARCHAR(32) 是否足够，截断会改变等值匹配，严格模式下还可能拒绝 DDL 或写入。
3. 排序规则：用 gold 与 Gold 两条样例确认大小写是否应视为相等。
4. 表达式限制：生成列应使用确定性表达式；子查询、变量、存储函数和非确定性函数不能放进定义。

完成这四项后，再用代表性数据执行 EXPLAIN。如果直接使用生成列可以命中索引，而原 JSON 表达式不稳定，就把生成列名作为应用查询契约，不要依赖优化器替你猜等价关系。

常见问题

为什么 JSON 路径不能直接随便建索引

JSON 是文档类型，路径提取结果还需要落到可索引的 SQL 类型；字符串结果若被推断成 LONGTEXT，普通索引就会受到限制。

生成列一定要使用 STORED 吗

不一定。VIRTUAL 默认不保存列值，但可以为生成列建立二级索引；STORED 适合需要把计算结果物化、且能接受额外行存储的场景。

查询直接写 JSON_EXTRACT 会自动命中索引吗

匹配的等价表达式有机会被优化器识别，但类型和排序规则必须一致。上线初期直接引用生成列并用 EXPLAIN 验收更稳妥。

数组路径也适合用本文方案吗

本文只覆盖固定标量路径。JSON 数组包含关系要评估多值索引或其他数据建模方式，不能把标量生成列的写法机械套过去。

把 JSON 路径变成生成列，本质是给动态文档字段建立一个稳定的 SQL 边界：先明确路径，再确定类型和排序规则，最后用 EXPLAIN 证明查询真的使用了这条边界。