登录
推荐 文章 Go 技术 课程 下载 专题 AI
首页 >  数据库 >  MySQL

MySQL JSON_TABLE 怎样处理缺失字段的 DEFAULT ON EMPTY

来源:17golang原创

时间:2026-11-17 19:58:29 234浏览 收藏

如果 JSON_TABLE() 的 JSON 路径没有找到字段,想让结果列出现固定值,写在 PATH 列后面的 DEFAULT json_string ON EMPTY 就是正确入口。它只处理“路径缺失”,不负责把显式 JSON null 变成默认值,也不替代 ON ERROR 的类型错误处理。

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

先记住这条判断:有值就按列类型转换,路径缺失才进入 ON EMPTY,对象或数组写入标量、类型转换失败则进入 ON ERROR。下面用一组小查询把三种情况拆开。

先分清路径缺失和 JSON null

假设输入数组里有三类对象:第一条有 a,第二条没有 a,第三条明确写了 "a": null。这三条不能用同一个“空值”概念处理。

MySQL JSON_TABLE PATH 列对有值、路径缺失和 JSON null 的静态分流说明图
图1:JSON_TABLE PATH 列遇到有值、路径缺失和显式 JSON null 时的静态分流示意图。
-- 用三条对象分别表示有值、路径缺失和显式 JSON null
SELECT *
FROM JSON_TABLE(
    '[{"a": "ready"}, {}, {"a": null}]',
    '$[*]' COLUMNS (
        value_text VARCHAR(20) PATH '$.a' DEFAULT 'missing' ON EMPTY
    )
) AS jt;

第一行会得到 ready;第二行因为 $.a 没有匹配路径,会得到默认值 missing;第三行的路径存在但值是 JSON null,结果按 MySQL 的 JSON_TABLE 语义变成 SQL NULL,不会套用 DEFAULT ON EMPTY。

在 PATH 列上声明 DEFAULT ON EMPTY

语法结构是 name type PATH string_path DEFAULT json_string ON EMPTY。默认值参数是 JSON 字符串,会先按 JSON 解析,再受目标列类型规则约束。因此字符串默认值要写成合法 JSON 字符串,例如 SQL 文本中的 'unknown' 解析后是 JSON 字符串;数字默认值则可以写成 0。

-- 缺少 level 时填充字符串,缺少 retry_count 时填充数字
SELECT *
FROM JSON_TABLE(
    '[{"name":"api"}, {"name":"worker","retry_count":2}]',
    '$[*]' COLUMNS (
        service_name VARCHAR(30) PATH '$.name' DEFAULT 'unknown' ON EMPTY,
        retry_count INT PATH '$.retry_count' DEFAULT 0 ON EMPTY
    )
) AS jt;

这里的默认值服务于“输入字段没有提供”的情况。它不是对所有 NULL 做替换,也不是在 SQL 输出阶段再执行一次 COALESCE()。把默认值放进 JSON_TABLE(),可以让半结构化输入在转成关系列时就完成一部分契约收敛。

DEFAULT 的值仍然要服从目标列类型

DEFAULT ON EMPTY 并不会绕过列类型。目标列声明为 INT 时,默认值最终也要能按整数列规则保存;目标列声明为 VARCHAR 时,则按字符串列规则处理。设计默认值时,先问“结果列想表达什么”,再决定 JSON 字面量。

-- 默认值写成 JSON 数字,目标列为 INT,语义比字符串更明确
SELECT *
FROM JSON_TABLE(
    '[{"name":"api"}]',
    '$[*]' COLUMNS (
        timeout_ms INT PATH '$.timeout_ms' DEFAULT 3000 ON EMPTY
    )
) AS jt;

-- JSON 列可以接收一个 JSON 对象作为缺失字段的默认结构
SELECT *
FROM JSON_TABLE(
    '[{"name":"api"}]',
    '$[*]' COLUMNS (
        options JSON PATH '$.options' DEFAULT '{"mode":"safe"}' ON EMPTY
    )
) AS jt;

