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。这三条不能用同一个“空值”概念处理。

-- 用三条对象分别表示有值、路径缺失和显式 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 值转换到目标列类型时发生的错误。

-- 缺失 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 分工写清楚,才能既给可选字段提供稳定默认值,又不掩盖输入数据真正的缺陷。
-
501 收藏
-
501 收藏
-
501 收藏
-
501 收藏
-
501 收藏
-
490 收藏
-
273 收藏
-
171 收藏
-
428 收藏
-
204 收藏
-
497 收藏
-
242 收藏
-
211 收藏
-
486 收藏
-
304 收藏
-
385 收藏
-
197 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 立即学习 543次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 立即学习 516次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 立即学习 500次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 立即学习 485次学习