MySQL JSON_VALUE 返回 NULL 时怎么区分缺少路径和空值

使用 MySQL 的 JSON_VALUE() 取字段时，返回 SQL NULL 并不能直接说明“路径不存在”。路径缺失和路径值为 JSON null 都会落到这个结果；如果值是空字符串，返回的则是空字符串，不是 SQL NULL。稳妥的做法是先用 JSON_CONTAINS_PATH() 判断路径，再用 JSON_TYPE(JSON_EXTRACT()) 识别 JSON 类型。

你可以搭配JSON_TYPE函数来同步判断目标路径的状态：如果JSON_VALUE返回NULL同时JSON_TYPE返回NULL，说明是指定的访问路径不存在；如果JSON_VALUE返回NULL但JSON_TYPE返回的是null类型标识，就说明路径是合法的，对应位置存的就是空值本身。

先把四种 NULL 场景分开

先准备一份包含 JSON null、空字符串和数字 0 的文档。missing 路径故意不写入，方便对照。

-- 这些字段分别模拟 JSON null、空字符串和数字 0
SET @doc = '{"name": null, "empty": "", "age": 0}';

-- 只观察 JSON_VALUE 的原始返回语义
SELECT
  JSON_VALUE(@doc, '$.missing') AS missing_path,
  JSON_VALUE(@doc, '$.name') AS json_null_value,
  JSON_VALUE(@doc, '$.empty') AS empty_string,
  JSON_VALUE(@doc, '$.age' RETURNING SIGNED) AS zero_value;

missing_path 和 json_null_value 都显示为 SQL NULL；empty_string 是长度为 0 的字符串，zero_value 是数字 0。若 @doc 本身是 SQL NULL，函数也会返回 SQL NULL。所以单独写 JSON_VALUE(...) IS NULL，只能说明结果为空，不能说明原因。

用 JSON_CONTAINS_PATH 判断路径是否存在

需要区分“没有这个键”和“有这个键但值为 null”时，先判断路径。one 表示多个路径中只要有一个存在就返回 1；这里每次只传一个路径。

-- 先排除输入文档本身为 SQL NULL 的情况
SELECT CASE
  WHEN @doc IS NULL THEN 'SQL NULL 文档'
  WHEN JSON_CONTAINS_PATH(@doc, 'one', '$.missing') = 0 THEN '缺失路径'
  WHEN JSON_TYPE(JSON_EXTRACT(@doc, '$.missing')) = 'NULL' THEN 'JSON null'
  ELSE '普通值'
END AS result;

-- 对存在的 name 路径再看 JSON 类型
SELECT
  JSON_CONTAINS_PATH(@doc, 'one', '$.name') AS path_exists,
  JSON_TYPE(JSON_EXTRACT(@doc, '$.name')) AS value_type;

对 $.missing，路径存在性是 0；对 $.name，路径存在性是 1，而类型是字符串 NULL。注意这里的字符串 'NULL' 是 JSON_TYPE() 对 JSON null literal 的描述，不是 SQL NULL。JSON_TYPE(JSON_EXTRACT(...)) 在路径不存在时本身才会返回 SQL NULL。

想继续只用 JSON_VALUE，可以给缺失路径设置哨兵

如果业务只需要区分“缺失”和“存在但为 JSON null”，可以把 ON EMPTY 改成一个约定好的字符串。路径缺失会命中哨兵；JSON null 仍按 JSON_VALUE 的规则返回 SQL NULL。

-- 哨兵只用于表示路径缺失，不要与真实订单状态混用
SELECT
  JSON_VALUE(@doc, '$.missing' RETURNING CHAR(16)
    DEFAULT '__MISSING__' ON EMPTY) AS missing_mark,
  JSON_VALUE(@doc, '$.name' RETURNING CHAR(16)
    DEFAULT '__MISSING__' ON EMPTY) AS null_mark,
  JSON_VALUE(@doc, '$.empty' RETURNING CHAR(16)
    DEFAULT '__MISSING__' ON EMPTY) AS empty_mark;

这个写法的结果分别是 __MISSING__、SQL NULL 和空字符串。若业务字段允许出现任意字符串，不要只依赖哨兵；使用前面的路径存在性查询，或把状态与值拆成两个返回列，避免真实值碰巧等于哨兵。

生产查询要留意 ON ERROR 和非法路径

ON EMPTY 只处理路径没有数据，不能把它当成错误兜底。提取对象或数组到标量、把非数字文本转换成 UNSIGNED、发生截断时，会进入 ON ERROR。如果希望错误尽早暴露，可以显式写 ERROR ON ERROR，不要让错误也静默变成 SQL NULL。

此外，JSON 文档或路径本身不合法时会直接报 SQL 错误；ON ERROR 不是非法 JSON 的通用捕获器。路径参数还应使用固定的字符串字面量，动态路径要在应用层做白名单映射，避免把用户输入直接拼进 JSON 路径。

常见问题

JSON_VALUE 返回 NULL，能只用 IS NULL 判断缺失吗？

不能。缺失路径、JSON null、SQL NULL 文档和部分 ON ERROR 场景都可能得到 SQL NULL，至少要增加路径存在性判断。

JSON_TYPE() 返回的 NULL 是 SQL NULL 还是 JSON null？

对 JSON null literal，返回字符串 'NULL'；对 SQL NULL 参数或不存在的提取结果，才是 SQL NULL。比较时要区分引号里的字符串。

什么时候适合使用 DEFAULT ... ON EMPTY？

当缺失路径需要映射成固定业务状态，且能保证哨兵值不可能是合法数据时适合。需要完整分类或字段值范围开放时，优先使用 JSON_CONTAINS_PATH() 加 JSON_TYPE()。