MySQL JSON_VALUE 怎样指定返回类型

MySQL 的 JSON_VALUE() 默认把路径上的标量返回为 VARCHAR(512)。如果业务字段实际是金额、数量、日期或明确的 JSON 值，应该在函数内部使用 RETURNING 指定类型，而不是等查询外层再猜测或隐式转换。

MySQL 官方地址：https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html

可以把它看成一个小型类型契约：JSON 文档和路径负责提供输入，RETURNING 决定结果类型，ON EMPTY 处理路径没有值，ON ERROR 处理对象、数组、非法值或截断。先分清这几类边界，后面的过滤、排序和写入才不会被字符串结果带偏。

先看默认返回类型为什么容易埋坑

不写 RETURNING 时，JSON_VALUE() 的结果类型是 VARCHAR(512)。这对展示文本很方便，但不适合直接表达数值大小或日期先后。例如 JSON 里保存的 quantity 是数字，查询结果如果仍按字符处理，后续比较就可能和读者预期不同。

-- 用同一份 JSON 对照默认字符结果和明确的数值结果
SELECT
  JSON_VALUE('{"quantity":12}', '$.quantity') AS default_text,
  JSON_VALUE('{"quantity":12}', '$.quantity' RETURNING UNSIGNED) AS quantity_number;

-- 明确转换金额，避免把金额字段交给外层隐式转换
SELECT JSON_VALUE('{"amount":"49.95"}', '$.amount'
  RETURNING DECIMAL(10,2)) AS amount_value;

选择类型时先问“下游怎样使用这个字段”：只展示的标签可以保留字符结果，需要计算的数量用 UNSIGNED 或 DECIMAL，日期筛选用 DATE 或 DATETIME。类型不是越严格越好，而是要和数据契约以及后续运算一致。

用 RETURNING 指定真正需要的类型

RETURNING 放在路径表达式后面，可以指定 FLOAT、DOUBLE、DECIMAL、SIGNED、UNSIGNED、DATE、TIME、DATETIME、YEAR、CHAR 或 JSON 等类型。对于金额，精度和小数位应由业务字段决定；对于计数器，使用无符号整数通常比字符结果更容易比较。

-- 数量用于范围过滤时，直接返回无符号整数
SELECT JSON_VALUE('{"quantity":"12"}', '$.quantity'
  RETURNING UNSIGNED) AS quantity_value;

-- 日期字段用于排序或时间范围查询时，返回日期类型
SELECT JSON_VALUE('{"published_on":"2026-10-15"}', '$.published_on'
  RETURNING DATE) AS published_date;

-- 需要保留 JSON 语义时使用 JSON，而不是把对象强行当成标量
SELECT JSON_VALUE('{"profile":{"level":3}}', '$.profile'
  RETURNING JSON) AS profile_value;

如果路径指向对象或数组，而目标类型又是标量，问题就不再是“应该选哪种数字类型”，而是结果形态与函数契约不匹配。此时应重新确认路径是否写到了叶子节点，或者是否应该使用其他 JSON 函数来处理复合值。

把 ON EMPTY 和 ON ERROR 分成两条决策线

路径不存在和路径存在但不能转换，是两种不同的数据问题。ON EMPTY 只处理路径没有数据的情况；ON ERROR 处理对象或数组无法按目标类型返回、转换失败以及截断等错误。两者的顺序也固定为先写 ON EMPTY，再写 ON ERROR。

-- 缺少折扣字段时按 0 返回；字段存在但无法转换时返回 NULL
SELECT JSON_VALUE(
  '{"price":"49.95"}', '$.discount'
  RETURNING DECIMAL(10,2)
  DEFAULT 0 ON EMPTY
  NULL ON ERROR
) AS discount_value;

-- 关键字段缺失或类型错误时都中断语句，适合数据契约必须完整的场景
SELECT JSON_VALUE(
  '{"quantity":"many"}', '$.quantity'
  RETURNING UNSIGNED
  ERROR ON EMPTY
  ERROR ON ERROR
) AS quantity_value;

默认行为是 NULL ON EMPTY 和 NULL ON ERROR。默认值适合“字段确实可选”的场景；必须保留异常的导入或审计流程可以选择 ERROR；不希望一条坏记录拖垮整批查询时，再考虑返回 NULL 并把异常数据单独统计。默认值的类型也要和 RETURNING 的目标类型匹配。

按业务负载选择类型策略

把返回类型放进整体查询设计里，通常有三种选择。读多写少、字段只是展示时，可以保留字符结果；需要比较、排序或聚合时，应尽早返回数值或时间类型；数据质量是硬约束时，则用 ERROR ON EMPTY 和 ERROR ON ERROR 让问题尽快暴露。三者的差别不是写法偏好，而是异常应该在哪里被发现。

如果查询结果还要写入关系表，最好让 RETURNING 类型和目标列类型一致，减少外层再次转换的机会。测试样例至少覆盖：字段存在且类型正确、字段缺失、值为空、值无法转换、路径指向对象或数组。这样才能知道 NULL、默认值和错误分别代表什么。

一张可复用的判断清单

遇到 JSON_VALUE 返回结果不符合预期时，可以按下面顺序判断：

1. 确认路径是否指向标量；如果指向对象或数组，先修正路径或改变函数选择。
2. 确认下游是展示、比较、聚合还是写入，并据此选择字符、数字、日期或 JSON 类型。
3. 为路径缺失单独决定 ON EMPTY，不要用 ON ERROR 代替。
4. 为非法值、对象数组和截断单独决定 ON ERROR，不要把所有异常默默归为默认值。
5. 用边界样例观察默认值、NULL 和报错分别落在哪个分支，再把策略写入数据契约。

相关问题

JSON_VALUE 不写 RETURNING 时是什么类型？ 默认返回 VARCHAR(512)；如果后面要做数值或日期运算，应显式指定返回类型。

ON EMPTY 和 ON ERROR 可以调换顺序吗？ 不可以。使用两者时，ON EMPTY 必须写在 ON ERROR 前面，否则会产生语法错误。

路径缺失和 JSON 中的 null 是一回事吗？ 不是。路径没有数据会触发 ON EMPTY；路径解析到 JSON null 时，函数返回 SQL NULL，应按业务语义区分处理。

为什么 RETURNING UNSIGNED 仍可能得到 NULL？ 可能是路径缺失、JSON null，或转换问题采用了 NULL ON ERROR。要定位原因，需要分别检查路径和错误策略。