JSON_SCHEMA_VALID 怎样先判断结构

当 JSON 数据写入 MySQL 前需要先判断结构时，最小做法是调用 JSON_SCHEMA_VALID(schema, document)。它接收 JSON Schema 和待检查的 JSON 文档：符合规则返回 1，不符合返回 0；如果还需要知道哪个字段、哪个关键字失败，再换用 JSON_SCHEMA_VALIDATION_REPORT()。

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

先用 JSON_SCHEMA_VALID() 做结构闸门，再用报告函数解释失败原因。不要把返回 0 和参数错误混为一谈：NULL 会返回 NULL，非法 JSON 或不支持的 Schema 能力会直接报错。

先准备一个最小对象 Schema

下面的例子把坐标文档限定为对象，并要求 latitude、longitude 都是数字，同时限制它们的范围。Schema 本身必须是合法 JSON 对象，待检文档也必须是合法 JSON。

-- 用变量保存规则和待检文档，便于连续比较多个输入
SET @schema = '{
  "type": "object",
  "properties": {
    "latitude": {"type": "number", "minimum": -90, "maximum": 90},
    "longitude": {"type": "number", "minimum": -180, "maximum": 180}
  },
  "required": ["latitude", "longitude"]
}';

SET @document = '{"latitude": 31.23, "longitude": 121.47}';

-- 两个参数的顺序固定：先 Schema，再待校验的 document
SELECT JSON_SCHEMA_VALID(@schema, @document) AS is_valid;

这个查询的结果是 1。如果把 latitude 改成字符串，或者删掉 longitude，结果会变为 0。这里的 0 表示“文档没有通过 Schema”，并不等于 SQL 语句语法错误。

为什么缺字段会返回 0

排查时先看 Schema 是否真的写了 required。只有列在 required 数组中的属性才必须出现；仅写在 properties 中，通常只是声明“如果出现，应当满足什么规则”，不会自动要求该字段存在。

-- 当前 Schema 要求两个字段都存在，并且值必须是数字
SET @document = '{"latitude": 31.23}';

-- 缺少 required 中的 longitude，所以返回 0
SELECT JSON_SCHEMA_VALID(@schema, @document) AS missing_longitude;

-- 字段存在，但类型不对，同样返回 0
SET @document = '{"latitude": "31.23", "longitude": 121.47}';
SELECT JSON_SCHEMA_VALID(@schema, @document) AS wrong_type;

另一个容易忽略的点是范围条件。minimum 和 maximum 约束的是数字值；字段存在且类型正确，并不代表它一定在允许范围内。把 latitude 写成 95，也会得到 0。

先区分返回 0、NULL 和报错

遇到“校验没有通过”时，可以按参数状态分三层判断：

-- SQL NULL 不会得到 0，而是得到 NULL
SELECT JSON_SCHEMA_VALID(@schema, NULL) AS null_document;

-- 非法 JSON 会触发错误，而不是返回 FALSE
SELECT JSON_SCHEMA_VALID(@schema, '{"latitude": }') AS invalid_document;

-- Schema 必须是 JSON 对象；数组不能作为有效 Schema
SELECT JSON_SCHEMA_VALID('["type", "object"]', @document) AS invalid_schema;

MySQL 8.4 文档还特别说明，JSON Schema 使用外部资源引用时，$ref 会触发不支持错误。不要用换一个函数名的方式掩盖这个能力边界，应该把需要的规则展开到当前 Schema，或在应用层完成更复杂的组合。

返回 0 时怎样看到具体原因

布尔值适合做第一道判断，但不适合直接展示给排查人员。此时使用 JSON_SCHEMA_VALIDATION_REPORT()，它会返回 JSON 报告；失败时可重点读取 reason、schema-location、document-location 和 schema-failed-keyword。

-- 把经度故意写到 maximum 之外，观察报告中的位置和关键字
SET @document = '{"latitude": 31.23, "longitude": 210}';

-- JSON_PRETTY 只负责排版报告，便于日志或人工阅读
SELECT JSON_PRETTY(
  JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)
) AS validation_report;

典型报告会把失败关键字指出为 maximum，并给出文档位置 #/longitude。如果缺少必填字段，失败关键字会是 required，文档位置可能是根路径 #。这里使用的是 JSON Pointer 位置，不要和 JSON_EXTRACT() 使用的 JSON Path 语法混淆。

因此，推荐把两种函数分工：写入前或查询条件中用 JSON_SCHEMA_VALID() 得到简单真假；记录诊断信息、返回接口错误详情时用报告函数保留原因。

把固定规则放进 CHECK 约束

如果一张表的 JSON 列长期遵守同一套结构，可以把 Schema 直接写进 CHECK 约束。MySQL 的 CHECK 约束不能引用用户变量，所以 Schema 需要内联到表达式中。

-- 把固定的坐标规则放到写入闸门中
CREATE TABLE geo_point (
  coordinate JSON NOT NULL,
  CHECK (
    JSON_SCHEMA_VALID(
      '{
        "type": "object",
        "properties": {
          "latitude": {"type": "number", "minimum": -90, "maximum": 90},
          "longitude": {"type": "number", "minimum": -180, "maximum": 180}
        },
        "required": ["latitude", "longitude"]
      }',
      coordinate
    )
  )
);

-- 这个写入满足结构和范围条件
INSERT INTO geo_point (coordinate)
VALUES ('{"latitude": 31.23, "longitude": 121.47}');

-- 这个写入会违反 CHECK，因为 latitude 超过 maximum
INSERT INTO geo_point (coordinate)
VALUES ('{"latitude": 95, "longitude": 121.47}');

如果插入被拒绝，可以紧接着执行 SHOW WARNINGS 查看 MySQL 给出的约束错误与 Schema 失败原因。生产环境中要注意 Schema 的维护成本：规则更新时，表定义和已有数据的兼容关系都需要一起考虑。

排查时只记住这四个判断

1. 先确认参数顺序是 Schema 在前、document 在后。
2. 返回 0 时检查 required、类型和数值范围，不要立刻怀疑 SQL 语法。
3. 返回 NULL 说明至少有一个 SQL 参数为空；非法 JSON、非对象 Schema 和不支持的 $ref 则是错误路径。
4. 需要定位字段时使用 JSON_SCHEMA_VALIDATION_REPORT()，不要让调用方只看到一个无法行动的 FALSE。

相关问题

• 没有写 required 时，空对象为什么可能验证通过？
• JSON_SCHEMA_VALID() 和 JSON_SCHEMA_VALIDATION_REPORT() 什么时候分别使用？
• CHECK 约束中的 JSON Schema 为什么不能写用户变量？