MySQL JSON_TABLE 的 ON EMPTY 和 ON ERROR 怎么分别处理

JSON_TABLE 的两个子句处理的不是同一种异常：路径找不到值时看 ON EMPTY，找到值但它不能按列定义落地时看 ON ERROR。实际项目里可以让缺失字段使用默认值，同时让数组误写进整数列直接报错，避免把脏数据悄悄吞掉。

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

一、先分清 ON EMPTY 和 ON ERROR 的触发条件

可以把一个列定义看成两道检查。第一道是 JSON path 有没有匹配到值；没有匹配时触发 ON EMPTY。第二道是已经拿到值后，能不能放进列声明的标量类型；例如把数组放入 INT 列，或者把无法转换的文本写入数值列，这类情况才进入 ON ERROR。

两者的语法位置都跟在列的 PATH 后面：

-- 缺失时给默认值，类型转换异常时拒绝整条读取
amount DECIMAL(10,2) PATH '$.amount'
  DEFAULT '0.00' ON EMPTY
  ERROR ON ERROR

这里的 '0.00' 是 JSON 字符串字面量，MySQL 会先按 JSON 解析，再按 DECIMAL 的列规则处理。不要把“字段不存在”和“字段存在但格式错误”都写成同一个默认值。

二、按业务语义组合 NULL、DEFAULT 和 ERROR

三个动作分别表示三种决策：NULL 表示允许缺省，DEFAULT json_string 表示给出可解释的兜底，ERROR 表示这份文档不能继续进入结果集。可以先按下面的清单选：

一个订单导入例子可以把订单号设为强约束，把备注设为软约束：

-- order_id 缺失就失败；备注缺失或类型异常都转为空值
SELECT jt.order_id, jt.note
FROM JSON_TABLE(
  '[{"order_id":101},{"order_id":102,"note":["bad"]}]',
  '$[*]' COLUMNS(
    order_id BIGINT PATH '$.order_id' ERROR ON EMPTY ERROR ON ERROR,
    note VARCHAR(200) PATH '$.note' NULL ON EMPTY NULL ON ERROR
  )
) AS jt;

如果备注错误不能被容忍，把第二列改为 ERROR ON ERROR；如果业务要保留行并标记异常，则使用一个明确的默认 JSON 字符串，而不是在下游猜测空值含义。

三、用一组混合数据核对四种结果

排查时不要只拿一条“正常 JSON”验证。至少准备缺失、数字、字符串和数组四类输入，让每列的路径与目标类型都被覆盖：

-- 用四种输入观察缺失处理和类型转换处理的边界
SELECT *
FROM JSON_TABLE(
  '[{"id":1},{"id":2,"score":3},{"id":3,"score":"bad"},{"id":4,"score":[1,2]}]',
  '$[*]' COLUMNS(
    id INT PATH '$.id' ERROR ON EMPTY,
    score INT PATH '$.score' DEFAULT '0' ON EMPTY DEFAULT '99' ON ERROR
  )
) AS jt;

第1行缺少 score，所以使用 0；第2行按整数读取；第3、4行虽然有 score，但分别遇到转换失败和数组转标量，使用 99。这组数据能确认你设置的两个默认值确实分属不同分支。

四、JSON null、截断警告与语法迁移别混在一起

JSON 中明确写成 null，不等于 path 缺失；MySQL 8.4 的 JSON_TABLE 会把它返回为 SQL NULL。另外，数值写入较窄的列产生截断时，会独立发出 warning，不能简单理解为 ON ERROR 已经替你处理了所有数据质量问题。

语法顺序也要保持规范：先写 ON EMPTY，再写 ON ERROR。把 ON ERROR 放在前面属于 MySQL 已标记弃用的非标准写法，迁移老 SQL 时应一并调整，减少未来升级的兼容风险。

相关问题

ON EMPTY 和 ON ERROR 都不写时是什么结果？

两者默认都把对应列设为 SQL NULL，但缺失与转换失败的原因仍不同，排查时不能只看最终空值。

DEFAULT 的值可以直接写普通字符串吗？

它会先按 JSON 字符串解析；默认值必须是有效 JSON，之后还要满足目标列类型的转换规则。

为什么 ERROR ON ERROR 没拦住 JSON null？

JSON null 是明确存在的 JSON 值，MySQL 会按 SQL 标准映射为 NULL，它不是对象、数组或标量转换错误。