MySQL JSON_TABLE 的 EXISTS PATH 怎么区分字段缺失和 JSON null

MySQL 用 JSON_TABLE 把半结构化数据转成关系列时，最容易误判的一点是：普通 PATH 列读到 NULL，并不能直接说明 JSON 中的字段不存在。字段缺失、字段明确写成 JSON null，都可能需要落成 SQL NULL。要保留这层差异，可以在同一个 JSON_TABLE 中增加 EXISTS PATH 列，先记录路径是否出现，再读取字段值。

下面以 MySQL 8.4 官方文档为基线，示例用于说明语义，未在本机执行，也不代表某个线上表的实际输出。

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

先把字段缺失和 JSON null 分成两个问题

假设业务要导入客户资料，JSON 对象里的 nickname 有三种输入：没有这个键、键存在但值是 JSON null、键存在且值为普通字符串。若只写一个 nickname VARCHAR(40) PATH '$.nickname'，前两种情况都可能落到 NULL，后续 SQL 就无法仅凭取值判断用户是“没有填写”还是“明确清空”。

这里要分开问两个问题：路径是否命中，以及命中后得到什么值。前者是布尔性质，后者是业务字段值。EXISTS PATH 只负责前一个问题，不能替代普通 PATH 列；两列配合才有完整信息。

用 EXISTS PATH 生成存在性标记

在 COLUMNS 中同时声明一列普通路径和一列存在性路径。存在性列可以使用整数类型承接 0 或 1；命名时建议把 _exists 或 _present 写进列名，避免把它误当成原始业务字段。

SELECT jt.customer_name, jt.nickname, jt.nickname_exists
FROM JSON_TABLE(
    '[{"name":"林一"},{"name":"周二","nickname":null},{"name":"吴三","nickname":"blue"}]',
    '$[*]' COLUMNS (
        customer_name VARCHAR(40) PATH '$.name',
        -- 普通 PATH 读取值；缺失或 JSON null 都可能得到 SQL NULL。
        nickname VARCHAR(40) PATH '$.nickname' NULL ON EMPTY NULL ON ERROR,
        -- EXISTS PATH 只记录路径是否出现：存在为 1，不存在为 0。
        nickname_exists INT EXISTS PATH '$.nickname'
    )
) AS jt;

这段查询的关键不是把 EXISTS PATH 当作过滤器，而是把它作为伴随字段输出。对第一行，nickname_exists=0；第二行路径存在，即使值是 JSON null，存在性仍为 1；第三行存在性为 1，普通取值是 blue。这三个字段组合起来，才足够支撑导入策略。

如果只想找“明确包含 nickname 的对象”，可以在外层使用 WHERE nickname_exists = 1。如果还要判断它是不是可用字符串，则继续检查取值列，不能把存在性为 1 直接当成非空字符串。

组合标记和取值解释三种状态

可以把结果按两列映射成下面的业务判断。表中的“SQL NULL”是关系结果中的空值，不等同于 JSON 文档里的字符串 "null"。

这是一种语义映射，不是强制的业务规则。例如补丁接口可能把第一行解释为“不修改”、第二行解释为“清空”、第三行解释为“更新”。全量导入也可以把两种 NULL 都转成默认值，但应该在应用层明确选择，而不是让 JSON_TABLE 的空值表现替你做决定。

把 ON EMPTY 和 ON ERROR 放在正确位置

ON EMPTY 处理路径没有找到目标的情况，ON ERROR 处理找到路径后发生的提取或类型转换异常。二者都属于普通 PATH 列的取值策略，不会改变 EXISTS PATH 对路径是否出现的判断。

SELECT jt.nickname, jt.nickname_exists
FROM JSON_TABLE(
    '[{"nickname":null},{"nickname":123},{"other":"x"}]',
    '$[*]' COLUMNS (
        -- 缺少 nickname 时给普通取值列 NULL；类型异常按业务选择处理。
        nickname VARCHAR(40) NULL ON EMPTY NULL ON ERROR,
        -- 该列独立记录路径是否存在，不能用 nickname 是否为 NULL 代替。
        nickname_exists INT EXISTS PATH '$.nickname'
    )
) AS jt;

如果项目需要把缺失字段变成固定字面量，可以在普通取值列使用 DEFAULT 处理；但这样会改变取值列，不能因此删除存在性列。对可疑输入使用 ERROR ON ERROR 还是容错，也应依据导入契约决定，不能把坏类型静默当成字段缺失。

用边界样本决定入库动作

上线前至少准备四组样本：字段完全缺失、字段为 JSON null、字段为正常字符串、字段存在但类型不符合预期。每组同时观察存在性列、取值列和最终写入动作。特别要确认补丁接口是否把“缺失”解释成不更新，而不是把所有 NULL 都覆盖到目标表。

最后记住三个边界：EXISTS PATH 判断的是路径是否出现，不负责判断值是否非空；普通 PATH 负责提取值，不足以单独判断字段是否缺失；ON EMPTY 与 ON ERROR 只改变取值异常的处理方式，不替代存在性标记。

EXISTS PATH 能判断 JSON null 吗？

它能判断路径是否出现。路径存在但值为 JSON null 时，存在性列仍表示路径命中；要知道值本身是什么，再结合普通 PATH 列解释。

为什么不用 JSON_EXTRACT 是否为 NULL 代替？

直接比较提取结果时容易把路径不存在、JSON null、SQL NULL 转换规则混在一起。JSON_TABLE 的存在性列把“路径是否出现”单独表达，查询意图更清楚。

字段缺失时应该保留旧值还是写默认值？

这取决于接口是补丁更新还是全量覆盖。先用存在性列区分输入，再在 UPDATE 或导入映射层决定动作，不要在取值层隐式决定业务语义。