MySQL CHECK 与 NULL：生效规则的完整性约束教程

CHECK (amount >= 0) 已经写进建表语句，amount = NULL 却照样插入。这不是 MySQL 忽略了约束，而是这条约束没有表达“金额必须填写”。

在 MySQL 8.4 中，CHECK 拒绝计算结果为 FALSE 的记录；TRUE 和 UNKNOWN 都可以通过。NULL 参与普通比较通常得到 UNKNOWN。对可空字段写范围约束之前，必须先决定“未知值”在业务里是否允许存在。

用一张优惠规则表复现漏网记录

规则分为草稿和生效两种状态。草稿允许暂时不填金额、开始时间和结束时间；生效规则则必须三项齐全，且金额非负、结束时间晚于开始时间。

下面第一版结构只写了范围判断，使用 MySQL 8.4、InnoDB 和独立实验库：

CREATE DATABASE IF NOT EXISTS check_null_lab
    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE check_null_lab;

CREATE TABLE coupon_rule (
    id BIGINT NOT NULL,
    rule_state VARCHAR(10) NOT NULL,
    amount DECIMAL(10,2) NULL,
    starts_at DATETIME(6) NULL,
    ends_at DATETIME(6) NULL,
    PRIMARY KEY (id),
    CONSTRAINT ck_coupon_state
        CHECK (rule_state IN ('draft', 'active')),
    CONSTRAINT ck_coupon_amount
        CHECK (amount >= 0),
    CONSTRAINT ck_coupon_period
        CHECK (starts_at < ends_at)
) ENGINE=InnoDB;

INSERT INTO coupon_rule
    (id, rule_state, amount, starts_at, ends_at)
VALUES
    (1, 'draft', NULL, NULL, NULL),
    (2, 'active', NULL, NULL, NULL);

SELECT * FROM coupon_rule ORDER BY id;

两行都能入库。第一行符合草稿需求，第二行却是业务意义上的无效生效规则。数据库没有足够信息替你区分，因为它看到的金额判断、时间比较都是 UNKNOWN，而不是 FALSE。

再单独执行一条负金额写入：

-- 预期失败：金额比较明确为 FALSE。
INSERT INTO coupon_rule
    (id, rule_state, amount, starts_at, ends_at)
VALUES
    (3, 'active', -1.00,
     '2026-11-01 00:00:00', '2026-12-01 00:00:00');

这能证明范围约束确实在工作。只用负数测约束，很容易得到“校验完善”的错觉；缺失值和正常值边界必须单独测试。

WHERE 过滤与 CHECK 接受条件不能套同一个直觉

SELECT
    NULL >= 0 AS amount_test,
    NULL < TIMESTAMP('2026-12-01 00:00:00') AS period_test,
    NULL IS NOT NULL AS presence_test;

前两个结果是 NULL，最后一个是 0。IS NOT NULL 才明确把“缺失”变成 FALSE。

WHERE 只保留判断为 TRUE 的行，UNKNOWN 会被过滤掉；CHECK 则允许 UNKNOWN。两者都使用三值逻辑，但处理未知结果的规则不同。拿一条 WHERE amount >= 0 查不出空金额记录，不能推导出相同表达式放进 CHECK 就会拒绝这些记录。

如果字段在所有状态下都必须填写，最直接的设计通常是列上 NOT NULL，再用 CHECK 限制取值范围。但本例草稿确实需要不完整状态，直接把三列改成非空，会把合法草稿也赶出去。这里需要的是条件完整性约束。

让“生效必须完整”成为独立规则

新的判断可以写成：规则不是 active，或者三个字段都已填写。rule_state 本身非空且受状态约束限制，因此这个表达式不会靠未知状态蒙混过去。

先尝试添加它，暴露历史脏数据：

-- 预期失败：id=2 已经违反准备添加的约束。
ALTER TABLE coupon_rule
ADD CONSTRAINT ck_coupon_active_complete
CHECK (
    rule_state <> 'active'
    OR (
        amount IS NOT NULL
        AND starts_at IS NOT NULL
        AND ends_at IS NOT NULL
    )
);

不要为了让 ALTER 通过就把所有缺失金额填成零。零元优惠与金额未知不是同一条业务事实，随意回填会把缺失信息改造成看似有效的数据。

先查出问题范围：

SELECT id, rule_state, amount, starts_at, ends_at
FROM coupon_rule
WHERE rule_state = 'active'
  AND (
      amount IS NULL
      OR starts_at IS NULL
      OR ends_at IS NULL
  );

实验里可以将这条尚未完整的规则退回草稿。正式数据则应根据实际审批和生效记录决定补录、退回还是隔离，修正依据要能追溯。

