MySQL JSON_VALUE 怎样给缺失路径提供默认值

MySQL 的 JSON_VALUE() 在指定路径不存在时，使用 DEFAULT value ON EMPTY 提供默认值。例如，读取用户资料中的可选昵称，可以写成 JSON_VALUE(profile, '$.nickname' RETURNING CHAR DEFAULT '未设置' ON EMPTY)。关键是把“路径没有数据”和“路径有数据但类型转换失败”分开处理。

缺失路径用 ON EMPTY，转换错误或截断用 ON ERROR；两者同时使用时，ON EMPTY 必须写在前面。JSON 文档或路径本身不合法时，函数仍会抛出 SQL 错误，不能靠默认值掩盖输入问题。

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

下面用一组订单扩展字段说明这条规则。示例只围绕缺失路径的返回策略展开，不把默认值当成数据清洗或权限控制的替代品。

先分清三种“没有拿到值”的情况

假设 JSON 文档只有 status 字段，没有 nickname。读取 $.nickname 时，路径没有匹配到数据，这正是 ON EMPTY 的触发条件。MySQL 文档中，未写处理子句时，ON EMPTY 默认返回 SQL NULL。

但下面两种情况不能混为一谈：

这一区分相当于给数据契约划边界：默认值只能覆盖“字段没出现”的约定场景，不能让坏 JSON、错路径或错误类型悄悄通过。

用 ON EMPTY 给缺失字段返回默认值

最小写法是把默认值放在 ON EMPTY 前面。下面的第二条记录没有 nickname，因此返回“未设置”；第一条记录有值，则返回原始昵称。

-- 只处理 nickname 路径不存在的情况，存在的值仍然原样返回
SELECT
    JSON_VALUE(profile, '$.nickname'
        RETURNING CHAR
        DEFAULT '未设置' ON EMPTY) AS nickname
FROM customer_profile;

-- 也可以直接用字面量演示缺失路径的兜底结果
SELECT JSON_VALUE(
    '{"status":"active"}',
    '$.nickname'
    RETURNING CHAR
    DEFAULT '未设置' ON EMPTY
) AS nickname;

如果没有明确写 RETURNING，JSON_VALUE() 的返回类型默认为 VARCHAR(512)。简单文本字段可以依赖这个默认类型；需要稳定参与比较、排序或计算时，建议显式声明返回类型，让默认值和实际值使用同一个数据类型。

用 RETURNING 固定默认值的类型

数字字段更适合显式写 RETURNING。例如订单 JSON 中的 retry_count 可能是可选字段，缺失时希望按 0 次处理。此时默认值也应写成数字 0，而不是字符串 '0'。

-- 缺失 retry_count 时返回无符号整数 0
SELECT JSON_VALUE(
    '{"order_id":1001}',
    '$.retry_count'
    RETURNING UNSIGNED
    DEFAULT 0 ON EMPTY
) AS retry_count;

-- 有字段时按 UNSIGNED 返回，默认值沿用同一类型
SELECT JSON_VALUE(
    '{"retry_count":3}',
    '$.retry_count'
    RETURNING UNSIGNED
    DEFAULT 0 ON EMPTY
) AS retry_count;

默认值的类型必须与 RETURNING 指定的结果类型匹配。金额、比例或日期字段也应在这里定下类型，例如 DECIMAL、DATE 或 DATETIME，不要等到外层再用一串隐式转换补救。

什么时候还要补 ON ERROR

ON EMPTY 只针对路径没有数据。如果路径存在但内容无法转换，例如 retry_count 是字符串 "many"，这属于转换错误，应使用 ON ERROR 单独处理。两条子句可以一起写，而且顺序不能交换。

-- 缺失时按 0 处理，存在但无法转成 UNSIGNED 时也按 0 处理
SELECT JSON_VALUE(
    '{"retry_count":"many"}',
    '$.retry_count'
    RETURNING UNSIGNED
    DEFAULT 0 ON EMPTY
    DEFAULT 0 ON ERROR
) AS retry_count;

-- 如果坏数据必须暴露出来，可以让转换错误直接中断语句
SELECT JSON_VALUE(
    '{"retry_count":"many"}',
    '$.retry_count'
    RETURNING UNSIGNED
    DEFAULT 0 ON EMPTY
    ERROR ON ERROR
) AS retry_count;

在生产查询里，不建议无条件把所有错误都变成默认值。对可选字段可以用 DEFAULT ... ON EMPTY，对确实允许降级的脏值才使用 DEFAULT ... ON ERROR；如果数据错误需要修复，应保留 ERROR ON ERROR，让问题进入日志或任务失败流程。

非法输入不属于默认值场景

MySQL 会检查 JSON 文档和路径表达式是否有效。文档内容不是合法 JSON，或者路径字符串本身不合法时，会直接产生 SQL 错误，不会触发 ON ERROR。因此，ON EMPTY 不能拿来掩盖上游拼接出的坏文档，ON ERROR 也不能替代参数绑定和输入校验。

-- 路径合法但字段不存在：允许走 ON EMPTY
SELECT JSON_VALUE(
    '{"status":"active"}',
    '$.nickname'
    RETURNING CHAR
    DEFAULT '未设置' ON EMPTY
) AS nickname;

-- 生产代码应把 JSON 文档作为参数绑定值，不要拼接不可信文本
-- 下面的 ? 代表应用驱动提供的绑定参数，路径仍应使用固定字符串
SELECT JSON_VALUE(
    ?,
    '$.nickname'
    RETURNING CHAR
    DEFAULT '未设置' ON EMPTY
) AS nickname;

如果路径来自可配置字段，先把它限制在应用允许的固定集合中，再传给查询层。JSON_VALUE() 的路径参数必须是字符串字面量，不能把任意用户输入当作路径规则。

把默认值策略写成可复查的查询契约

落地时可以按下面的顺序复查每个字段：先问字段缺失是不是正常，再决定 ON EMPTY 的默认值；再问字段出现但类型错误是否允许降级，决定 ON ERROR 是默认值还是错误；最后确认 RETURNING 和默认值的类型一致。

最后记住两个语法边界：ON EMPTY 必须写在 ON ERROR 前面；如果路径匹配到对象或数组等无法作为标量返回的结果，也会进入错误处理分支。这样写出的查询，默认值只保护明确的缺失字段，异常数据仍然可见、可追踪。

常见问题

缺失路径不写 ON EMPTY 会返回什么？ 默认返回 SQL NULL。

JSON null 会触发 DEFAULT ... ON EMPTY 吗？ 不会把它当成路径缺失；路径解析到 JSON null 时，JSON_VALUE() 返回 SQL NULL。

ON EMPTY 和 ON ERROR 能交换顺序吗？ 不能。若同时指定，必须先写 ON EMPTY，再写 ON ERROR。

为什么写了 ON ERROR 仍然报错？ 需要先排查 JSON 文档或路径是否本身无效；这类输入合法性错误不由 ON ERROR 兜底。