MySQL 怎样用 JSON Schema 检查文档结构

如果 MySQL 里的 JSON 字段“看起来像对象”却仍然不满足业务结构，先不要把 JSON_VALID() 当成 schema 校验。前者只关心文本是不是合法 JSON，本文要解决的是：怎样用 JSON_SCHEMA_VALID() 判断字段是否符合指定的属性、类型和必填项。

官方资料入口：https://dev.mysql.com/doc/refman/8.4/en/json-validation-functions.html。最关键的结论是：schema 和 document 都合法时，返回 1 表示通过，返回 0 表示结构不符合；NULL 或参数类型错误则要按另一条分支排查。

先确认 1 和 0 分别代表什么

先把校验范围压缩到一个坐标对象：两个字段必须存在，且纬度、经度都必须是数字。下面的 SQL 是可复制的说明示例，重点是观察返回值，不依赖某个业务表。

-- 用 required 保证字段存在，用 type 和范围约束字段值
SET @schema = '{
  "type": "object",
  "properties": {
    "latitude": {"type": "number", "minimum": -90, "maximum": 90},
    "longitude": {"type": "number", "minimum": -180, "maximum": 180}
  },
  "required": ["latitude", "longitude"]
}';

-- 两个字段存在且数值在范围内，返回 1
SET @good = '{"latitude": 31.23, "longitude": 121.47}';
SELECT JSON_SCHEMA_VALID(@schema, @good);

-- 缺少 latitude，结构不满足 required，返回 0
SET @missing = '{"longitude": 121.47}';
SELECT JSON_SCHEMA_VALID(@schema, @missing);

这两个结果最容易被误读：返回 0 并不代表 SQL 函数执行失败，而是 document 被成功解析后没有通过 schema。只有当你需要知道“哪个字段、哪个规则失败”时，才继续看报告函数。

结构不通过时，按规则逐层检查

我排查这类问题时，会先看顶层是不是对象，再看 required，最后才看数值范围或字符串模式。因为 schema 没有 required 时，只有 properties 的空对象可能仍然通过：它是合法对象，但没有被要求必须带某个属性。

-- 没有 required 时，空对象仍可能符合“类型是 object”的规则
SET @loose_schema = '{
  "type": "object",
  "properties": {"name": {"type": "string"}}
}';
SELECT JSON_SCHEMA_VALID(@loose_schema, '{}');

-- 增加 required 后，缺少 name 会返回 0
SET @strict_schema = '{
  "type": "object",
  "properties": {"name": {"type": "string"}},
  "required": ["name"]
}';
SELECT JSON_SCHEMA_VALID(@strict_schema, '{}');

如果字段存在但类型不对，或数值越过 minimum、maximum，也会得到 0。这时不要只看业务字段的 SQL 类型：JSON 文档内部的类型仍由 schema 的 type 决定。

别把 0、NULL 和错误混成一类

这是线上排障最容易误判的地方。两份参数都是合法 JSON、schema 也是对象时，0 是“校验完成但不通过”；只要一个参数是 NULL，结果就是 NULL；参数不是合法 JSON，则会抛出错误，而不是返回 0。schema 本身若不是 JSON 对象，也属于参数问题。

-- NULL 是未知值传播，不等同于结构不匹配
SELECT JSON_SCHEMA_VALID('{"type":"object"}', NULL);

-- 这是合法 JSON，但不是对象 schema，应按 schema 参数问题处理
SELECT JSON_SCHEMA_VALID('[]', '{}');

-- 这是非法 JSON，实际使用时会触发 JSON 参数错误
SELECT JSON_SCHEMA_VALID('{"type":"object"}', '{bad-json}');

因此应用层不要把函数结果简单转成布尔值后丢掉异常信息：先区分 1、0、NULL 和 SQL 错误，再决定是拒绝数据、补齐字段，还是修复调用方传入的字符串。

需要定位原因时，再读取验证报告

只需要放行或拒绝时，JSON_SCHEMA_VALID() 已经足够；需要返回接口错误详情时，可以使用同一组 schema 和 document 调用 JSON_SCHEMA_VALIDATION_REPORT()。报告会给出 reason、schema-location、document-location 和失败的 keyword，方便定位到 required 或 maximum。

-- 用报告函数定位失败路径，JSON_PRETTY 只负责可读展示
SET @bad = '{"latitude": 31.23, "longitude": 181}';
SELECT JSON_PRETTY(
  JSON_SCHEMA_VALIDATION_REPORT(@schema, @bad)
);

-- 先把 schema 规则定位清楚，再把简洁的 0/1 结果用于业务判断
SELECT JSON_SCHEMA_VALID(@schema, @bad);

报告中的 JSON Pointer 路径使用 #/longitude 这类形式，不要和 JSON_EXTRACT() 的 JSON path 写法混为一谈。若错误来自正则 pattern，还要注意 MySQL 文档提到的实现边界：无效 pattern 可能被静默忽略，不能只凭返回 1 就认为所有规则都经过了有效正则检查。

把稳定规则放进 CHECK 约束

当 schema 是表结构的一部分，而不是每次请求都变化的配置，可以把 JSON_SCHEMA_VALID() 放进 CHECK，让写入边界统一执行。约束里不能引用会话变量，所以 schema 需要内联；维护时应把它当作表结构变更来评审。

-- CHECK 中内联稳定 schema，写入不符合结构的 JSON 会被拒绝
CREATE TABLE geo_point (
  coordinate JSON,
  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
    )
  )
);

最终检查可以记成四句话：合法 JSON 但不符合结构看 0；缺少必填项先看 required；NULL 与异常单独处理；需要人类可读原因时再取报告。这样既能把 JSON 校验和普通 JSON 合法性区分开，也能避免把所有失败都归咎于数据库函数。

两个常见问题

为什么空对象返回 1？ 如果 schema 只有 properties 而没有 required，空对象可能满足“没有违反已声明属性规则”的条件。要强制字段出现，明确写入 required。

能不能用会话变量给 CHECK 提供 schema？ 不能依赖会话变量。CHECK 约束需要稳定表达式，schema 应直接写在约束表达式中，并在规则变化时按表结构变更处理。