MySQL 给 JSON 标量建索引前为什么要先 CAST

MySQL 给 JSON 标量建索引前先 CAST，核心不是“语法要求”，而是先把 JSON 提取结果定型成可索引、可比较的 SQL 标量。例如 payload->>'$.customer_code' 会展开为 JSON_UNQUOTE(JSON_EXTRACT(...))，其结果类型是 LONGTEXT；函数索引又不能给这个结果指定前缀长度，所以直接建索引会失败。转换为 CHAR(32) 后，MySQL 才能为隐藏生成列确定长度、排序规则和索引键格式。

本文以 MySQL 8.4 参考手册为事实边界，官方入口为 https://dev.mysql.com/doc/refman/8.4/en/create-index.html。

先给出一套可用写法

下面直接给 customer_code 建函数索引。双层括号中，外层是索引键列表，内层表示函数表达式：

-- 把 JSON 字符串标量限制为最长 32 个字符，再创建函数索引
CREATE INDEX idx_customer_code
ON orders ((
  CAST(
    JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer_code'))
    AS CHAR(32)
  )
));

-- 查询复用相同路径、类型与长度，避免表达式不匹配
SELECT id, payload
FROM orders
WHERE CAST(
        JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer_code'))
        AS CHAR(32)
      ) = 'C1024';

如果项目允许使用 JSON_VALUE，写法会更紧凑。它的 RETURNING 子句直接声明结果类型；官方文档也将它说明为 CAST(JSON_UNQUOTE(JSON_EXTRACT(...)) AS type) 的简化形式：

-- 直接声明 JSON 路径返回无符号整数，并为该表达式建索引
CREATE INDEX idx_customer_id
ON orders ((JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED)));

-- WHERE 使用与索引完全相同的 JSON_VALUE 表达式
SELECT id, payload
FROM orders
WHERE JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED) = 1024;

为什么 JSON 标量不能直接拿来做函数索引键

MySQL 的 JSON 列本身不能像普通字符串列那样直接创建 B-tree 索引。常见做法是从文档中提取一个标量，再索引生成列或表达式。问题出在“提取”与“索引”之间：JSON 路径只说明从哪里取值，并没有替业务确定这个值应按字符串、整数、金额还是时间比较。

对字符串路径来说，->> 等价于 JSON_UNQUOTE(JSON_EXTRACT(...))。后者返回 LONGTEXT。普通长文本索引往往需要前缀长度，而函数索引的键表达式不能写列前缀，结果就是缺少可用的索引边界。CAST(... AS CHAR(32)) 会让隐藏虚拟生成列得到一个有界的字符串类型，索引键的最大长度也随之确定。

从字段到查询的完整流程

可以把这件事固定成四段：先定路径，再定类型，然后建索引，最后对齐查询。

一、锁定单一 JSON 路径

先确认索引服务的是哪个谓词，例如客户编号、订单金额或发生时间。不要为了“以后可能会用”而给整份 JSON 文档做宽泛设计。标量路径不存在时，JSON_VALUE 默认返回 SQL NULL；如果业务不能接受，应该显式选择 ERROR ON EMPTY 或给出同类型默认值。

二、按比较语义选择类型

类型选择决定索引中的排序和比较。订单金额若用字符串保存，'100' 可能排在 '20' 前面；改成 DECIMAL(12,2) 才是金额语义。纯数字 ID 没有负数时可用 UNSIGNED；业务编码则通常保留字符串。

三、在函数索引和生成列之间选择

函数索引更短，适合只为查询加速的单一表达式。它在内部仍以隐藏虚拟生成列实现。显式生成列更适合需要复用列名、查看转换结果或把类型契约写进表结构的场景：

-- 显式生成列便于检查类型结果，也能复用普通列名查询
ALTER TABLE orders
  ADD COLUMN customer_code VARCHAR(32)
    GENERATED ALWAYS AS (
      CAST(
        JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer_code'))
        AS CHAR(32)
      )
    ) STORED,
  ADD INDEX idx_customer_code (customer_code);

-- 查询生成列时不必重复 JSON 表达式
SELECT id, payload
FROM orders
WHERE customer_code = 'C1024';

若只需要索引而不需要保存生成值，可以改用虚拟生成列。选择 STORED 还是 VIRTUAL 属于写入成本、读取成本与维护方式的权衡，不改变“先定型再索引”的原则。

四、用 EXPLAIN 检查表达式是否命中

建完索引后，不要仅凭索引存在就判断成功。先把真实查询放进 EXPLAIN，查看 possible_keys 与 key。如果没命中，逐项比较 JSON 路径、RETURNING 或 CAST 类型、长度和排序规则：

-- 计划检查只验证优化器选择，不代表实际返回行数
EXPLAIN
SELECT id
FROM orders
WHERE JSON_VALUE(payload, '$.amount' RETURNING DECIMAL(12,2)) >= 100.00;

推荐流程：优先让类型契约显式可见

1. 从生产查询中选出一个高频等值或范围谓词，冻结 JSON 路径。
2. 根据业务含义选择 SQL 类型，不根据 JSON 文本长什么样猜类型。
3. 新写法优先考虑 JSON_VALUE(... RETURNING type)；已有 JSON_EXTRACT 代码则使用显式 CAST。
4. 字符串索引同时固定长度和排序规则；数值索引固定精度与符号。
5. 索引与查询复用同一表达式，再用 EXPLAIN 核对。

常见误区

• 只写 payload->>'$.name'：它得到 LONGTEXT，没有给函数索引提供可用的前缀边界。
• 索引 CAST 了，WHERE 没 CAST：字符串排序规则可能不同。官方示例中，CAST 默认排序规则与 JSON_UNQUOTE 的二进制排序规则不同，索引可能因此不被使用。
• 数字统一转 CHAR：这样得到的是字典序，不适合范围查询、金额和统计比较。
• 忽略异常值：路径缺失、对象或数组、非法数字和截断都会影响转换结果。先决定返回 NULL、默认值还是报错。
• 把 JSON 数组当标量：数组成员检索属于多值索引场景，不是本文讨论的单标量函数索引。

速查表

相关问题

CAST 是为了把 JSON 字符串截短吗

不只是截短。它同时声明 SQL 类型、最大长度和比较语义。长度应覆盖业务允许的最大值，过小会触发截断问题，过大则增加索引键空间。

JSON_VALUE 不写 RETURNING 可以吗

可以，但默认返回 VARCHAR(512)。对金额、整数、日期等字段，显式写 RETURNING 能避免字符串比较语义，也让表结构和查询意图更清楚。

字符串索引为什么还要关心 COLLATE

因为排序规则决定大小写、重音和二进制比较方式。索引表达式与 WHERE 的排序规则不一致时，即使文本看起来相同，也可能无法匹配同一个函数索引。

什么时候用生成列而不是函数索引

需要复用列名、查看转换结果、加入更多列约束，或希望类型契约在表结构中清晰可见时，用显式生成列更直观；只想为单一表达式加速时，函数索引更简洁。