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

最新推荐文章于 2024-08-07 13:49:28 发布

NPU_Li Meng

最新推荐文章于 2024-08-07 13:49:28 发布

阅读量1.3w

点赞数 14

分类专栏： MySQL 文章标签： MySQL SQL中check约束

本文链接：https://blog.csdn.net/ldx19980108/article/details/79921853

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

问题引入

我们知道在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)