如果默认 JSON 字符串本身不是合法 JSON,或者默认值经过类型转换仍不符合目标列要求,就不应把问题归类为“字段缺失”。这时需要回到列类型和错误策略一起调整。

把 ON EMPTY 与 ON ERROR 分开

最容易误用的地方是把两种异常写成同一套规则。ON EMPTY 的触发条件是路径没有找到值;ON ERROR 处理的是保存对象或数组、以及 JSON 值转换到目标列类型时发生的错误。

MySQL JSON_TABLE PATH 列定义包含类型、ON EMPTY 和 ON ERROR 的静态关系图
图2:把列类型、PATH、ON EMPTY 和 ON ERROR 放在同一个列定义契约中的静态说明图。
-- 缺失 a 时用 111;a 是数组而不能转成 VARCHAR 时用 999
SELECT *
FROM JSON_TABLE(
    '[{"b":1}, {"a":[1,2]}]',
    '$[*]' COLUMNS (
        a_text VARCHAR(20) PATH '$.a'
            DEFAULT '111' ON EMPTY
            DEFAULT '999' ON ERROR
    )
) AS jt;

第一条输入触发 ON EMPTY,得到 111;第二条输入找到了 a,但数组不能直接保存成这个标量列,于是进入 ON ERROR,得到 999。两者都写默认值时,最好让它们表达不同的含义,方便后续排查。

需要知道字段是否出现时,用 EXISTS PATH

如果业务关心的是“字段是否提供”,不要拿默认值是否等于某个字符串来反推。EXISTS PATH 会在路径有数据时返回 1,否则返回 0,适合和实际值列并排输出。

-- value_text 表示值,has_value 表示路径是否出现,两者职责分开
SELECT *
FROM JSON_TABLE(
    '[{"a":"ready"}, {}, {"a":null}]',
    '$[*]' COLUMNS (
        value_text VARCHAR(20) PATH '$.a' DEFAULT 'missing' ON EMPTY,
        has_value INT EXISTS PATH '$.a'
    )
) AS jt;

这样可以区分“缺失后填了默认值”和“原始字段确实存在但值为空”。如果还要区分 JSON null 与普通字符串,继续保留原始 JSON 或增加单独的业务标记,不要把默认值当作审计证据。

按字段语义选择 NULL、DEFAULT 还是 ERROR

输入情况适合的策略原因
字段可选,缺失代表未知NULL ON EMPTY保留“未知”语义,不伪造业务值
字段缺失有明确业务默认值DEFAULT ... ON EMPTY在 JSON 转关系列时完成规范化
字段缺失说明输入不完整ERROR ON EMPTY尽早暴露不满足契约的输入
字段存在但类型可能错误ON ERROR单独定义对象、数组或转换失败的处理

生产查询中不要为了“让结果总有值”而统一写默认值。默认值应该代表可以接受的业务含义,否则会把上游数据缺陷隐藏在正常结果里。

常见问题

DEFAULT ON EMPTY 能处理 JSON null 吗?

不能把两者等同。路径缺失才触发 ON EMPTY;路径存在且值为 JSON null 时,JSON_TABLE 返回 SQL NULL。

默认值为什么要写成 JSON 字符串?

因为语法要求 DEFAULT json_string,MySQL 会先解析这个 JSON 字符串,再按列类型保存。字符串、数字和对象默认值的写法要分别符合 JSON 格式。

能不能只写 ON ERROR 不写 ON EMPTY?

可以。两种子句分别有默认行为,但建议在字段契约很重要的查询里显式写出策略,让缺失字段和转换错误的意图清楚可读。

总结

JSON_TABLE() 的 DEFAULT ON EMPTY 解决的是“路径找不到”的问题:它不会替换显式 JSON null,也不会接管对象、数组或类型转换错误。把 ON EMPTY、ON ERROR 和 EXISTS PATH 分工写清楚,才能既给可选字段提供稳定默认值,又不掩盖输入数据真正的缺陷。

声明:本文转载于:17golang原创 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>