有趣的特性:CHECK约束
有趣的特性:CHECK约束
功能说明
在MySQL 8.0.16以前, CREATE TABLE
允许从语法层面输入下列CHECK
约束,但实际没有效果:
CHECK (expr)
在 MySQL 8.0.16,CREATE TABLE
添加了针对所有存储引擎的表和列的CHECK
约束的核心特性。CREATE TABLE
允许如下针对表或列的约束语法:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
- 可选的
symbol
指定了约束的名称,如果省略,MySQL会自动生成一个类似:${table_name}_check_${seq_num}
的约束名称,约束名称是大小写敏感的,且最长可以到64个字符
-
expr
设定了一个返回值为boolean
类型的约束条件,表达式对所有的数据行评估的结果值为:TRUE
或UNKNOWN
(对NULL
值),当值为FALSE
时,约束就被违反,产生的效果与执行的语句有关 -
可选的执行子句标识约束是否需要被强制:
-
当未指定或指定为:
ENFORCED
时,约束被创建且生效 -
当指定为:
NOT ENFORCED
时,约束被创建但未生效
-
-
一个
CHECK
约束可以被指定为表约束或列约束-
表约束不会出现在列定义内,可以引用任意多个或一个列,且允许引用后续定义的表列
-
列约束出现在列定义内,仅允许引用该列
-
示例如下:
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
命令:
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
约束的信息。
建议使用CHECK约束的场景
复杂业务场景下的约束,从架构角度看,允许有不同的实现方式:
-
放在数据库表中,通过约束实现,但不支持子查询
-
放在数据库中,通过触发器(TRIGGER)实现
-
放在应用程序的逻辑中,在提前数据库前检查
一般性的,选择不同方式的原则如下:
- 如果CHECK约束可以实现,且约束比较稳定,一般用CHECK约束实现,比如:年龄不允许为负数,不允许>150等,比如:
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;
- 如果属于数据库逻辑,比如:审计,外键可以使用触发器
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 😃
文章推荐:
Changes in GreatSQL 8.0.25 (2021-8-18)
关于 GreatSQL
GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。
Gitee:
https://gitee.com/GreatSQL/GreatSQL
GitHub:
https://github.com/GreatSQL/GreatSQL
Bilibili:
https://space.bilibili.com/1363850082/favlist
微信&QQ群:
QQ群:533341697
微信群:可搜索添加GreatSQL社区助手
微信好友,发送验证信息“加群”加入GreatSQL/MGR交流微信群
GreatSQL社区助手:wanlidbc
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器