MySQL JSON_VALUE 返回数字类型时如何避免字符串比较

MySQL 的 JSON_VALUE() 如果省略 RETURNING，默认返回 VARCHAR(512)。因此 JSON 里的分数、金额或数量即使看起来像数字，也可能先以字符串参与比较。解决办法是在取值处明确写出数值类型：整数用 UNSIGNED 或 SIGNED，带小数的金额用 DECIMAL，不要把类型判断留给隐式转换。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html

先把 JSON_VALUE 的返回类型固定成数字

假设订单扩展字段放在 payload 中，业务需要筛选积分不少于 100 的记录。下面两种写法的语义不同：第一种依赖字符结果，第二种在函数边界就确定数值类型。

-- 建立演示表，payload 中的 score 可能来自数字或数字字符串
CREATE TABLE order_extra (
  id BIGINT PRIMARY KEY,
  payload JSON NOT NULL
);

-- 默认结果是 VARCHAR(512)，不要让比较规则自行推断
SELECT id
FROM order_extra
WHERE JSON_VALUE(payload, '$.score') > '100';

-- 明确返回无符号整数，比较对象从这里开始就是数值
SELECT id
FROM order_extra
WHERE JSON_VALUE(payload, '$.score' RETURNING UNSIGNED) > 100;

-- 金额或比例保留小数位，使用与业务精度匹配的 DECIMAL
SELECT id
FROM order_extra
WHERE JSON_VALUE(payload, '$.discount' RETURNING DECIMAL(10, 2)) >= 9.50;

整数 ID、计数和非负分值可以使用 UNSIGNED；允许负数时使用 SIGNED；金额、折扣率等小数不要用浮点近似替代固定精度。若 JSON 存的是带引号的数字字符串，RETURNING 会把它转换到目标类型，真正需要关注的是非法字符和超出目标精度时的错误策略。

把缺失路径、JSON null 和转换错误分开处理

类型写对只是第一步，生产数据还会遇到字段不存在、值为 JSON null 或内容无法转换。三者都不能简单归为“没有值”：路径不存在由 ON EMPTY 决定，转换失败由 ON ERROR 决定，而 JSON null 会返回 SQL NULL。

-- 缺少 score 时返回 NULL，调用方可以继续区分“没有字段”
SELECT JSON_VALUE(
  payload,
  '$.score' RETURNING DECIMAL(10, 2)
  NULL ON EMPTY
  NULL ON ERROR
) AS score
FROM order_extra;

-- 关键业务字段缺失或格式错误时直接让语句失败，避免静默使用 0
SELECT JSON_VALUE(
  payload,
  '$.score' RETURNING UNSIGNED
  ERROR ON EMPTY
  ERROR ON ERROR
) AS score
FROM order_extra;

-- 只在业务确实定义了默认值时使用 DEFAULT，并保持默认值与类型一致
SELECT JSON_VALUE(
  payload,
  '$.retry_count' RETURNING UNSIGNED
  DEFAULT 0 ON EMPTY
  DEFAULT 0 ON ERROR
) AS retry_count
FROM order_extra;

建议先决定“字段缺失”和“字段损坏”是否应该进入同一业务分支，再选择 NULL、默认值或错误。不要用 DEFAULT 0 ON ERROR 掩盖金额字段的脏数据；那会把异常记录伪装成合法的零值。

让过滤、排序和索引使用同一类型表达式

如果 WHERE 里用数值返回，ORDER BY 却继续使用默认字符返回，页面会出现筛选正确但排序奇怪的错觉。把路径和返回类型写成同一份表达式，并在需要时建立函数索引。

-- 过滤与排序共用 DECIMAL 表达式，避免两处类型不一致
SELECT id,
       JSON_VALUE(payload, '$.discount' RETURNING DECIMAL(10, 2)) AS discount
FROM order_extra
WHERE JSON_VALUE(payload, '$.discount' RETURNING DECIMAL(10, 2)) >= 9.50
ORDER BY JSON_VALUE(payload, '$.discount' RETURNING DECIMAL(10, 2)) DESC;

-- 对稳定的 JSON 路径建立表达式索引，查询条件必须保持同样的类型
CREATE INDEX idx_order_score
  ON order_extra ((JSON_VALUE(payload, '$.score' RETURNING UNSIGNED)));

-- 检查优化器是否识别该表达式索引
EXPLAIN SELECT id
FROM order_extra
WHERE JSON_VALUE(payload, '$.score' RETURNING UNSIGNED) = 123;

表达式索引适合路径稳定、查询频繁且类型定义不会随业务变化的字段。若同一 JSON 键在不同记录中既表示整数又表示小数，应先统一数据契约，否则索引和查询类型即使写得一致，脏数据仍会造成转换错误或 NULL。

上线前检查这四个数字边界

最后可以用 JSON_TYPE() 抽样检查原始 JSON 的标量类型，再对负数、边界值、缺失键、JSON null 和非法字符串各准备一条样本。这样验证的是数据契约，而不是某一次查询恰好返回了期望结果。

常见问题

JSON_VALUE 默认返回的是 JSON 类型吗？

不是。省略 RETURNING 时默认是 VARCHAR(512)；只有显式指定 RETURNING JSON 或其他目标类型，返回语义才会改变。

金额字段应该用 UNSIGNED 还是 DECIMAL？

金额通常应使用与业务精度一致的 DECIMAL(p,s)。UNSIGNED 适合没有小数且不允许负数的计数类字段。

为什么 JSON null 不能用 DEFAULT ON EMPTY 兜底？

ON EMPTY 只处理路径不存在；JSON null 是路径存在但值为 null，最终会得到 SQL NULL。若业务要把它当默认值，应在外层明确使用 COALESCE，并确认这不会掩盖数据问题。