为什么要把这个单独列出来写呢，经常遇到不规范的 SQL，很多时候只能靠数据库层来过滤，代码端不过滤，就只能在数据库端过滤了。

一、常见过滤方式

1）写前置触发器

执行下，暴露出异常

mysql> insert into f1 values (5);ERROR 1644 (45000): Column r1 should be mod by 3,failed to insert.

以上例子简单针对了单列过滤的场景，多列复杂的过滤后面再说。

在存储过程里处理输入约束，和在程序端处理输入约束逻辑一致，只是把相同的处理逻辑放在数据库端，并且以后所有对数据的录入只能依赖存储过程单一入口。

这样会导致录入的数据量非常大，存在很多无用的不规范数据，一般选择非业务高峰时段定期处理不规范数据。

这两种就不举例子了，和第一种类似的处理方法。

二、CHECK 约束

现在要说的是在列这一层次过滤的基于表定义之前就规范好的 CHECK 约束。（MySQL 版本 >= 8.0.16）

mysql> create table f1 (r1 int constraint tb_f1_r1_chk1 check (mod(r1,3)=0));Query OK, 0 rows affected (0.03 sec)mysql> create table f2 (r1 int constraint tb_f2_r1_chk1 check (mod(r1,3)=0) not enforced);Query OK, 0 rows affected (0.02 sec)

1. constraint 名字在每个数据库中唯一。

2. check 约束针对语句 insert/update/replace/load data/load xml 生效；针对对应的 ignore 语句失效。

4. 不适用于存储过程和存储函数。

6. 子查询不适用。

8. enforced 默认启用，如果单独加上 not enforced ，check 约束失效。

结合以上看看刚才那两张表实际的例子，check 约束仅仅对表 f1 生效。

mysql> insert into f1 values (10);ERROR 3819 (HY000): Check constraint 'tb_f1_r1_chk1' is violated.mysql> insert into f2 values (10);Query OK, 1 row affected (0.01 sec)mysql> select * from f1;Empty set (0.00 sec)mysql> select  * from f2;+------+| r1   |+------+|   10 |+------+1 row in set (0.00 sec)

示例 3

mysql> drop table f1;Query OK, 0 rows affected (0.02 sec)mysql> create table f1    -> (    ->  r1 int constraint tb_f1_r1_chk1 check (r1 > 10),    ->  r2 int constraint tb_f1_r2_positive check (r2 > 0),    ->  r3 int constraint tb_f1_r3_chk1 check (r3 < 100),    ->  constraint tb_f1_r1_nonzero check (r1 <> 0),    ->  constraint tb_f1_r1r2_chk1 check (r1 <> r2),    ->  constraint tb_f1_r1r3_chk1 check (r1 > r3)    -> );Query OK, 0 rows affected (0.02 sec)

1. 约束tb_f1_r1_nonzero、tb_f1_r1r2_chk1、tb_f1_r1r3_chk 不跟随固定的列，对全局有效，也可以说基于表的 check 约束。

拿掉多余的约束后的定义，

mysql> create table f1    -> (    ->  r1 int constraint tb_f1_r1_chk1 check (r1 > 10),    ->  r2 int constraint tb_f1_r2_positive check (r2 > 0),    ->  r3 int constraint tb_f1_r3_chk1 check (r3 < 100),    ->  constraint tb_f1_r1r2_chk1 check (r1 <> r2),    ->  constraint tb_f1_r1r3_chk1 check (r1 > r3)    -> );Query OK, 0 rows affected (0.02 sec)

那接下来我们改造刚开始那个触发器，只要把相关条件加进去就可以实现同样的 check 列约束。

DELIMITER $$USE `ytt`$$DROP TRIGGER  `tr_check_f1_r1`$$CREATE        TRIGGER `tr_check_f1_r1` BEFORE INSERT ON `f1`    FOR EACH ROW BEGIN    DECLARE v1 TINYINT DEFAULT 0;      IF (new.r1 > 10 AND new.r1 > new.r3 AND new.r1 <> new.r2 AND new.r2 > 0 AND new.r3 < 100) = 0 THEN        SIGNAL SQLSTATE '45000'            SET MESSAGE_TEXT = "Failed to write: constraint check: \n (\n r1 >10 \n&&  r1 > r3 \n&& r1 <> r2 \n&& r2> 0 \n&& r3 < 100\n).";      END IF;     END;$$DELIMITER ;

测试下效果，

mysql> insert into f1 values (20,30,100);ERROR 1644 (45000): Failed to write: constraint check: ( r1 >10&&  r1 > r3&& r1 <> r2&& r2> 0&& r3 < 100).mysql> insert into f1 values (100,30,90);Query OK, 1 row affected (0.01 sec)mysql> select * from f1;+------+------+------+| r1   | r2   | r3   |+------+------+------+|  100 |   30 |   90 |+------+------+------+1 row in set (0.00 sec)

结论

本文介绍了数据库 CHECK 约束相关的用法以及大概例子。

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

关于爱可生

公司持续积累的核心关键技术，覆盖到分布式数据库集群、云数据平台、数据库大体量运管平台、海量数据集成于存储、清洗与治理、人工智能分析挖掘、可视化展现、安全与隐私保护等多个领域。