MySQL 8.0有趣的新特性：CHECK约束

2024-07-11 07:22| 来源: 网络整理| 查看: 265

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

1.功能说明2.建议使用CHECK约束的场景1.功能说明

在MySQL 8.0.16以前， CREATE TABLE允许从语法层面输入下列CHECK约束，但实际没有效果：

代码语言：javascript复制CHECK (expr)

在 MySQL 8.0.16，CREATE TABLE添加了针对所有存储引擎的表和列的CHECK约束的核心特性。CREATE TABLE允许如下针对表或列的约束语法：

代码语言：javascript复制[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED] 可选的symbol指定了约束的名称，如果省略，MySQL会自动生成一个类似：{table_name}_check_{seq_num}的约束名称，约束名称是大小写敏感的，且最长可以到64个字符expr设定了一个返回值为boolean类型的约束条件，表达式对所有的数据行评估的结果值为：TRUE或UNKNOWN（对 NULL值），当值为FALSE时，约束就被违反，产生的效果与执行的语句有关可选的执行子句标识约束是否需要被强制：当未指定或指定为: ENFORCED时，约束被创建且生效当指定为: NOT ENFORCED时，约束被创建但未生效一个CHECK约束可以被指定为表约束或列约束表约束不会出现在列定义内，可以引用任意多个或一个列，且允许引用后续定义的表列列约束出现在列定义内，仅允许引用该列

示例如下：

代码语言：javascript复制CREATE TABLE t1 ( CHECK (c1 c2), c1 INT CHECK (c1 > 10), c2 INT CONSTRAINT c2_positive CHECK (c2 > 0), c3 INT CHECK (c3 < 100), CONSTRAINT c1_nonzero CHECK (c1 0), CHECK (c1 > c3) );

以上示例包含了列约束和表约束，命名和未命名的格式：

第一个约束是一个不包含在任何列定义内的表约束，所以允许引用任意列，且引用了后续定义的列，同时没有给出约束名称，所以MySQL会给该约束生成一个名字后续的3个约束是包含在列定义内的列约束，所有指定引用所在的列最后的两个是表约束

如果想查看上述命令所生成的约束名，可以输入以下SHOW CREATE TABLE命令：

代码语言：javascript复制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, CONSTRAINT `c1_nonzero` CHECK ((`c1` 0)), CONSTRAINT `c2_positive` CHECK ((`c2` > 0)), CONSTRAINT `t1_chk_1` CHECK ((`c1` `c2`)), CONSTRAINT `t1_chk_2` CHECK ((`c1` > 10)), CONSTRAINT `t1_chk_3` CHECK ((`c3` < 100)), CONSTRAINT `t1_chk_4` CHECK ((`c1` > `c3`)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SQL规范要求：所有约束(包括：PRIMARY KEY, UNIQUE，FOREIGN KEY, CHECK)属于同一个命名空间(NAMESPACE)，在MySQL实现中，所有的约束类型在每个schema (database)内有自己的命名空间。所以，CHECK约束的名称在SCHEMA内必须唯一，也就是说不允许有两张表使用同一个CHECK约束名称。(例外：一个临时表可能使用与非临时表一样的约束名称)

CHECK的条件表达式必须遵守以下规则，如果包含不允许的结构，将会触发错误：

非生成列和生成列允许被添加到表达式，但包含AUTO_INCREMENT属性的列和其他表的列不允许被加入字面量和确定性(deterministic)的内置函数以及操作符允许被添加到表达式，确定性的含义是：同样的数据不同用户的多次调用的结果是一致的，非确定性的函数包括：CONNECTION_ID()，CURRENT_USER()，NOW()存储函数和用户自定义函数不被允许存储过程不被允许变量：系统变量、用户自定义变量和存储过程的本地变量均不被允许使用子查询不应许被使用外键参考动作，如：ON UPDATE， ON DELETE被禁止在包含CHECK约束的列使用，相应的，CHECK约束也被禁止在使用外键参考动作的列使用CHECK约束在插入、更新、替换(REPLACE)和LOAD DATA/XML语句的时候被评估，如果评估结果是FALSE将触发错误，如果错误发生，已经提交的数据的处理与对应存储引擎是否支持事务有关，也依赖严格SQL模式是否生效如果约束表达式所需的数据类型与声明的列类型不一致，数据将参考MySQL的类型转换规则被隐式的转换

约束表达式在不同的SQL模式下，可能返回不同的结果

另外，在INFORMATION_SCHEMA的CHECK_CONSTRAINTS表中存放着所有表中定义的CHECK约束的信息。

2.建议使用CHECK约束的场景

复杂业务场景下的约束，从架构角度看，允许有不同的实现方式：

放在数据库表中，通过约束实现，但不支持子查询放在数据库中，通过触发器(TRIGGER)实现放在应用程序的逻辑中，在提前数据库前检查

一般性的，选择不同方式的原则如下：

如果CHECK约束可以实现，且约束比较稳定，一般用CHECK约束实现，比如：年龄不允许为负数，不允许>150等，比如：代码语言：javascript复制CREATE TABLE Departments ( ID int NOT NULL, PID int NOT NULL, Name varchar(255) NOT NULL Default '', CHECK (ID>=1) ); -- add check separately ALTER TABLE Departments ADD CONSTRAINT CHK_PID CHECK (ID>=1 AND PID >=0); -- remove check ALTER TABLE Departments DROP CHECK CHK_PID; 如果属于数据库逻辑，比如：审计，外键可以使用触发器代码语言：javascript复制CREATE TABLE IF NOT EXISTS `department` ( `id` int NOT NULL AUTO_INCREMENT, `pid` int COMMENT 'parent id', `name` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB; CREATE TRIGGER pid_insert_check BEFORE INSERT ON department FOR EACH ROW BEGIN IF (NEW.pid 0 AND NEW.pid NOT IN (select id from department)) THEN signal sqlstate '45000' set message_text = 'department parent id has to be chosen from id'; END IF; END CREATE TRIGGER pid_delete_check BEFORE DELETE ON department FOR EACH ROW BEGIN IF (OLD.id < 0 OR OLD.id IN (select pid from department)) THEN signal sqlstate '45000' set message_text = 'department parent id has to be chosen from id'; END IF; END 如果属于业务逻辑，建议放在应用层处理，方便开发者：理解和维护，但是：也需要通过强化业务管理，避免特权用户偶发操作引起对数据完整性的破坏

Enjoy GreatSQL :)

点击小程序留言

深入浅出MGR》视频课程

戳此小程序即可直达B站

https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0

文章推荐：

MySQL 启停过程了解一二技术分享 | 微服务架构的数据库为什么喜欢分库分表？MySQL内存管理机制浅析技术分析 | 浅析MySQL与ElasticSearch的组合使用构建MySQL智能化高可用架构

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。

Gitee: https://gitee.com/GreatSQL/GreatSQL

GitHub: https://github.com/GreatSQL/GreatSQL

Bilibili：

https://space.bilibili.com/1363850082/video

【本文地址】

公司简介

联系我们