UPDATE coupon_rule SET rule_state = 'draft' WHERE id = 2;

ALTER TABLE coupon_rule
ADD CONSTRAINT ck_coupon_active_complete
CHECK (
    rule_state <> 'active'
    OR (
        amount IS NOT NULL
        AND starts_at IS NOT NULL
        AND ends_at IS NOT NULL
    )
);

原有 ck_coupon_amount 和 ck_coupon_period 继续负责范围和先后关系，新约束负责激活时的完整性。职责分开以后，错误也更容易定位，不需要把所有规则揉成一条很长的表达式。

既测写入，也测状态转换

一条完整规则可以正常写入：

INSERT INTO coupon_rule
    (id, rule_state, amount, starts_at, ends_at)
VALUES
    (4, 'active', 0.00,
     '2026-11-01 00:00:00', '2026-12-01 00:00:00');

金额为零在本例被明确允许。如果业务要求正数，应该把原范围改成 amount > 0，不要指望字段非空顺便排除零。

接着尝试把不完整草稿直接激活：

-- 预期失败：id=1 仍未填写必要字段。
UPDATE coupon_rule SET rule_state = 'active' WHERE id = 1;

正确的转换应在同一条更新里写入完整状态：

UPDATE coupon_rule
SET amount = 20.00,
    starts_at = '2026-11-01 00:00:00',
    ends_at = '2026-12-01 00:00:00',
    rule_state = 'active'
WHERE id = 1;

SELECT id, rule_state, amount FROM coupon_rule ORDER BY id;

这个测试比只测新增更接近真实系统。许多约束漏洞出现在状态转换：新建时字段完整，后续编辑却把生效记录的金额清空，或者仅更新状态就绕过了应用入口的验证。

还要避免在导入任务里滥用 IGNORE：

INSERT IGNORE INTO coupon_rule
    (id, rule_state, amount, starts_at, ends_at)
VALUES (5, 'active', NULL, NULL, NULL);

SHOW WARNINGS;
SELECT * FROM coupon_rule WHERE id = 5;

违反 CHECK 的行可能以警告和跳过行的方式处理，任务没有抛出普通错误不代表这一行已经导入。批处理要核对警告与实际写入结果，不能只看客户端命令返回成功。

给已有表补约束，还要处理并发写入窗口

历史数据扫描完成到 ALTER 真正开始之间，旧接口仍可能继续写入不完整的 active 记录。扫描结果为零只是某个时间点的证据，不是后续写入的保证。迁移应先让所有写入入口遵守新规则，再修正历史数据，最后执行约束变更并核对实际定义。

如果 ALTER 因新出现的坏数据失败，回到同一条问题查询定位并处理，不要为了完成发布临时关闭约束。反复出现的新记录说明仍有遗漏的写入入口，例如批量导入、后台编辑或旧版本服务。数据库约束的价值正是让这些路径无法悄悄绕过规则。

应用也应把这种拒绝翻译成明确的业务反馈。用户激活规则时少填结束时间，返回“生效规则缺少结束时间”比把数据库异常直接展示出去更有用。自动重试同一份非法数据不会使它变合法，反而可能把表单错误放大成连续请求。

边界测试至少应该包含空草稿、零金额生效规则、负金额、相同起止时间、激活不完整草稿，以及对已生效记录清空字段。它们分别走不同判断路径，不能用一条正常新增测试代替。测试关注的是业务条件是否被表达完整，而不是数据库里出现了几个 CHECK 名称。

表结构里看得到，不等于约束正在执行

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, ENFORCED
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'coupon_rule';

SHOW CREATE TABLE coupon_rule;

核对约束是否为 ENFORCED。如果迁移脚本写成 NOT ENFORCED，规则只是保存了定义，没有替你拦截数据。另一个明确的版本边界是 MySQL 8.0.16：更早版本对有限 CHECK 语法可能只解析并忽略，不能拿 5.7 上“建表没报错”证明校验有效。本例面向 8.4，不把不同产品或旧版本的行为混用。

CHECK 也不适合表达“当前时间必须仍在有效期内”这样的持续状态。非确定性的 NOW() 不能随意放进 CHECK；即使某次写入时验证通过，时钟继续前进，也不会主动对所有旧行重新执行约束。有效期查询和状态任务仍有自己的责任。

跨行累计额度、另一张表的库存同样超出了这里的单行约束范围。CHECK 不允许用子查询去替代完整的并发控制。把能由单行数据确定的规则交给数据库，把需要事务协调的规则放在正确的写入边界，约束才不会变成一种虚假的安全感。

实验完成后：

DROP DATABASE check_null_lab;