第04期：列 CHECK 约束

于由杨涛涛发布

check 约束意思，就是对一列或者多列按照一定的预先设置好的规则进行过滤，条件为真，则过滤成功；条件为假，则过滤失败，返回失败代码给客户端。

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

一、常见过滤方式

假设表 f1，字段 r1 类型为 3 的倍数，否则拒绝写入。又假设 r1 的输入不规范，只能靠数据库来过滤，那怎么办？无非有几种：

1）写前置触发器

示例 1

  1. mysql> create table f1 (r1 int);
  2. Query OK, 0 rows affected (0.03 sec)

  4. DELIMITER $$

  6. USE `ytt`$$

  8. DROP TRIGGER /*!50032 IF EXISTS */ `tr_check_f1_r1`$$

  10. CREATE
  11.     /*!50017 DEFINER = 'root'@'%' */
  12.     TRIGGER `tr_check_f1_r1` BEFORE INSERT ON `f1`
  13.     FOR EACH ROW BEGIN
  14.       IF MOD(new.r1,3) <> 0 THEN
  15.        SIGNAL SQLSTATE '45000'
  16.             SET MESSAGE_TEXT = 'Column r1 should be mod by 3,failed to insert.';
  17.       END IF;
  18.      END;
  19. $$

  21. DELIMITER ;

执行下，暴露出异常

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

正常插入

  1. mysql> insert into f1 values (3);
  2. Query OK, 1 row affected (0.01 sec)

  4. mysql> select * from f1;
  5. +------+
  6. | r1   |
  7. +------+
  8. |    3 |
  9. +------+
  10. 1 row in set (0.00 sec)

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

2）写存储过程封装 SQL

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

3）不拒绝任何输入，定期处理不规范数据

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

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

二、CHECK 约束

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

  1. mysql> create table f1 (r1 int constraint tb_f1_r1_chk1 check (mod(r1,3)=0));
  2. Query OK, 0 rows affected (0.03 sec)

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

这里 CHECK 约束的相关限制如下：

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

也就是说单个数据库里不存在相同的两个 constraint，如果不定义，系统自动生成一个唯一的约束名字。

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

3. 并非每个函数都可以使用，比如函数结果不确定的：NOW()，CONNECTION_ID()，CURRENT_USER()。

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

5. 系统变量不适用。

6. 子查询不适用。

7. 外键动作（比如 ON UPDATE, ON DELETE） 不适用。

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

示例 2

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

  1. mysql> insert into f1 values (10);
  2. ERROR 3819 (HY000): Check constraint 'tb_f1_r1_chk1' is violated.
  3. mysql> insert into f2 values (10);
  4. Query OK, 1 row affected (0.01 sec)

  6. mysql> select * from f1;
  7. Empty set (0.00 sec)

  9. mysql> select  * from f2;
  10. +------+
  11. | r1   |
  12. +------+
  13. |   10 |
  14. +------+
  15. 1 row in set (0.00 sec)

接下来看看 CHECK 约束更加详细的例子。

示例 3
  1. mysql> drop table f1;
  2. Query OK, 0 rows affected (0.02 sec)

  4. mysql> create table f1
  5.     -> (
  6.     ->  r1 int constraint tb_f1_r1_chk1 check (r1 > 10),
  7.     ->  r2 int constraint tb_f1_r2_positive check (r2 > 0),
  8.     ->  r3 int constraint tb_f1_r3_chk1 check (r3 < 100),
  9.     ->  constraint tb_f1_r1_nonzero check (r1 <> 0),
  10.     ->  constraint tb_f1_r1r2_chk1 check (r1 <> r2),
  11.     ->  constraint tb_f1_r1r3_chk1 check (r1 > r3)
  12.     -> );
  13. Query OK, 0 rows affected (0.02 sec)

上面例子有一点说明下，

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

2. 约束tb_f1_r1_chk1 包含 约束 tb_f1_r1_nonezero， 这样 tb_f1_r1_nonezero 永远探测不到异常。所以检查后，去掉这个约束。

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

  1. mysql> create table f1
  2.     -> (
  3.     ->  r1 int constraint tb_f1_r1_chk1 check (r1 > 10),
  4.     ->  r2 int constraint tb_f1_r2_positive check (r2 > 0),
  5.     ->  r3 int constraint tb_f1_r3_chk1 check (r3 < 100),
  6.     ->  constraint tb_f1_r1r2_chk1 check (r1 <> r2),
  7.     ->  constraint tb_f1_r1r3_chk1 check (r1 > r3)
  8.     -> );
  9. Query OK, 0 rows affected (0.02 sec)

那针对这张表做个测试，可以看到这里每个列的约束其实是“与”的关系，任何一列约束不成立写入就失败。

  1. mysql> insert into f1 values (20,10,10);
  2. Query OK, 1 row affected (0.01 sec)

  4. mysql> insert into f1 values (10,10,10);
  5. ERROR 3819 (HY000): Check constraint 'tb_f1_r1_chk1' is violated.
  6. mysql> insert into f1 values (20,-10,10);
  7. ERROR 3819 (HY000): Check constraint 'tb_f1_r2_positive' is violated.
  8. mysql> insert into f1 values (20,10,30);
  9. ERROR 3819 (HY000): Check constraint 'tb_f1_r1r3_chk1' is violated.

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

  1. DELIMITER $$

  3. USE `ytt`$$

  5. DROP TRIGGER /*!50032 IF EXISTS */ `tr_check_f1_r1`$$

  7. CREATE
  8.     /*!50017 DEFINER = 'root'@'%' */
  9.     TRIGGER `tr_check_f1_r1` BEFORE INSERT ON `f1`
  10.     FOR EACH ROW BEGIN
  11.     DECLARE v1 TINYINT DEFAULT 0;

  13.       IF (new.r1 > 10 AND new.r1 > new.r3 AND new.r1 <> new.r2 AND new.r2 > 0 AND new.r3 < 100) = 0 THEN
  14.         SIGNAL SQLSTATE '45000'
  15.             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).";
  16.       END IF;
  17.      END;
  18. $$

  20. DELIMITER ;

测试下效果，

  1. mysql> insert into f1 values (20,30,100);
  2. ERROR 1644 (45000): Failed to write: constraint check:
  3.  (
  4.  r1 >10
  5. &&  r1 > r3
  6. && r1 <> r2
  7. && r2> 0
  8. && r3 < 100
  9. ).

  11. mysql> insert into f1 values (100,30,90);
  12. Query OK, 1 row affected (0.01 sec)

  14. mysql> select * from f1;
  15. +------+------+------+
  16. | r1   | r2   | r3   |
  17. +------+------+------+
  18. |  100 |   30 |   90 |
  19. +------+------+------+
  20. 1 row in set (0.00 sec)

结论

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

我个人建议，这种 CHECK 约束如果能从数据库端剥离放到应用端实现最好不过了，数据端越简单，性能越好。但也有例外，应用端如果由于历史原因或者其他因素实现困难，也只能寄于数据库端。

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

分类： MySQL 调优

相关文章

MySQL 调优

第45期：一条 SQL 语句优化的基本思路

SQL 语句优化是一个既熟悉又陌生的话题。面对千奇百怪的 SQL 语句 阅读更多…

MySQL 调优

第44期：无主键分区表该不该使用

本来想着分区表在上一篇后就不续写了，最近又有同学咨询我分区表的新问题： 阅读更多…

MySQL 调优

第43期：多表关联场景下如何用好分区表

如何在多表关联场景下合理利用分区表来提升查询性能？基于前几篇关于分区表 阅读更多…