MySQL CHECK 约束怎样限制 JSON 字段结构

如果 JSON 字段只要“能解析”还不够，还需要限制它必须是对象、包含指定键，并且键值符合类型与取值范围，可以把这些规则写进 MySQL 的 CHECK 约束。核心组合是：JSON_CONTAINS_PATH() 检查路径是否存在，JSON_TYPE() 检查 JSON 类型，JSON_EXTRACT() 取出值后再做范围或枚举判断。

官方地址：https://www.mysql.com/

先把 JSON 结构拆成四类条件

以订单事件为例，约束目标可以明确成四层：顶层必须是 JSON 对象；必须有 kind 和 version；kind 必须是字符串且只能取 invoice 或 refund；version 必须是整数并落在 1 到 3 之间。把自然语言拆开后，每一层都能对应一个可读的 SQL 表达式。

创建带命名 CHECK 的 JSON 表

下面的表只接受三种版本范围内的订单事件。payload 使用原生 JSON 类型，因此 MySQL 先负责 JSON 文档的语法合法性；命名的 CHECK 再负责本文关心的结构规则。

CREATE TABLE order_event (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    payload JSON NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT chk_order_event_shape CHECK (
        -- 顶层必须是对象，避免把数组或标量当成事件文档。
        JSON_TYPE(payload) = 'OBJECT'
        -- kind 与 version 都必须出现，缺失路径返回 0。
        AND JSON_CONTAINS_PATH(payload, 'all', '$.kind', '$.version')
        -- kind 先判类型，再限制为两个业务枚举值。
        AND JSON_TYPE(JSON_EXTRACT(payload, '$.kind')) = 'STRING'
        AND JSON_UNQUOTE(JSON_EXTRACT(payload, '$.kind')) IN ('invoice', 'refund')
        -- version 必须是 JSON 整数，并限制在当前支持的版本区间。
        AND JSON_TYPE(JSON_EXTRACT(payload, '$.version')) IN ('INTEGER', 'UNSIGNED INTEGER')
        AND CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.version')) AS UNSIGNED)
            BETWEEN 1 AND 3
    )
);

这里把 JSON_CONTAINS_PATH(..., 'all', ...) 放在类型和取值条件之前，是为了让“字段缺失”成为一个清晰的失败条件。JSON_EXTRACT() 返回的是 JSON 值，字符串字段需要再用 JSON_UNQUOTE() 去掉 JSON 字符串的引号后，才能和 SQL 字符串枚举比较。

示例使用了确定性的内置 JSON 函数和转换，没有引入变量、子查询或存储函数。MySQL 8.4 文档规定，CHECK 表达式必须遵守这些限制；约束名还需要在同一 schema 的 CHECK 命名空间内保持唯一。

按四种失败类型检查数据

不要只准备一条成功样本。结构约束最有价值的地方，是把常见的脏数据在写入边界挡住。下面四组 INSERT 分别对应合法对象、缺少字段、类型错误和枚举错误。

-- 合法样本：对象、必需字段、类型和取值都满足约束。
INSERT INTO order_event (payload)
VALUES ('{"kind":"invoice","version":1}');

-- 缺少 version：JSON 文档合法，但必需路径检查返回 0。
INSERT INTO order_event (payload)
VALUES ('{"kind":"invoice"}');

-- version 是字符串：字段存在，但 JSON_TYPE 不是整数类型。
INSERT INTO order_event (payload)
VALUES ('{"kind":"invoice","version":"1"}');

-- kind 不在枚举范围：类型正确，但业务取值不被接受。
INSERT INTO order_event (payload)
VALUES ('{"kind":"shipment","version":1}');

第二、三、四条数据会触发 CHECK 约束错误；它们不是“JSON 格式错误”，而是 JSON 已经能被解析，只是不符合表定义的业务结构。这个区分很重要：前者应排查输入序列化，后者应排查契约或调用方版本。

为什么必须同时理解 SQL NULL、JSON null 和缺失路径

