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

henda

　　首先看下面这段MySQL的操作，我新建了一个含有a和b的表，其中a用check约束必须大于0，然而我插入了一条(-2,1,1)的数据，其中a=-2，也是成功插入的。
　　所以MySQL只是check，但是不强制check。
　　

mysql> create table checkDemoTable(a int,b int,id int,primary key(id));　　

Query OK, 0 rows affected　　


　　
mysql>>　　
Query OK, 0 rows affected
　　
Records: 0  Duplicates: 0  Warnings: 0
　　

　　
mysql> insert into checkDemoTable values(-2,1,1);
　　
Query OK, 1 row affected
　　

　　
mysql> select * from checkDemoTable;
　　
+

　　
| a  | b |>　　
+
　　
| -2 | 1 |  1 |
　　
+
　　
1 row in set
　　

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

mysql> create table checkDemoTable(a enum('男','女'),b int,id int,primary key(id));　　

Query OK, 0 rows affected　　

　　
mysql> insert into checkDemoTable values('男',1,1);
　　
Query OK, 1 row affected
　　

　　
mysql> select * from checkDemoTable;
　　
+

　　
| a  | b |>　　
+
　　
| 男 | 1 |  1 |
　　
+
　　
1 row in set
　　

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

mysql> create table checkDemoTable(a int,b int,id int,primary key(id));　　

Query OK, 0 rows affected　　

　　
mysql> delimiter ||  
　　
drop trigger if exists checkTrigger||  
　　
create trigger checkTrigger before insert on checkDemoTable for each row
　　
begin  
　　
if new.a<=0 then set new.a=1; end if;
　　
end||  
　　
delimiter;
　　
   
　　
Query OK, 0 rows affected
　　
Query OK, 0 rows affected
　　

　　
mysql> insert into checkDemoTable values(-1,1,1);
　　
Query OK, 1 row affected
　　

　　
mysql> select * from checkDemoTable;
　　
+

　　
| a | b |>　　
+
　　
| 1 | 1 |  1 |
　　
+
　　
1 row in set