[原]MySQL中check约束无效的处理方式

李猛 18/04/12 23:12:35

问题引入

我们知道在SQL中,可以用check来来约束字段的范围。
下面这些SQL语句在MySQL下运行(系统环境为deepin 15.5 64位):

mysql> CREATE TABLE `Student` (
    ->   `Sno` char(8) NOT NULL,
    ->   `Sname` varchar(10) NOT NULL,
    ->   `Sex` char(2)NOT NULL  DEFAULT '男' CHECK (Sex IN ('男','女')) ,
    ->   `Age` tinyint(4) NOT NULL DEFAULT '20' CHECK (Age between 15 and 30),
    ->   `Phonenumber` char(12) DEFAULT NULL,
    ->   `Sdept` varchar(20) NOT NULL,
    ->   PRIMARY KEY (`Sno`),
    ->   UNIQUE KEY `Phonenumber` (`Phonenumber`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into Student(Sno,Sname,Sex,Age,Phonenumber,Sdept) values('4','nancy','m',0,'12345243965','CS');
Query OK, 1 row affected (0.02 sec)

mysql> select * from Student;
+-----+-------+-----+-----+-------------+-------+
| Sno | Sname | Sex | Age | Phonenumber | Sdept |
+-----+-------+-----+-----+-------------+-------+
| 4   | nancy | m   |   0 | 12345243965 | CS    |
+-----+-------+-----+-----+-------------+-------+
1 row in set (0.00 sec)

由此,尽管在创建table时用check来约束字段的范围,但是这是无效的,不符合规范的记录还是被插入表中。

为什么约束无效?

check

查看
MySQL的官方文档
(科学上网)会发现下面一句话

CHECK

The CHECK clause is parsed but ignored by all storage engines. See Section 1.8.2.3, “Foreign Key Differences”.

即CHECK语句会被忽略,也意味着MySQL被没有实现这个功能。在bug report中也提到该问题。

bug

解决这个问题的两种方式:

1、如果要设置CHECK约束的字段范围小,并且比较容易列举全部的值,就可以考虑将该字段的类型设置为枚举类型 enum()或集合类型set()。

比如性别字段可以这样设置,插入枚举值以外值的操作将不被允许。

CREATE TABLE `Student` (
  `Sno` char(8) NOT NULL,
  `Sname` varchar(10) NOT NULL,
  `Sex` enum('男','女') NOT NULL DEFAULT '男',
  `Age` tinyint(4) NOT NULL DEFAULT '20',
  `Phonenumber` char(12) DEFAULT NULL,
  `Sdept` varchar(20) NOT NULL,
  PRIMARY KEY (`Sno`),
  UNIQUE KEY `Phonenumber` (`Phonenumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、如果需要设置CHECK约束的字段范围大,且列举全部值比较困难,比如整数或者某一区间,那就只能使用触发器来代替约束实现数据的有效性了。

DELIMITER $
create trigger studentcheck before insert on Student for each row
begin
if new.Age<15 or new.Age>30 then set new.Age=20;end if;
end $
DELIMITER ;

测试:

mysql> insert into Student(Sno,Sname,Sex,Age,Phonenumber,Sdept) values('5','Joe','m',0,'12345243912','CS');
ERROR 1265 (01000): Data truncated for column 'Sex' at row 1
mysql> insert into Student(Sno,Sname,Sex,Age,Phonenumber,Sdept) values('5','Joe','男',0,'12345243912','CS');
Query OK, 1 row affected (0.00 sec)

mysql> select * from Student;
+-----+-------+-----+-----+-------------+-------+
| Sno | Sname | Sex | Age | Phonenumber | Sdept |
+-----+-------+-----+-----+-------------+-------+
| 4   | nancy | m   |   0 | 12345243965 | CS    |
| 5   | Joe   | 男  |  20 | 12345243912 | CS    |
+-----+-------+-----+-----+-------------+-------+
2 rows in set (0.00 sec)

可以看出,现在Sex必须在“男”和“女”中选择,否则插入失败;当年龄不合规范时,由于触发器的存在,会将其自动设置为默认的20。

由此可以解决check约束无效的问题。

参考

MySQL官方文档

bug-report

wangtianze的博客

小风筝0010的博客

作者:ldx19980108 发表于 2018/04/12 23:12:35 原文链接 https://blog.csdn.net/ldx19980108/article/details/79921853
阅读:5