MySQL CHECK 约束上线前清理不符合约束的旧数据的实现方法

给旧表补 MySQL CHECK 约束时，先不要直接执行强制变更。正确顺序是：把约束条件写成反向查询，找出并处理历史违规行；再用命名约束暂存规则，确认全表通过后切换为 ENFORCED。MySQL 8.4 在添加或启用强制约束时会检查已有数据，存在条件为 FALSE 的行就会拒绝修改。

先把 CHECK 条件变成可执行的违规查询

假设订单表需要保证金额不为负，并允许业务暂时保留未知金额：

-- 反向条件只筛出明确违反 amount >= 0 的旧行
SELECT id, amount, updated_at
FROM orders
WHERE amount 

这里的反向条件应与最终约束逐字对应。MySQL 的 CHECK 表达式为 TRUE 或 UNKNOWN 时通过，FALSE 才违反；因此如果规则是“金额必须非负”，amount 是主要清理条件，而 NULL 是否允许必须由业务另行决定。

修复旧数据时要先定规则，再动 UPDATE

负金额可能代表录入错误、退款冲正或尚未结算，不能一律改成 0。先把违规行分为“可由业务规则纠正”和“必须隔离复核”两组；更新时按主键范围分批，并写入审计表或变更单号。下面的示例只展示安全边界，实际值应来自业务确认：

-- 事务内按已确认的订单范围修复；生产环境应先备份并限批
START TRANSACTION;

UPDATE orders
SET amount = 0,
    updated_at = CURRENT_TIMESTAMP
WHERE id BETWEEN 10001 AND 11000
  AND amount 

如果不能可靠推导修复值，就把记录移入隔离表或标记为待人工处理，而不是为了让 DDL 通过而覆盖原始金额。每批完成后重新运行全量反向查询；结果为 0 才进入下一阶段。

先用 NOT ENFORCED 暂存命名约束

数据清理完成后，可以先把规则写入表结构，但暂不强制拦截：

-- 约束名固定，便于后续 ALTER、审计和回滚
ALTER TABLE orders
  ADD CONSTRAINT chk_orders_amount_nonnegative
  CHECK (amount >= 0) NOT ENFORCED;

-- 查看约束是否存在以及当前是否强制执行
SELECT CONSTRAINT_NAME, ENFORCED
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND TABLE_NAME = 'orders'
  AND CONSTRAINT_TYPE = 'CHECK';

NOT ENFORCED 适合把结构变更与业务清理拆开，但它不是数据校验的替代品。这个阶段仍要持续观察新写入是否出现 amount ，并确认应用层校验、导入脚本和补偿任务都已更新。

全表通过后再切换为 ENFORCED

上线前最后一次检查至少包含三项：反向查询为 0；应用和离线导入已停止制造违规值；约束名和状态与变更单一致。确认后执行：

-- 只有全表通过后才切换强制执行
ALTER TABLE orders
  ALTER CHECK chk_orders_amount_nonnegative ENFORCED;

-- 复核最终定义
SHOW CREATE TABLE orders;

常见问题

amount 为 NULL 会被 CHECK 拒绝吗？

不一定。MySQL 文档规定条件为 UNKNOWN 时通过，涉及 NULL 的表达式常会得到 UNKNOWN。若业务不允许 NULL，应额外使用 NOT NULL 或把规则明确写成同时排除 NULL 的条件。

可以直接 ADD CHECK 不清理旧数据吗？

可以添加非强制约束，但添加或启用强制约束时，已有违规行会使 ALTER 失败。先反向查询和清理，能把失败从上线 DDL 阶段提前到可控的数据阶段。

为什么一定要给约束命名？

命名后可以准确执行 ALTER CHECK chk_orders_amount_nonnegative ENFORCED 或按名称删除，避免自动生成名称在迁移脚本和回滚中难以追踪。

这套顺序的核心不是把旧数据“改到能过”，而是先明确违规语义、留下修复证据，再让数据库接管后续写入边界。