MySQL 8.0 新特性之检查约束的实现
来源:脚本之家
时间:2023-01-08 14:38:04 128浏览 收藏
本篇文章向大家介绍《MySQL 8.0 新特性之检查约束的实现》,主要包括MySQL8.0检查约束,具有一定的参考价值,需要的朋友可以参考一下。
大家好,我是只谈技术不剪发的 Tony 老师。这次我们来介绍一个 MySQL 8.0 增加的新功能:检查约束(CHECK )。
SQL 中的检查约束属于完整性约束的一种,可以用于约束表中的某个字段或者一些字段必须满足某个条件。例如用户名必须大写、余额不能小于零等。
我们常见的数据库都实现了检查约束,例如 Oracle、SQL Server、PostgreSQL 以及 SQLite;然而 MySQL 一直以来没有真正实现该功能,直到最新的 MySQL 8.0.16。
MySQL 8.0.15 之前
在 MySQL 8.0.15 以及之前的版本中,虽然 CREATE TABLE 语句允许CHECK (expr)形式的检查约束语法,但实际上解析之后会忽略该子句。例如
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.15 | +-----------+ 1 row in set (0.00 sec) mysql> CREATE TABLE t1 -> ( -> c1 INT CHECK (c1 > 10), -> c2 INT , -> c3 INT CHECK (c3 CONSTRAINT c2_positive CHECK (c2 > 0), -> CHECK (c1 > c3) -> ); Query OK, 0 rows affected (0.33 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, `c3` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
虽然我们在定义时指定了各种 CHECK 选项,但最终的表结构中不包含任何检查约束。这也意味着我们可以插入非法的数据:
mysql> insert into t1(c1, c2, c3) values(1, -1, 100); Query OK, 1 row affected (0.06 sec)
如果我们想要在 MySQL 8.0.15 之前实现类似的检查约束,可以使用触发器;或者创建一个包含 WITH CHECK OPTION 选项的视图,然后通过视图插入或修改数据。
MySQL 8.0.16 之后
MySQL 8.0.16 于 2019 年 4 月 25 日发布,终于带来了我们期待已久的 CHECK 约束功能,而且对于所有的存储引擎都有效。CREATE TABLE 语句允许以下形式的 CHECK 约束语法,可以指定列级约束和表级约束:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
其中,可选的 symbol 参数用于给约束指定一个名称。如果省略该选项,MySQL 将会产生一个以表名开头、加上 _chk_ 以及一个数字编号(1、2、3 …)组成的名字(table_name_chk_n)。约束名称最大长度为 64 个字符,而且区分大小写。
expr 是一个布尔表达式,用于指定约束的条件;表中的每行数据都必须满足 expr 的结果为 TRUE 或者 UNKNOWN(NULL)。如果表达式的结果为 FALSE,将会违反约束。
可选的 ENFORCED 子句用于指定是否强制该约束:
- 如果忽略或者指定了 ENFORCED,创建并强制该约束;
- 如果指定了 NOT ENFORCED,创建但是不强制该约束。这也意味着约束不会生效。
CHECK 约束可以在列级指定,也可以在表级指定。
列级检查约束
列级约束只能出现在字段定义之后,而且只能针对该字段进行约束。例如:
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.16 | +-----------+ 1 row in set (0.00 sec) mysql> CREATE TABLE t1 -> ( -> c1 INT CHECK (c1 > 10), -> c2 INT CONSTRAINT c2_positive CHECK (c2 > 0), -> c3 INT CHECK (c3 ); Query OK, 0 rows affected (0.04 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int DEFAULT NULL, `c2` int DEFAULT NULL, `c3` int DEFAULT NULL, CONSTRAINT `c2_positive` CHECK ((`c2` > 0)), CONSTRAINT `t1_chk_1` CHECK ((`c1` > 10)), CONSTRAINT `t1_chk_2` CHECK ((`c3`其中,字段 c1 和 c3 上的检查约束使用了系统生成的名称;c2 上的检查约束使用了自定义名称。
SQL 标准中所有的约束(主键、唯一约束、外键、检查约束等)都属于相同的命名空间,意味着它们相互不能重名。但在 MySQL 中,每个数据库中的约束类型属于自己的命名空间;因此,主键和检查约束可以重名,但是两个检查约束不能重名。
我们插入一条测试数据:
mysql> insert into t1(c1, c2, c3) values(1, -1, 100); ERROR 3819 (HY000): Check constraint 'c2_positive' is violated.插入数据的三个字段都违反了约束,结果显示的是违反了 c2_positive;因为它按照名字排在第一,由此也可以看出 MySQL 按照约束的名字排序依次进行检查。
我们再插入一条测试数据:
mysql> insert into t1(c1, c2, c3) values(null, null, null); Query OK, 1 row affected (0.00 sec)数据插入成功,所以 NULL 值并不会违反检查约束。
表级检查约束
表级约束独立于字段的定义,而且可以针对多个字段进行约束,甚至可以出现在字段定义之前。例如:
mysql> drop table t1; Query OK, 0 rows affected (0.04 sec) mysql> CREATE TABLE t1 -> ( -> CHECK (c1 c2), -> c1 INT, -> c2 INT, -> c3 INT, -> CONSTRAINT c1_nonzero CHECK (c1 0), -> CHECK (c1 > c3) -> ); Query OK, 0 rows affected (0.04 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int DEFAULT NULL, `c2` int DEFAULT NULL, `c3` int DEFAULT NULL, CONSTRAINT `c1_nonzero` CHECK ((`c1` 0)), CONSTRAINT `t1_chk_1` CHECK ((`c1` `c2`)), CONSTRAINT `t1_chk_2` CHECK ((`c1` > `c3`)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)第一个约束 t1_chk_1 出现在字段定义之前,但是仍然可以引用 c1 和 c2;第二个约束 c1_nonzero 使用了自定义的名称;第三个约束 t1_chk_2 在所有字段定义之后。
我们同样插入一些测试数据:
mysql> insert into t1(c1, c2, c3) values(1, 2, 3); ERROR 3819 (HY000): Check constraint 't1_chk_2' is violated. mysql> insert into t1(c1, c2, c3) values(null, 2, 3); Query OK, 1 row affected (0.01 sec)第一条记录中的 c1 小于 c3,违反了检查约束 t1_chk_2;第二条记录中的 c1 为 NULL,检查约束 t1_chk_2 的结果为 UNKNOWN,不违法约束。
强制选项
使用默认方式或者 ENFORCED 选项创建的约束处于强制检查状态,我们也可以将其修改为 NOT ENFORCED,从而忽略检查:
ALTER TABLE tbl_name ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED修改之后的检查约束仍然存在,但是不会执行检查。例如:
mysql> alter table t1 -> alter check t1_chk_1 not enforced; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int DEFAULT NULL, `c2` int DEFAULT NULL, `c3` int DEFAULT NULL, CONSTRAINT `c1_nonzero` CHECK ((`c1` 0)), CONSTRAINT `t1_chk_1` CHECK ((`c1` `c2`)) /*!80016 NOT ENFORCED */, CONSTRAINT `t1_chk_2` CHECK ((`c1` > `c3`)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)从最新的定义可以看出,t1_chk_1 处于 NOT ENFORCED 状态。我们插入一条违反该约束的数据:
mysql> insert into t1(c1, c2, c3) values(1, 1, 0); Query OK, 1 row affected (0.01 sec)该记录的 c1 和 c2 相等,但是插入成功。
如果我们需要迁移一些低版本的历史数据时,它们可能会违反新的检查约束;此时可以先将该约束禁用,等数据迁移并处理完成之后,再次启用强制选项。
检查约束限制
MySQL 中的 CHECK 条件表达式必须满足以下规则,否则无法创建检查约束:
- 允许使用非计算列和计算列,但是不允许使用 AUTO_INCREMENT 字段或者其他表中的字段。
- 允许使用字面值、确定性内置函数(即使不同用户,多次调用该函数,只要输入相同结果就相同)以及运算符。非确定性函数包括:CONNECTION_ID()、CURRENT_USER()、NOW() 等等,它们不能用于检查约束。
- 不允许使用存储函数或者自定义函数。
- 不允许使用存储过程和函数参数。
- 不允许使用变量,包括系统变量、用户定义变量和存储程序的局部变量。
- 不允许使用子查询。
另外,禁用在 CHECK 约束字段上定义外键约束的参照操作(ON UPDATE、ON DELETE);同理,存在外键约束参照操作的字段上也不允许创建 CHECK 约束。
对于 INSERT、UPDATE、REPLACE、LOAD DATA 以及 LOAD XML 语句,如果违反检查约束将会返回错误。此时,对于已经修改的数据处理取决于存储引擎是否支持事务,以及是否使用了严格 SQL 模式。
对于 INSERT IGNORE、UPDATE IGNORE、REPLACE、LOAD DATA … IGNORE 以及 LOAD XML … IGNORE 语句,如果违反检查约束将会返回警告并且跳过存在问题的数据行。
如果约束表达式的结果类型和字段的数据类型不同,MySQL 将会执行隐式类型转换;如果类型转换失败或者丢失精度,将会返回错误。
总结
MySQL 8.0.16 新增的检查约束提高了 MySQL 实现业务完整性约束的能力,也使得 MySQL更加遵循 SQL 标准。
本篇关于《MySQL 8.0 新特性之检查约束的实现》的介绍就到此结束啦,但是学无止境,想要了解学习更多关于数据库的相关知识,请关注golang学习网公众号!
-
501 收藏
-
501 收藏
-
501 收藏
-
501 收藏
-
501 收藏
-
377 收藏
-
250 收藏
-
463 收藏
-
206 收藏
-
132 收藏
-
188 收藏
-
404 收藏
-
101 收藏
-
265 收藏
-
209 收藏
-
446 收藏
-
339 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 立即学习 508次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 立即学习 484次学习
-
- 称心的台灯
- 细节满满,收藏了,感谢老哥的这篇文章,我会继续支持!
- 2023-06-14 10:08:38
-
- 正直的棒棒糖
- 赞 👍👍,一直没懂这个问题,但其实工作中常常有遇到...不过今天到这,帮助很大,总算是懂了,感谢up主分享技术文章!
- 2023-04-29 02:59:52
-
- 正直的棒棒糖
- 好细啊,mark,感谢楼主的这篇技术贴,我会继续支持!
- 2023-01-23 05:53:39
-
- 壮观的大门
- 这篇博文真及时,太细致了,赞 👍👍,mark,关注作者了!希望作者能多写数据库相关的文章。
- 2023-01-23 00:44:27
-
- 稳重的小兔子
- 这篇文章内容真及时,太细致了,很好,收藏了,关注大佬了!希望大佬能多写数据库相关的文章。
- 2023-01-19 09:57:53
-
- 要减肥的唇膏
- 赞 👍👍,一直没懂这个问题,但其实工作中常常有遇到...不过今天到这,帮助很大,总算是懂了,感谢作者分享博文!
- 2023-01-13 20:20:50
-
- 危机的河马
- 这篇博文真是及时雨啊,作者大大加油!
- 2023-01-12 04:37:49