MySQL JSON_VALUE 怎样区分路径不存在和 JSON 空值

在 MySQL JSON 查询里，路径不存在和 JSON 文档里的 null 很容易都落成 SQL NULL，后续业务就无法判断“字段没传”还是“字段明确为空”。解决办法是先把三种输入放在同一组基线里，再分别利用 ON EMPTY、ON ERROR 和 JSON_CONTAINS_PATH() 拆开判断。

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

路径不存在由 ON EMPTY 处理，JSON null 会返回 SQL NULL；如果需要稳定区分两者，要么给缺失路径设置独有的默认哨兵值，要么额外判断路径是否存在。类型转换失败则属于 ON ERROR 分支。

先建立三种输入基线

排查这类问题时，我会先固定一个 JSON 文档，只改变被读取的路径。这样观察到的差异来自数据分支，而不是查询写法变化。下面的样例故意同时放入正常字符串、JSON null，并让第三个路径完全不存在。

-- 用同一份文档对照正常值、JSON null 和缺失路径
SET @doc = '{"name":"Lin","nickname":null}';

SELECT
  JSON_VALUE(@doc, '$.name') AS normal_value,
  JSON_VALUE(@doc, '$.nickname') AS json_null_value,
  JSON_VALUE(@doc, '$.missing') AS missing_value;

这组输入可以先整理成三个基线：

最后两行的结果相同，正是误判的来源。此时不要急着在外层用 IFNULL() 补一个业务值，因为它会把“缺失”和“明确为空”继续合并。

读懂 JSON_VALUE 的默认分支

JSON_VALUE(json_doc, path) 先按路径取值，再把结果转换为标量。没有额外配置时，返回类型是 VARCHAR(512)；路径没有数据时默认走 NULL ON EMPTY，转换或取到对象、数组等错误情况默认走 NULL ON ERROR。

两个子句解决的是两类不同问题：

• ON EMPTY：路径没有找到数据。
• ON ERROR：路径找到的内容不能按要求转换成目标标量，或者结果不满足标量约束。

写法顺序也有要求，ON EMPTY 必须在 ON ERROR 前面。把顺序写反，容易得到语法错误或兼容性警告。

-- 为缺失路径指定独有哨兵，为转换错误保留 SQL NULL
SELECT
  JSON_VALUE(
    @doc,
    '$.missing'
    RETURNING CHAR(32)
    DEFAULT '__MISSING__' ON EMPTY
    NULL ON ERROR
  ) AS missing_marker,
  JSON_VALUE(
    '{"age":"unknown"}',
    '$.age'
    RETURNING UNSIGNED
    NULL ON EMPTY
    DEFAULT 0 ON ERROR
  ) AS conversion_fallback;

这里的哨兵值只服务于“路径缺失”这个状态，应该选业务数据不可能自然出现的值。对于数值类型，不要把普通的 0 当成万能哨兵，因为 0 可能本来就是合法业务值。

用哨兵值区分路径缺失

如果查询只需要在结果层区分缺失和 JSON null，最短的做法是让 ON EMPTY 返回一个独有字符串，而 JSON null 继续保持 SQL NULL：

-- 缺失路径返回哨兵，JSON null 仍然保持 SQL NULL
SELECT
  CASE
    WHEN JSON_VALUE(
      @doc,
      '$.missing'
      RETURNING CHAR(32)
      DEFAULT '__MISSING__' ON EMPTY
      NULL ON ERROR
    ) = '__MISSING__' THEN 'missing'
    WHEN JSON_VALUE(
      @doc,
      '$.missing'
      RETURNING CHAR(32)
      DEFAULT '__MISSING__' ON EMPTY
      NULL ON ERROR
    ) IS NULL THEN 'json-null-or-error'
    ELSE 'present'
  END AS state;

这段写法适合展示规则，但在真实查询里不建议重复调用同一个表达式。可以先在派生表中得到一次结果，再由外层 CASE 判断，避免后续修改类型或路径时漏改其中一处。

