MySQL JSON_TABLE 数值与日期字段的类型转换和截断边界

把 JSON 数据接入报表或临时查询时，JSON_TABLE() 的关键不是“能不能展开”，而是 COLUMNS 中声明的 SQL 类型会怎样接住 JSON 标量。数值列建议先确定精度，日期列明确目标类型；缺失字段交给 ON EMPTY，对象或数组误映射到标量交给 ON ERROR，而 DECIMAL 截断要另外看警告。

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

先把 JSON 标量映射成正确的 SQL 列类型

下面的例子只关注类型映射，不讨论嵌套数组。amount 进入 DECIMAL(10,2)，created_at 进入 DATE；路径存在且值可转换时，结果就是关系表中的两列。

SELECT jt.amount, jt.created_at
FROM JSON_TABLE(
  '[{"amount": 12.50, "created_at": "2026-10-27"}]',
  '$[*]' COLUMNS (
    amount DECIMAL(10,2) PATH '$.amount', -- 金额列固定两位小数
    created_at DATE PATH '$.created_at' -- 日期列只接收日期语义
  )
) AS jt;

这里的类型声明不是格式装饰。JSON_TABLE 先取出 JSON 值，再按目标列类型进行常规转换；因此输入字符串、数字和日期文本都应该按目标列的容忍范围设计。生产查询里不要让隐式转换替你决定金额精度。

把缺失字段和转换错误分开处理

路径找不到时触发 ON EMPTY；路径取到对象或数组，却要写进标量列，或者发生列类型转换错误时，才进入 ON ERROR。两者都不写时有默认行为，但对数据质量敏感的导入应显式写出策略。

SELECT jt.amount, jt.created_at
FROM JSON_TABLE(
  '[{"amount": 8.2}, {"amount": [1,2], "created_at": "bad"}]',
  '$[*]' COLUMNS (
    amount DECIMAL(10,2) PATH '$.amount'
      NULL ON EMPTY ERROR ON ERROR, -- 缺失金额置空，数组误入金额列直接报错
    created_at DATE PATH '$.created_at'
      NULL ON EMPTY NULL ON ERROR -- 缺失或无法转换时保留空值
  )
) AS jt;

两列可以采用不同策略：金额通常宁愿中止并修数据，日期字段则可能允许空值后续补齐。重要的是不要把“没有字段”和“字段存在但类型不对”写成同一个排障结论。

把截断告警和 ON ERROR 分开检查

例如 3.14159 写入 DECIMAL(10,2)，结果可能保留两位小数，同时产生截断警告。这个警告不是 ON ERROR 的替代分支；即使你为列写了 NULL ON ERROR，也应在需要严格精度时查看警告。

SELECT jt.amount
FROM JSON_TABLE(
  '[{"amount": 3.14159}]',
  '$[*]' COLUMNS (
    amount DECIMAL(10,2) PATH '$.amount' NULL ON ERROR -- 转换错误置空，不吞掉精度检查
  )
) AS jt;
SHOW WARNINGS; -- 检查 DECIMAL 精度缩减是否产生截断提示

如果业务要求金额不可悄悄改变，应把源字段精度、目标列精度和警告采集一起纳入导入检查，而不是只看查询是否返回了一行。

上线前的四项复核清单

这份清单适合放在数据导入 SQL 的评审说明里。先验证输入样本，再定列类型；先区分错误类别，再决定是否继续导入，排查成本会比事后追踪一批被静默转换的数据低得多。

相关问题

JSON_TABLE 的 ON ERROR 能消除所有转换告警吗？

不能。它主要处理对象、数组或转换失败等错误，精度截断可能仍以 warning 形式出现，需要单独查看。

缺失字段应该写 ON EMPTY 还是 ON ERROR？

字段不存在属于 ON EMPTY；字段存在但值无法按目标列保存，才属于 ON ERROR。

为什么日期列不建议完全依赖默认类型转换？

因为输入可能同时出现缺失、空字符串和格式错误。显式写出 DATE 列与异常策略，才能让结果和后续补数规则一致。