MySQL JSON_SCHEMA_VALID 如何在入库前拒绝结构错误

如果 JSON 列只是“能解析”就放行，后续查询仍可能遇到字段缺失、类型漂移或数值越界。更稳妥的做法是把 JSON_SCHEMA_VALID(schema, document) 放进 CHECK 约束：合法文档返回 1，不符合 schema 的文档返回 0，INSERT 或 UPDATE 直接被数据库拒绝。

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

JSON_SCHEMA_VALID 负责判断结构是否符合规则，CHECK 负责把判断变成入库门禁；排查失败时，再用 JSON_SCHEMA_VALIDATION_REPORT 读取具体路径和失败关键字。

先把“合法 JSON”和“合法结构”分开

JSON_VALID() 只回答文本能不能作为 JSON 解析，例如 {"amount":"12"} 是合法 JSON，但它不保证 amount 必须是数字。JSON_SCHEMA_VALID() 的第一个参数是 schema，第二个参数是待检查文档，适合把字段类型、必填项和范围写成数据库能执行的规则。

这篇示例以订单扩展信息为例，约束三个事实：根节点必须是对象，order_id 是字符串，amount 是不小于 0 的数字，而且两个字段都不能缺失。MySQL 8.4 手册说明该函数支持 JSON Schema Draft 4；schema 本身必须是有效 JSON 对象。

把 JSON_SCHEMA_VALID 写进 CHECK 约束

schema 要直接写在约束表达式里，因为 MySQL 的 CHECK 约束不能引用用户变量。生产环境通常还会把 schema 放进迁移脚本，修改规则时同步评估旧数据是否都能通过。

-- 用 CHECK 把 JSON 结构规则绑定到表约束
CREATE TABLE order_extra (
    id BIGINT PRIMARY KEY,
    payload JSON NOT NULL,
    CONSTRAINT chk_order_extra_payload CHECK (
        JSON_SCHEMA_VALID(
            '{
              "type": "object",
              "properties": {
                "order_id": {"type": "string"},
                "amount": {"type": "number", "minimum": 0}
              },
              "required": ["order_id", "amount"]
            }',
            payload
        )
    )
);

这里的关键不是把 JSON 再存一份，而是让约束直接读取 payload。required 决定字段必须出现；type 与 minimum 决定出现后的值是否合规。若应用写入 {"order_id":"A-1001","amount":99.5}，约束判断通过；若缺少 amount 或写成负数，INSERT/UPDATE 会被拒绝。

用返回值和报告判断拒绝原因

在把规则接入表之前，可以先单独调用函数确认 schema 与样例的对应关系。下面的 SQL 同时保留中文注释，第一条返回 1，第二条返回 0。

-- 先用最小样例检查通过和失败两种分支
SELECT JSON_SCHEMA_VALID(
    '{"type":"object","properties":{"amount":{"type":"number","minimum":0}},"required":["amount"]}',
    '{"amount":18.8}'
) AS valid_document;

-- 负数违反 minimum，结果为 0；这里只判断，不写入表
SELECT JSON_SCHEMA_VALID(
    '{"type":"object","properties":{"amount":{"type":"number","minimum":0}},"required":["amount"]}',
    '{"amount":-2}'
) AS invalid_document;

如果只看到约束名，不知道是哪条规则失败，可在写入前使用报告函数：

-- 用报告函数把失败字段和 schema 规则一起返回
SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(
    '{"type":"object","properties":{"amount":{"type":"number","minimum":0}},"required":["amount"]}',
    '{"amount":-2}'
)) AS validation_report;

失败报告会包含 valid、reason、schema-location、document-location 和 schema-failed-keyword。例如 document-location 指向 #/amount，schema-failed-keyword 为 minimum，应用就能把模糊的“约束失败”转成明确的字段提示。真正触发 CHECK 失败后，也可以紧接着执行 SHOW WARNINGS 查看服务器给出的详细原因。

这个校验模式的边界

第一，NULL 不是“自动通过”：两个函数任一参数为 NULL 时返回 NULL，而 CHECK 的三值逻辑可能让设计者误判，因此 JSON 列通常应配合 NOT NULL，是否允许空值要单独定义。

第二，schema 不宜依赖外部文件或远程引用。MySQL 不支持 JSON Schema 的外部资源，使用 $ref 会失败；schema 应随数据库迁移脚本版本化。第三，JSON Schema 的 required 只表示字段必须存在，不等于字段值不能是 JSON null，需要按业务再设计 type 规则。

最后，记住这是一道数据边界，不是完整业务校验。金额精度、跨字段一致性、权限和库存状态，仍应由更适合的列约束、事务逻辑或应用服务负责。

常见问题

为什么 JSON_VALID 返回 1，CHECK 仍然失败？

因为 JSON_VALID 只检查语法，而 CHECK 中的 JSON_SCHEMA_VALID 还检查必填字段、类型和范围。先用报告函数读取失败关键字，再决定是修正文档还是调整 schema。

能不能把 schema 放在用户变量里复用？

普通 SELECT 可以使用变量；但 MySQL CHECK 约束不能引用变量，所以建表时应把 schema 作为表达式中的 JSON 字符串写入，并通过迁移脚本统一维护。