MySQL CHECK 条件只要得到 TRUE 或 UNKNOWN 就可以通过，得到 FALSE 才会拒绝。因此，设计“字段必须存在”时不能只依赖某个可能返回 SQL NULL 的比较。本文的表把整列声明为 JSON NOT NULL，再用 JSON_CONTAINS_PATH(..., 'all', ...) 把缺失字段转换成明确的 0。

还要分清三种情况：

• SQL NULL：整列没有值，会受到 NOT NULL 约束。
• JSON null：文档里存在一个 JSON null 值，路径存在，但它不是字符串或整数。
• 缺失路径：对象中根本没有这个键，JSON_CONTAINS_PATH 会返回 0。

-- 用查询把缺失、JSON null 和正常值放在一起观察。
SELECT
    JSON_CONTAINS_PATH('{"kind":null}', 'all', '$.kind') AS has_null_kind,
    JSON_CONTAINS_PATH('{}', 'all', '$.kind') AS has_missing_kind,
    JSON_TYPE(JSON_EXTRACT('{"kind":"invoice"}', '$.kind')) AS normal_kind_type;

如果业务允许某个字段缺失，就不要把它放进 'all' 的必需路径列表；如果允许 JSON null，也要在类型条件中明确写出相应分支，而不是依赖 UNKNOWN 带来的偶然放行。

更新、REPLACE 和 IGNORE 也会触发约束

CHECK 不是只在首次 INSERT 时检查。MySQL 会在 INSERT、UPDATE、REPLACE、LOAD DATA 和 LOAD XML 等写入场景评估它。应用只验证创建请求而不验证更新请求，仍然可能在更新时收到约束错误。

-- 先取得一条合法记录，再用 UPDATE 演示同一条规则仍然生效。
UPDATE order_event
SET payload = '{"kind":"shipment","version":1}'
WHERE id = 1;

-- IGNORE 会把违反 CHECK 的行跳过并产生警告，不应被误当成成功写入。
INSERT IGNORE INTO order_event (payload)
VALUES ('{"kind":"invoice"}');

在事务型表中，正常写入失败时应由调用方处理错误并决定是否回滚；使用 IGNORE 时则必须读取警告或检查受影响行数，否则可能出现“接口返回成功，但坏行没有落库”的隐蔽问题。

部署后怎样确认约束真的存在

上线前后都可以从数据库元数据查看最终定义。SHOW CREATE TABLE 适合人工阅读完整建表语句，INFORMATION_SCHEMA.CHECK_CONSTRAINTS 适合被迁移检查脚本读取。它们用于确认部署结果，不替代应用层的业务测试。

-- 查看数据库最终保存的约束名称和表达式。
SHOW CREATE TABLE order_event;

-- 按 schema 查询 CHECK 表达式，便于迁移脚本对比目标状态。
SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE TABLE_NAME = 'order_event';

总结：让数据库守住最小结构契约

MySQL CHECK 约束限制 JSON 字段结构时，推荐按“对象类型 → 必需路径 → JSON 类型 → 枚举或范围”的顺序编写。原生 JSON 类型解决文档语法，CHECK 解决表级业务边界；NOT NULL 和明确的路径判断则避免 SQL NULL、JSON null 与缺失字段混在一起。

常见追问：CHECK 能不能直接验证完整 JSON Schema？ 可以在 MySQL 支持的 JSON Schema 函数和确定性约束范围内评估，但复杂 schema 会明显降低约束的可读性与迁移维护性；简单结构适合 CHECK，复杂校验应在应用层或专门的 schema 流程中保留更清晰的错误信息。

常见追问：为什么 JSON 列还需要 CHECK？ JSON 列主要保证文档合法，并不自动知道你的业务键、类型和枚举要求；CHECK 正好把最小业务契约下沉到数据库写入边界。

常见追问：只写 JSON_TYPE 是否足够？ 不够。类型正确不代表路径存在、值在允许范围内，也不代表多个字段之间满足业务关系。