CHECK 约束：8.0.16 才真正可用的数据校验

CHECK 约束：8.0.16 才真正可用的数据校验

8.0.16 之前 MySQL 会"解析但忽略" CHECK 约束——
这是个大坑。这篇讲现在怎么用。

一、历史：被忽略的 CHECK

8.0.16 之前：

CREATETABLEt(ageINT,CHECK(age>=0));INSERTINTOtVALUES(-1);-- 成功了！约束完全没生效

MySQL 能解析 CHECK 语法（为了兼容其他数据库），
但完全不执行。这是官方文档明确说明的行为。

所以很多从其他数据库迁过来的 DDL，
CHECK 约束是"静默失效"的。

8.0.16 起才真正支持。

SELECTVERSION();-- 必须 ≥ 8.0.16

二、基本用法

2.1 列级约束

CREATETABLEproducts(idINTPRIMARYKEY,nameVARCHAR(100)NOTNULL,priceDECIMAL(10,2)CHECK(price>=0),discountDECIMAL(3,2)CHECK(discountBETWEEN0AND1),stockINTCHECK(stock>=0));

INSERTINTOproductsVALUES(1,'x',-10,0.1,5);-- ERROR 3819: Check constraint 'products_chk_1' is violated

2.2 表级约束（推荐，可以命名）

CREATETABLEproducts(idINTPRIMARYKEY,priceDECIMAL(10,2),costDECIMAL(10,2),stockINT,created_atDATETIME,CONSTRAINTchk_priceCHECK(price>=0),CONSTRAINTchk_profitCHECK(price>=cost),CONSTRAINTchk_stockCHECK(stock>=0));

给约束起名字很重要——报错信息里会显示，
方便定位是哪个约束拦住了。

2.3 已有表上加约束

ALTERTABLEproductsADDCONSTRAINTchk_priceCHECK(price>=0);

注意：加约束时会检查已有数据。

-- 如果表里有 price < 0 的数据ALTERTABLEproductsADDCONSTRAINTchk_priceCHECK(price>=0);-- ERROR 3819: Check constraint 'chk_price' is violated

先清理脏数据：

UPDATEproductsSETprice=0WHEREprice<0;-- 或者DELETEFROMproductsWHEREprice<0;

2.4 删除约束

ALTERTABLEproductsDROPCHECKchk_price;ALTERTABLEproductsDROPCONSTRAINTchk_price;-- 等价

三、能写什么条件

3.1 支持的

-- 比较CHECK(age>=0)CHECK(price<10000)-- BETWEENCHECK(scoreBETWEEN0AND100)-- INCHECK(statusIN('a','b','c'))-- LIKECHECK(emailLIKE'%@%')-- IS NULL / IS NOT NULLCHECK(deleted_atISNULLORdeleted_byISNOTNULL)-- 逻辑运算CHECK(a>0ANDb>0)CHECK(NOT(a=b))-- 多列关联CHECK(end_date>start_date)-- 简单函数CHECK(CHAR_LENGTH(name)>=2)

3.2 不支持的

-- ❌ 子查询CHECK(idIN(SELECTidFROMother_table))-- ❌ 不确定函数CHECK(created_at<NOW())CHECK(YEAR(created_at)=YEAR(CURDATE()))-- ❌ 用户变量、存储过程、UDFCHECK(@x>0)-- ❌ 引用其他表的列CHECK(other_table.col>0)-- ❌ AUTO_INCREMENT 列上建约束（部分情况）

原因：CHECK 必须是确定性的，
因为要在每次写入时求值。

四、查看约束

-- 方式 1SHOWCREATETABLEproducts;-- 方式 2SELECTconstraint_name,check_clauseFROMinformation_schema.check_constraintsWHEREconstraint_schema='mydb';-- 方式 3SELECTconstraint_name,table_name,constraint_typeFROMinformation_schema.table_constraintsWHEREconstraint_type='CHECK'ANDconstraint_schema='mydb';

五、CHECK vs 其它约束

CHECK 的定位：单行内的业务规则校验。

跨行/跨表的规则要靠：

• 外键
• 触发器
• 应用层

六、典型应用场景

6.1 数值范围

CONSTRAINTchk_scoreCHECK(scoreBETWEEN0AND100),CONSTRAINTchk_qtyCHECK(quantity>0),CONSTRAINTchk_rateCHECK(rate>=0ANDrate<=1)

6.2 枚举值校验

CONSTRAINTchk_statusCHECK(statusIN('pending','paid','done'))

这是 ENUM 的一个替代方案，加新值时不用改列类型。

6.3 时间先后

CONSTRAINTchk_timeCHECK(end_time>start_time)

6.4 互斥字段

-- 二选一，不能都填也不能都不填CONSTRAINTchk_phone_or_emailCHECK((phoneISNOTNULLANDemailISNULL)OR(phoneISNULLANDemailISNOTNULL))

6.5 格式校验（简单）

CONSTRAINTchk_emailCHECK(emailLIKE'%_@_%._%')

