当前位置:首页 > 文章列表 > 数据库 > MySQL > MySQL JSON_TABLE 怎样处理缺失字段的 DEFAULT ON EMPTY

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

来源:17golang原创 2026-11-17 19:58:29 0浏览 收藏

如果 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删除
MySQL HANDLER READ 怎样按索引逐行读取表数据MySQL HANDLER READ 怎样按索引逐行读取表数据
上一篇
MySQL HANDLER READ 怎样按索引逐行读取表数据
下一篇
下一篇
暂无
查看更多
最新文章
查看更多
课程推荐
  • 前端进阶之JavaScript设计模式
    前端进阶之JavaScript设计模式
    设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
    543次学习
  • GO语言核心编程课程
    GO语言核心编程课程
    本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
    516次学习
  • 简单聊聊mysql8与网络通信
    简单聊聊mysql8与网络通信
    如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
    500次学习
  • JavaScript正则表达式基础与实战
    JavaScript正则表达式基础与实战
    在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
    487次学习
  • 从零制作响应式网站—Grid布局
    从零制作响应式网站—Grid布局
    本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
    485次学习
查看更多
AI推荐
  • PubMedQA数据集详解:生物医学问答基准、功能与应用指南
    PubMedQA
    深入了解PubMedQA生物医学问答数据集,涵盖其核心功能、使用方法及在临床决策、药物研发等场景的应用,助力提升NLP模型性能。
    1080次使用
  • H2O EvalGPT:开源LLM大模型评估与排行榜工具
    H2O EvalGPT
    H2O EvalGPT是H2O.ai推出的开源LLM评估平台,提供详细的大模型性能排行榜、行业特定基准测试及A/B测试功能,助您快速选择最适合项目的高性能大语言模型。
    1079次使用
  • LMArena是什么?伯克利AI模型评估平台使用指南与功能解析
    LMArena
    LMArena是加州大学伯克利分校推出的AI模型匿名评测平台。通过盲测投票机制,用户可对比不同大模型回答并生成实时排行榜,助力开发者优化模型及用户选择最佳AI工具。
    1244次使用
  • 斯坦福HELM:大语言模型Holistic Evaluation整体评估框架详解
    HELM
    深入了解斯坦福推出的HELM(Holistic Evaluation of Language Models)大模型评测体系。本文解析其核心功能、安装配置步骤及应用场景,涵盖准确性、公平性、鲁棒性等多维度指标,助力开发者全面优化语言模型性能。
    1184次使用
  • MMBench详解:多模态大模型基准测试、功能特点与使用指南
    MMBench
    MMBench是由上海人工智能实验室等机构联合推出的多模态基准测试平台,提供细粒度能力评估、大规模数据集及VLMEvalKit工具。本文详细介绍其核心功能、安装使用方法及应用场景,助力开发者全面评估多模态模型性能。
    1029次使用
微信登录更方便
  • 密码登录
  • 注册账号
登录即同意 用户协议 和 隐私政策
返回登录
  • 重置密码