MySQL JSON_SCHEMA_VALID 怎么把 JSON 规则变成 CHECK 约束：校验结果与失败定位

订单服务把扩展字段存进 JSON 后，最容易出现的不是“JSON 解析失败”，而是字段能解析、业务却不符合约定：坐标少了一个键，金额变成字符串，或者数量悄悄越过上限。MySQL 8.4 可以把这层规则放进表级 CHECK，用 JSON_SCHEMA_VALID() 直接挡住不合格文档；需要解释失败原因时，再用 JSON_SCHEMA_VALIDATION_REPORT() 和 SHOW WARNINGS 补上诊断信息。

把 JSON Schema 内联到 CHECK 中负责“准入”，把 JSON_SCHEMA_VALIDATION_REPORT() 留给写入前诊断和测试；不要指望 CHECK 约束本身把完整错误报告返回给业务端。

先看一个会被数据库挡住的 JSON 文档

下面用订单收货坐标做例子。latitude 和 longitude 必须存在，类型为数字，并限制在真实经纬度范围内。Schema 直接写进 CHECK，这是 MySQL 的要求：约束表达式不能引用会话变量。

CREATE TABLE delivery_point (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    coordinate JSON NOT NULL,
    CONSTRAINT chk_delivery_coordinate 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_SCHEMA_VALID 接收 Schema 和 coordinate，结果交给 CHECK；结果在 valid / invalid 两条分支中决定是否允许写入。下面的配图只画这条链路，不把应用层函数或不存在的中间表塞进去。

为什么 CHECK 里应该放布尔校验，而不是错误报告

JSON_SCHEMA_VALID() 的职责很窄：文档符合 Schema 返回 1，不符合返回 0。参数为 NULL 时返回 NULL；Schema 或文档不是合法 JSON 时则直接报错。这种返回形态适合约束判断，却不适合作为面向用户的错误文案。

另一个容易混淆的函数是 JSON_SCHEMA_VALIDATION_REPORT()。它同样接收 Schema 和文档，但会返回 JSON 格式的校验报告，适合在写入前给接口层、测试脚本或管理后台展示具体原因。它不应替代表上的 CHECK，否则绕过应用层的写入仍然没有数据库兜底。

失败写入时，按这个顺序定位原因

先写一条合法文档，确认表和约束没有拼写问题：

INSERT INTO delivery_point (coordinate)
VALUES ('{"latitude":31.2304,"longitude":121.4737}');

再测试三个边界：缺少 longitude，把 latitude 写成 91，以及把数字改成字符串。

INSERT INTO delivery_point (coordinate)
VALUES ('{"latitude":31.2304}');

INSERT INTO delivery_point (coordinate)
VALUES ('{"latitude":91,"longitude":121.4737}');

INSERT INTO delivery_point (coordinate)
VALUES ('{"latitude":"31.2304","longitude":121.4737}');

写入被拒绝时，错误通常会先告诉你约束名，例如 Check constraint 'chk_delivery_coordinate' is violated。紧接着执行：

SHOW WARNINGS;

这样能把约束失败关联到更具体的校验提示。若你要在不写表的情况下查看报告，可以把同一份 Schema 和待写文档交给：

SELECT JSON_SCHEMA_VALIDATION_REPORT(
    '{"type":"object","required":["latitude","longitude"]}',
    '{"latitude":31.2304}'
);

注意别把报告函数放进 CHECK 期待它返回“错误列表”。约束需要稳定的真假判定，报告则适合诊断路径，两者分工清楚，线上接口才不会只能收到一条笼统的约束错误。

从应用层校验迁移到表约束，要补三项检查

先固定 Schema 的版本和约束名

Schema 是约束的一部分，后续修改要像改列定义一样走迁移脚本。显式命名 chk_delivery_coordinate，比让 MySQL 自动生成 delivery_point_chk_1 更方便监控和回滚。

把 NULL 和非法 JSON 单独测出来

coordinate 设为 NOT NULL 是一层保护，但函数本身对 NULL 的行为仍要在测试中确认。应用层传入文本时，还要区分“合法 JSON 但不符合 Schema”和“根本不是合法 JSON”这两类错误。

不要把正则校验当成绝对安全边界

MySQL 文档特别提醒，JSON Schema 中的无效正则模式可能被静默忽略。涉及编码、格式或安全输入时，应在数据库约束之外保留应用层校验，并为这个边界写回归用例。

最小验证清单

• 合法文档能插入，且 JSON_SCHEMA_VALID() 返回 1。
• 缺少必填字段、类型错误、数值越界各有一条测试数据。
• 失败写入后执行 SHOW WARNINGS，确认约束名和原因可追踪。
• 写入前用 JSON_SCHEMA_VALIDATION_REPORT() 验证接口能读到诊断报告。
• 迁移脚本明确写出 Schema、约束名和回滚动作。

相关问题

JSON_SCHEMA_VALID() 返回 0 时会自动返回字段级错误吗？

不会。它主要返回真假结果；字段级原因应使用 JSON_SCHEMA_VALIDATION_REPORT()，或在约束失败后查看 SHOW WARNINGS。

CHECK 约束里的 JSON Schema 可以写成会话变量吗？

不可以。MySQL 的 CHECK 表达式不能引用变量，所以 Schema 需要内联在约束定义中。

应用层已经校验 JSON，还需要 CHECK 吗？

建议保留。应用层适合返回友好提示，CHECK 负责兜住脚本、后台任务和其他写入入口。

把准入和诊断分开，迁移才稳

这次改造的关键不是把 JSON Schema 塞进数据库，而是让职责边界清楚：JSON_SCHEMA_VALID 和 CHECK 负责拒绝不合格数据，JSON_SCHEMA_VALIDATION_REPORT 与 SHOW WARNINGS 负责解释为什么拒绝。先用三类边界数据跑通，再把约束名、Schema 和回滚脚本纳入迁移记录，后续新增字段就不会变成一次无证据的线上试错。