MySQL JSON_VALUE 怎么把 JSON 字段直接建表达式索引

把业务字段放进 JSON 列后，查询常常先写成 JSON_VALUE(payload, '$.customer_id')。真正要给它建索引时，最容易踩的坑不是双括号，而是返回类型没有固定：默认结果是 VARCHAR(512)，而业务条件却拿它和数字比较。稳妥的写法是把路径和类型同时写进表达式索引，例如 JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED)，查询端也保持同一表达式。

MySQL 8.4 可以直接对使用 JSON_VALUE 的表达式建索引。关键是：索引定义和 WHERE 条件要使用同一 JSON 列、同一路径、同一返回类型，并提前决定缺失键和错误值如何处理。

先把 JSON_VALUE 的类型和路径定死

假设订单表的 payload 保存了客户信息，订单列表经常按 customer_id 等值查询。先不要急着写索引，先决定这个值在 SQL 中是什么类型。MySQL 文档说明，JSON_VALUE() 不写 RETURNING 时返回 VARCHAR(512)；写上后可以返回 UNSIGNED、DECIMAL、日期时间或 CHAR 等类型。

-- 用固定的数值类型表达业务 ID，路径必须是字符串字面量
SELECT JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED) AS customer_id
FROM orders
WHERE order_id = 1001;

-- 建议先观察缺失键和 JSON null 的结果，再决定索引策略
SELECT JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED
                 NULL ON EMPTY NULL ON ERROR) AS customer_id
FROM orders;

路径 '$.customer_id' 是索引语义的一部分。把它改成 '$["customer_id"]'、换成另一个 JSON 列，或者把返回类型从 UNSIGNED 改成 CHAR，都可能让查询与已有索引不再兼容。对 ID 这类数值字段，显式类型也能减少字符串比较和隐式转换带来的判断偏差。

用双括号建立 JSON_VALUE 表达式索引

新建表时，可以直接把函数表达式写在索引定义中：

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    payload JSON NOT NULL,
    -- 双括号表示这是表达式索引，索引值来自 JSON_VALUE 的转换结果
    INDEX idx_customer_id (
        (JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED))
    )
);

-- 已有表使用 ALTER TABLE 增加同一个表达式索引
ALTER TABLE orders
    ADD INDEX idx_customer_id (
        (JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED))
    );

这里的外层括号属于索引语法，内层表达式括号用于明确告诉 MySQL 这是一个 functional key part，不能省略后把它写成普通列列表。MySQL 将这类索引实现为隐藏的虚拟生成列；索引本身需要占用存储空间，但不会把一个可见业务列添加到表结构里。

建完后可以用 SHOW CREATE TABLE orders 检查定义是否符合预期。这个动作只检查元数据，不等于已经证明查询会命中索引；命中情况要交给执行计划确认。

查询端要复用完整表达式

索引建立后，查询条件也应把 JSON 列、路径和返回类型完整写出：

-- 查询表达式与索引保持同一列、同一路径和同一返回类型
EXPLAIN
SELECT order_id, payload
FROM orders
WHERE JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED) = 42;

-- 用实际业务参数替换常量时，仍保留 UNSIGNED 语义
SELECT order_id
FROM orders
WHERE JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED) = CAST(? AS UNSIGNED);

重点看 EXPLAIN 的 possible_keys 和 key：前者表示优化器认为可能使用的索引，后者表示本次计划实际选中的索引。若 key 为空，不要先盲目加更多索引，先逐项对照索引定义和 WHERE 表达式。把 RETURNING UNSIGNED 去掉、换路径、在外层再包一层不同的转换函数，都可能改变可匹配的表达式。

缺失值和错误值决定索引边界

JSON_VALUE() 找不到路径时默认是 NULL ON EMPTY，发生转换等错误时默认是 NULL ON ERROR；JSON 文档里的 JSON null 也会得到 SQL NULL。因此，同一个索引中可能同时存在有效客户 ID 和 NULL。若业务需要拒绝脏数据，可以显式使用 ERROR ON EMPTY 或 ERROR ON ERROR，但要先确认历史数据和写入链路能承受异常。

表达式索引继承生成列的限制，不能在表达式里使用子查询、变量、参数、存储函数或可加载函数。路径应保持为常量，转换类型应和业务字段含义一致。若字段本质上是带前导零的字符串编号，就不要为了“更容易索引”强行转成数字，否则 0012 与 12 会失去区分。

常见问题与排查清单

为什么不能只给 JSON 列本身建普通索引？

JSON 文档不是一个可直接按内部成员定位的普通标量列。常见做法是索引提取出来的标量表达式，或者先使用生成列再给生成列建索引；JSON_VALUE() 允许在合适场景下省掉中间的可见生成列。

默认返回 VARCHAR(512) 一定不能建索引吗？

不是“不能”，而是要先确认字符串类型、字符集和排序规则就是业务需要。数字 ID 通常更适合显式返回数值类型，避免查询比较时依赖隐式转换。

什么时候仍然选择生成列？

当多个查询、报表或应用代码都需要一个可见列名，或者需要对转换结果做更细的约束与复用时，生成列会更直观。直接表达式索引适合边界清楚、只需索引访问的字段。

最后按三项确认索引是否值得保留

第一，确认 JSON 路径代表稳定的业务字段；第二，确认 RETURNING 类型没有把字符串标识误转成数字；第三，针对真实查询用 EXPLAIN 检查 key，并观察数据分布和写入成本。表达式索引解决的是“从 JSON 中取出一个稳定标量并参与检索”，不是让任意 JSON 路径查询都自动变快。