前言:
现时你们对“mysql列约束”大约比较讲究,同学们都想要剖析一些“mysql列约束”的相关知识。那么小编也在网摘上搜集了一些关于“mysql列约束””的相关知识,希望你们能喜欢,看官们一起来学习一下吧!check 约束意思,就是对一列或者多列按照一定的预先设置好的规则进行过滤,条件为真,则过滤成功;条件为假,则过滤失败,返回失败代码给客户端。
为什么要把这个单独列出来写呢,经常遇到不规范的 SQL,很多时候只能靠数据库层来过滤,代码端不过滤,就只能在数据库端过滤了。
一、常见过滤方式
假设表 f1,字段 r1 类型为 3 的倍数,否则拒绝写入。又假设 r1 的输入不规范,只能靠数据库来过滤,那怎么办?无非有几种:
1)写前置触发器
示例 1
mysql> create table f1 (r1 int);Query OK, 0 rows affected (0.03 sec)DELIMITER $$USE `ytt`$$DROP TRIGGER /*!50032 IF EXISTS */ `tr_check_f1_r1`$$CREATE /*!50017 DEFINER = 'root'@'%' */ TRIGGER `tr_check_f1_r1` BEFORE INSERT ON `f1` FOR EACH ROW BEGIN IF MOD(new.r1,3) <> 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Column r1 should be mod by 3,failed to insert.'; END IF; END;$$DELIMITER ;
执行下,暴露出异常
mysql> insert into f1 values (5);ERROR 1644 (45000): Column r1 should be mod by 3,failed to insert.
正常插入
mysql> insert into f1 values (3);Query OK, 1 row affected (0.01 sec)mysql> select * from f1;+------+| r1 |+------+| 3 |+------+1 row in set (0.00 sec)
以上例子简单针对了单列过滤的场景,多列复杂的过滤后面再说。
2)写存储过程封装 SQL
在存储过程里处理输入约束,和在程序端处理输入约束逻辑一致,只是把相同的处理逻辑放在数据库端,并且以后所有对数据的录入只能依赖存储过程单一入口。
3)不拒绝任何输入,定期处理不规范数据
这样会导致录入的数据量非常大,存在很多无用的不规范数据,一般选择非业务高峰时段定期处理不规范数据。
这两种就不举例子了,和第一种类似的处理方法。
二、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)
这里 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 生效。
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)
接下来看看 CHECK 约束更加详细的例子。
示例 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 约束。
2. 约束tb_f1_r1_chk1 包含 约束 tb_f1_r1_nonezero, 这样 tb_f1_r1_nonezero 永远探测不到异常。所以检查后,去掉这个约束。
拿掉多余的约束后的定义,
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)
那针对这张表做个测试,可以看到这里每个列的约束其实是“与”的关系,任何一列约束不成立写入就失败。
mysql> insert into f1 values (20,10,10);Query OK, 1 row affected (0.01 sec)mysql> insert into f1 values (10,10,10);ERROR 3819 (HY000): Check constraint 'tb_f1_r1_chk1' is violated.mysql> insert into f1 values (20,-10,10);ERROR 3819 (HY000): Check constraint 'tb_f1_r2_positive' is violated.mysql> insert into f1 values (20,10,30);ERROR 3819 (HY000): Check constraint 'tb_f1_r1r3_chk1' is violated.
那接下来我们改造刚开始那个触发器,只要把相关条件加进去就可以实现同样的 check 列约束。
DELIMITER $$USE `ytt`$$DROP TRIGGER /*!50032 IF EXISTS */ `tr_check_f1_r1`$$CREATE /*!50017 DEFINER = 'root'@'%' */ 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 约束相关的用法以及大概例子。
我个人建议,这种 CHECK 约束如果能从数据库端剥离放到应用端实现最好不过了,数据端越简单,性能越好。但也有例外,应用端如果由于历史原因或者其他因素实现困难,也只能寄于数据库端。
关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!
关于爱可生
爱可生成立于2003年,依托于融合、开放、创新的数据处理技术和服务能力,为大型行业用户的特定场景提供深度挖掘数据价值的解决方案。
公司持续积累的核心关键技术,覆盖到分布式数据库集群、云数据平台、数据库大体量运管平台、海量数据集成于存储、清洗与治理、人工智能分析挖掘、可视化展现、安全与隐私保护等多个领域。
公司已与多个行业内的专业公司建立了长期伙伴关系,不断促进新技术与行业知识相结合,为用户寻求新的数据驱动的价值增长点。公司已在金融、能源电力、广电、政府等行业取得了众多大型用户典型成功案例,获得了市场的认可和业务的持续增长。
标签: #mysql列约束