MySQL JSON_SCHEMA_VALID 怎样校验 JSON 文档

如果一列 JSON 既要保留灵活性，又不能接受字段缺失或数值越界，MySQL 的 JSON_SCHEMA_VALID() 可以直接把 JSON Schema 变成一次布尔判断。它返回 1 表示文档通过，返回 0 表示文档不符合规则；参数为 NULL 时返回 NULL，而不是把空值当成合法文档。

MySQL JSON_SCHEMA_VALID 先把 schema 和文档放到同一个判断点

我更建议先用用户变量做一个小实验，再决定是否写进表约束。下面的规则要求文档是对象，并且必须同时有 latitude 与 longitude；纬度范围是 -90 到 90，经度范围是 -180 到 180。

-- 准备一个符合 Draft 4 语义的坐标对象规则
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}';

-- 返回 1 代表这份文档通过 schema
SELECT JSON_SCHEMA_VALID(@schema, @document) AS is_valid;

这个函数的两个参数都必须是合法 JSON，schema 还必须是合法的 JSON 对象。写成数组、字符串，或者把 JSON 拼坏，问题就不再是“校验不通过”，而是输入本身触发错误。

用 1、0、NULL 和错误区分四种结果

实际排查时最容易混淆的是“文档不符合规则”和“函数没有拿到可解析的输入”。可以用下面的对照表快速判断：

-- 缺少 longitude：这是合法 JSON，但不满足 required，所以返回 0
SET @document = '{"latitude":31.23}';
SELECT JSON_SCHEMA_VALID(@schema, @document) AS is_valid;

-- NULL 不代表校验通过；它表示至少一个参数为空
SELECT JSON_SCHEMA_VALID(@schema, NULL) AS is_unknown;

-- 需要注意：下面的字符串不是合法 JSON，函数会报错而不是返回 0
SELECT JSON_SCHEMA_VALID(@schema, '{"latitude":31.23') AS malformed_input;

生产代码里不要把 NULL 静默转换成 0 或 1。若业务要求“必须有 JSON”，应先对列本身设定 NOT NULL，再用 schema 约束内容；这样输入为空和内容不合规是两层不同的错误。

用验证报告定位失败字段，再决定是否加 CHECK

只返回 0 适合快速筛选，定位问题则调用 JSON_SCHEMA_VALIDATION_REPORT()。失败报告会给出 reason、schema-location、document-location 和失败的 schema 关键字。

-- 让经度越过 maximum，观察报告中的文档路径和规则路径
SET @document = '{"latitude":31.23,"longitude":181}';

-- JSON_PRETTY 只负责让报告更容易阅读
SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)) AS report;

如果报告指出 document-location 是 #/longitude，而 schema-location 是 #/properties/longitude，就能直接知道是哪个字段撞上了哪条规则。这个报告比在应用层重新遍历 JSON 更适合做日志和接口错误提示。

确认规则稳定后，可以把 schema 内联到 CHECK 约束。MySQL 的 CHECK 约束不能引用变量，所以这里不能写 @schema：

-- 把规则内联到 CHECK，让写入阶段拒绝越界坐标
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
    )
  )
);

-- 这条写入会触发 CHECK 失败；SHOW WARNINGS 可查看更具体的 schema 原因
INSERT INTO geo_point (coordinate) VALUES ('{"latitude":91,"longitude":0}');
SHOW WARNINGS;

这套校验规则的适用边界

MySQL 文档说明这里采用 JSON Schema Draft 4 语义，支持 required、type 等属性，但不是把所有新版 JSON Schema 关键字都原样搬进数据库。外部资源不受支持，使用 $ref 会失败；正则 pattern 的非法表达式还可能被静默忽略，不能把它当成严格的正则审计器。

实践中我会把规则分两层：数据库只守住跨服务都必须成立的结构和值域，复杂的业务条件仍放在应用层，并把验证报告记录下来。这样既避免 JSON 列退化成无约束文本，也不会把每次业务变更都变成一条难维护的超长 CHECK。

常见问题

JSON_SCHEMA_VALID 返回 0，是 JSON 格式错误吗？

不是。返回 0 通常表示 JSON 本身可解析，但缺字段、类型不匹配或数值越过 schema 边界；非法 JSON 会直接报错。

为什么不直接用 JSON_VALID？

JSON_VALID() 只回答“能不能解析”，不回答字段是否存在、类型是否正确或数值是否在范围内。两者可以组合，但职责不同。