复杂的格式校验还是要在应用层做（或用正则，MySQL 8.0 有 REGEXP_LIKE）。

CONSTRAINTchk_mobileCHECK(mobileREGEXP'^1[3-9][0-9]{9}$')

6.6 金额一致性

CONSTRAINTchk_totalCHECK(total=subtotal+tax-discount)

注意浮点字段别这么干，用 DECIMAL。

七、CHECK 和性能

每次 INSERT/UPDATE都会求值所有约束。
简单表达式开销很小（微秒级）。

但要注意：

1. 复杂表达式（正则、长字符串函数）会有开销
2. 约束很多时累加
3. 高频写入表要评估

一般不用太担心，正确性优先。

八、CHECK vs 应用层校验

两个都要有。

实际做法：

1. 前端做基础校验（格式、必填） 2. 后端做业务校验（复杂规则） 3. 数据库 CHECK 做底线校验（不为负、状态合法）

第 3 步保证：
即使有人直接连数据库改数据、或者有脚本绕过应用，
也不会写入明显非法的数据。

九、常见坑

9.1 版本问题

-- 5.7 里写了 CHECK，以为生效了-- 实际完全没生效！-- 升级到 8.0.16+ 后，之前"能通过"的数据现在会被拦住

升级后要验证现有数据是否满足约束。

9.2 NULL 的三值逻辑

CHECK(age>=0)

age = NULL时，NULL >= 0是UNKNOWN，
CHECK 约束通过（因为 CHECK 只在结果为 FALSE 时才拒绝）。

INSERTINTOt(age)VALUES(NULL);-- 成功！约束没拦住

如果想连 NULL 一起拦：

CHECK(age>=0ANDageISNOTNULL)-- 或者直接列上加 NOT NULL

9.3 约束名冲突

约束名在整个 schema（数据库）内唯一，不是表内唯一。

CREATETABLEa(...,CONSTRAINTchk_xCHECK(...));CREATETABLEb(...,CONSTRAINTchk_xCHECK(...));-- ERROR: Duplicate name

命名建议：带上表名

CONSTRAINTchk_order_amountCHECK(...)CONSTRAINTchk_product_priceCHECK(...)

9.4 约束导致导入失败

批量导入历史数据时，如果有脏数据会被拦住。

-- 临时跳过检查（不推荐生产用）SETSESSIONcheck_constraint_checks='OFF';-- 导入SETSESSIONcheck_constraint_checks='ON';

更好的做法是先清洗数据。

十、迁移表结构时的注意

用 mysqldump 导出再到低版本恢复：

mysqldump 8.0.16+ → MySQL 5.7 CHECK 约束会被保留语法但不生效

跨版本迁移要评估这个差异。

另外，CHECK 约束不能被 NOT NULL 之外的元数据操作破坏，
但以下操作要注意：

-- 修改列类型可能让约束失效或行为改变ALTERTABLEtMODIFYageVARCHAR(10);-- (age >= 0) 现在要做字符串比较了

十一、一个完整例子

CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(32)NOTNULL,user_idBIGINTNOTNULL,subtotalDECIMAL(12,2)NOTNULLDEFAULT0,discountDECIMAL(12,2)NOTNULLDEFAULT0,shipping_feeDECIMAL(12,2)NOTNULLDEFAULT0,totalDECIMAL(12,2)NOTNULLDEFAULT0,quantityINTNOTNULL,statusVARCHAR(20)NOTNULL,created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,paid_atDATETIME,shipped_atDATETIME,CONSTRAINTchk_order_noCHECK(CHAR_LENGTH(order_no)>=8),CONSTRAINTchk_subtotalCHECK(subtotal>=0),CONSTRAINTchk_discountCHECK(discount>=0),CONSTRAINTchk_feeCHECK(shipping_fee>=0),CONSTRAINTchk_totalCHECK(total=subtotal-discount+shipping_fee),CONSTRAINTchk_qtyCHECK(quantity>0),CONSTRAINTchk_statusCHECK(statusIN('pending','paid','shipped','completed','canceled')),CONSTRAINTchk_paid_after_createCHECK(paid_atISNULLORpaid_at>=created_at),CONSTRAINTchk_shipped_after_paidCHECK(shipped_atISNULLOR(paid_atISNOTNULLANDshipped_at>=paid_at)),UNIQUEKEYuk_order_no(order_no),KEYidx_user(user_id),KEYidx_status(status));

这些约束保证了：

• 金额不为负
• 总额的计算一致
• 状态只能是预定义的几个
• 时间线不会倒流（付款时间不能早于创建时间）

小结

实用建议：

1. 8.0.16+ 才用，低版本写了等于没写
2. 给约束起有意义的名字，报错时好定位
3. 注意 NULL 会通过 CHECK，必要时显式排除
4. 应用层 + 数据库层都做校验，不要互相替代
5. 别写复杂表达式，简单规则用 CHECK，复杂的放应用层

CHECK 约束是个"低成本高保障"的特性——
几行 SQL，换来的是任何途径的写入都不会破坏基本业务规则。
升级到 8.0 之后值得用起来。