-- 先提取一次，再在外层把哨兵、SQL NULL 和正常值映射成业务状态
SELECT
  CASE
    WHEN value_text = '__MISSING__' THEN 'missing'
    WHEN value_text IS NULL THEN 'json-null'
    ELSE 'present'
  END AS state,
  value_text
FROM (
  SELECT JSON_VALUE(
    @doc,
    '$.nickname'
    RETURNING CHAR(32)
    DEFAULT '__MISSING__' ON EMPTY
    NULL ON ERROR
  ) AS value_text
) AS extracted;

这里有一个容易忽略的边界：如果业务数据本身可能出现 __MISSING__，这个哨兵就不再唯一。此时应该改用更难与业务值冲突的编码约定，或者直接使用下一节的显式路径存在性判断。

用 JSON_CONTAINS_PATH 做显式判定

当字段值允许为空、缺失状态需要长期存储，或查询还要同时保留原始值时，我更倾向于把“路径是否存在”单独算出来。JSON_CONTAINS_PATH() 返回路径是否存在，JSON_VALUE() 负责取标量；两者的职责分开，业务状态更容易解释。

-- 用存在性标记先排除缺失，再用 JSON_VALUE 识别 JSON null
SELECT
  CASE
    WHEN JSON_CONTAINS_PATH(@doc, 'one', '$.missing') = 0 THEN 'missing'
    WHEN JSON_VALUE(@doc, '$.missing') IS NULL THEN 'json-null'
    ELSE 'present'
  END AS state;

-- 对已有路径重复同样的判定，结果会落到 json-null 或 present
SELECT
  CASE
    WHEN JSON_CONTAINS_PATH(@doc, 'one', '$.nickname') = 0 THEN 'missing'
    WHEN JSON_VALUE(@doc, '$.nickname') IS NULL THEN 'json-null'
    ELSE 'present'
  END AS state;

第一条查询的状态是 missing，第二条查询的状态是 json-null。如果把 nickname 改成 name，路径存在且取到普通字符串，就会得到 present。

类型转换错误要单独设计

路径存在并不等于转换一定成功。例如目标类型指定为 UNSIGNED，而 JSON 值是普通字符串或数组，就会进入转换错误分支。不要把这种情况和缺失路径共用同一个默认值，否则数据质量问题会被悄悄吞掉。

-- 对数值字段保留可识别的错误默认值，避免和合法 0 混淆
SELECT JSON_VALUE(
  '{"count":"not-a-number"}',
  '$.count'
  RETURNING SIGNED
  NULL ON EMPTY
  DEFAULT -1 ON ERROR
) AS count_value;

这个例子把转换失败映射为 -1，但是否适合业务要看字段范围。如果负数也是合法值，应该改为错误返回，或额外输出错误标记。另一个重要边界是：JSON 文档或 JSON path 本身不是合法 JSON 时，属于输入语法错误，不能指望 ON ERROR 把所有问题都变成默认值。

上线前用一张表核对分支

在提交查询前，我会把最小基线固定成下面这张判断表。它比只看一行 SQL 更容易发现默认值与业务含义冲突：

如果只是临时报表，哨兵值通常够用；如果状态会驱动写入、统计或接口返回，显式保存存在性标记更稳。关键不是选择哪一个函数，而是不要让路径缺失、JSON null 和类型错误共享同一个业务含义。

常见问题

为什么 JSON_VALUE 找不到路径时也是 NULL？

因为默认行为是 NULL ON EMPTY。这是设计上的宽松默认值，不代表路径真的存在并且值为 JSON null。

能不能只用 IFNULL 区分缺失和 JSON null？

不能。IFNULL 只看到最终的 SQL NULL，无法恢复它来自缺失路径还是 JSON null。应该使用 ON EMPTY 哨兵或 JSON_CONTAINS_PATH。

ON EMPTY 和 ON ERROR 可以调换顺序吗？

不建议。标准写法把 ON EMPTY 放在前面；调换顺序可能触发语法错误或兼容性警告。

把查询拆成“路径存在性”和“标量提取”两层后，JSON_VALUE 的结果就不再是一个含义模糊的 NULL。先用三类输入建立基线，再根据业务是否允许哨兵值选择最小实现，排查和上线都更容易复核。