MySQL关于check约束无效的解决办法

mysql> create table checkDemoTable(a int,b int,id int,primary key(id)); 2 Query OK, 0 rows affected 3 4 mysql> alter table checkDemoTable add constraint checkDemoConstraint check(age>0); 5 Query OK, 0 rows affected 6 Records: 0 Duplicates: 0 Warnings: 0 7 8 mysql> insert into checkDemoTable values(-2,1,1); 9 Query OK, 1 row affected 10 11 mysql> select * from checkDemoTable; 12 +----+---+----+ 13 | a | b | id | 14 +----+---+----+ 15 | -2 | 1 | 1 | 16 +----+---+----+ 17 1 row in set

 

解决这个问题有两种办法：

1. 如果需要设置CHECK约束的字段范围小，并且比较容易列举全部的值，就可以考虑将该字段的类型设置为枚举类型 enum()或集合类型set()。比如性别字段可以这样设置，插入枚举值以外值的操作将不被允许。

 1 mysql> create table checkDemoTable(a enum(‘男‘,‘女‘),b int,id int,primary key(id));
 2 Query OK, 0 rows affected
 3 
 4 mysql> insert into checkDemoTable values(‘男‘,1,1);
 5 Query OK, 1 row affected
 6 
 7 mysql> select * from checkDemoTable;
 8 +----+---+----+
 9 | a  | b | id |
10 +----+---+----+
11 | 男 | 1 |  1 |
12 +----+---+----+
13 1 row in set

2. 如果需要设置CHECK约束的字段范围大，且列举全部值比较困难，比如：>0的值，那就只能使用触发器来代替约束实现数据的有效性了。如下代码，可以保证a>0。

 1 mysql> create table checkDemoTable(a int,b int,id int,primary key(id));
 2 Query OK, 0 rows affected
 3 
 4 mysql> delimiter ||  
 5 drop trigger if exists checkTrigger||  
 6 create trigger checkTrigger before insert on checkDemoTable for each row 
 7 begin  
 8 if new.a<=0 then set new.a=1; end if;
 9 end||  
10 delimiter;
11   
12 Query OK, 0 rows affected
13 Query OK, 0 rows affected
14 
15 mysql> insert into checkDemoTable values(-1,1,1);
16 Query OK, 1 row affected
17 
18 mysql> select * from checkDemoTable;
19 +---+---+----+
20 | a | b | id |
21 +---+---+----+
22 | 1 | 1 |  1 |
23 +---+---+----+
24 1 row in set

 

MySQL关于check约束无效的解决办法

标签：ota   解决   mysql   ted   sql   creat   duplicate   limit   check