MySQL generated column 怎样把 JSON 字段提取成可索引列

如果 JSON 字段里有一个经常参与筛选的稳定标量，例如订单状态、租户编号或外部订单号，可以把它提取为 MySQL generated column，再给这个生成列建立索引。关键不是“把 JSON 整列塞进索引”，而是让生成表达式得到明确的字符串或数值类型，并让查询中的表达式与它保持一致。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/create-table-secondary-indexes.html

推荐的落地顺序是：确定单值 JSON 路径 → 明确返回类型 → 声明 generated column → 创建索引 → 用 EXPLAIN 检查表达式是否可匹配。没有稳定路径、值可能是数组，或每次更新都要重算大量字段时，不应为了“有索引”强行生成列。

• JSON 原列不直接承担普通索引，索引应落在提取出的标量列上。
• 字符串路径通常要去掉 JSON 引号；数值路径应显式转换为合适的数值类型。
• virtual 节省表内存，stored 读取更直接但会增加存储和写入维护成本。

先把 JSON 路径变成稳定的列类型

MySQL 的 JSON 列不能像普通字符列那样直接建立常规索引。应该先选一个只返回单值的路径，例如 $.customer_id，再决定它在查询中是字符串还是整数。JSON_EXTRACT() 返回 JSON 值；字符串路径如果直接拿来比较，容易保留 JSON 字符串的引号，因此生成列通常使用 JSON_UNQUOTE()，或在支持的场景使用带 RETURNING 的 JSON_VALUE()。

-- 先确认路径是标量，再决定生成列的 SQL 类型
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    payload JSON NOT NULL,
    -- 去掉 JSON 字符串引号，得到可按普通字符串比较的值
    customer_code VARCHAR(32)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer_code'))
        ) VIRTUAL
);

-- 数值字段要显式声明数值类型，避免按字符串排序和比较
ALTER TABLE orders
    ADD COLUMN amount_cents INT
        GENERATED ALWAYS AS (
            JSON_VALUE(payload, '$.amount_cents' RETURNING UNSIGNED)
        ) VIRTUAL;

如果路径不存在，JSON 提取结果可能变成 SQL NULL；这不是“索引坏了”，而是该行没有可索引的路径值。若业务要求缺失值和零值不同，应保留这种差异，不要随意用 COALESCE 把它们合并。

生成列和索引要成对设计

生成列默认是 VIRTUAL：值不单独存放，但可以建立二级索引。STORED 会在插入或更新时计算并保存值，适合需要反复读取、且愿意承担额外存储的字段。无论选择哪一种，索引都应建在生成列上，而不是把 JSON 原列直接当成普通索引键。

-- 生成列承载确定的类型，索引只负责查找这个标量
ALTER TABLE orders
    ADD INDEX idx_orders_customer_code (customer_code),
    ADD INDEX idx_orders_amount_cents (amount_cents);

-- 读取频繁、计算逻辑稳定时，可以改用 STORED
ALTER TABLE orders
    ADD COLUMN order_state VARCHAR(16)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(payload, '$.state'))
        ) STORED,
    ADD INDEX idx_orders_state (order_state);

同一表里可以混用两种生成列。选择时不要只看“虚拟列不占表空间”：对 virtual 列建立二级索引后，插入和更新仍要计算索引记录；而 stored 列除了索引，还会在聚簇记录中保存生成值。两者都可能增加写入工作，只是成本落点不同。

查询表达式必须与索引表达式对得上

优化器可以识别与生成列定义匹配的表达式，查询不一定要直接写生成列名称。但匹配依赖表达式形状和结果类型。比如生成列使用 JSON_UNQUOTE(JSON_EXTRACT(...))，查询也应保持同一路径、同一转换方式；把整数写成字符串、改变函数层级或替换排序规则，都可能让索引无法按预期参与计划。

-- 直接使用生成列，表达式边界最清楚
SELECT id
FROM orders
WHERE customer_code = 'C-1008';

-- 如果不写列名，保持与生成列相同的提取表达式
SELECT id
FROM orders
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer_code')) = 'C-1008';

-- 用 EXPLAIN 观察优化器是否把 idx_orders_customer_code 纳入候选
EXPLAIN SELECT id
FROM orders
WHERE customer_code = 'C-1008';

字符串索引还要留意排序规则。JSON_UNQUOTE 的结果有自己的字符集与排序规则，若又用 CAST(... AS CHAR) 改成另一套排序规则，查询表达式与索引表达式可能不再等价。实践中优先把列类型、字符集、排序规则一次定清楚，再固定查询写法。

什么时候值得提取，什么时候不要提取

最后的判断标准不是“是否成功建表”，而是索引是否覆盖真实查询、更新时是否能接受额外计算，以及缺失值、类型转换和排序规则是否符合业务语义。先选最稳定的一个路径做小范围变更，再结合实际 EXPLAIN 和写入负载决定是否扩展到更多字段。

常见问题

为什么不能直接给 JSON 列建普通索引？

JSON 文档不是一个单一的标量键。MySQL 的常见做法是把需要查询的路径提取为生成列，再在该列上建索引。

JSON_EXTRACT 返回字符串时为什么要 JSON_UNQUOTE？

JSON_EXTRACT 保留 JSON 值语义，字符串可能带 JSON 引号。JSON_UNQUOTE 把它转换为普通字符串，便于生成列类型和字符串比较保持一致。

virtual 和 stored 能不能只按性能二选一？

不能。还要同时考虑写入频率、生成值大小、聚簇记录空间、二级索引维护和查询读取成本。两者都需要用真实查询计划和写入负载做取舍。