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

问题引入

我们知道在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的博客

  • 12
    点赞
  • 29
    收藏
  • 打赏
    打赏
  • 4
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:技术黑板 设计师:CSDN官方博客 返回首页
评论 4

打赏作者

NPU_Li Meng

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值