有趣的特性:CHECK约束 - GreatSQL
source link: https://www.cnblogs.com/greatsql/p/16558491.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
有趣的特性: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
Recommend
-
150
前言约束概述转换布局开始创建布局添加约束基线约束ChainsProperties设置宽高比例Guidelines自动添加约束参考 本篇 ConstraintLayout 讲解版本:1.0.2,1.1.x 版本开始新增功能在下篇进行讲解。前言ConstraintLayout 是一个 ViewGroup,它的出现是为了解决复杂布...
-
82
-
105
-
20
1、变量定义的几种方式 如: //方式一:普通定义 var a int = 1 fmt.Println("a = ", a) //方式二:类型推导 var b = 10 fmt.Println("b = ", b) //方式三:省略var name := "tom" fmt.Prin...
-
61
虽然下面的有些例子并不一定会让你觉得 WTFs,但它们依然有可能会告诉你一些你所不知道的 Python 有趣特性。我觉得这是一种学习编程语言内部原理的好办法,而且我相信你也会从中获得乐趣! 如果您是一位经验比较丰富的 Python 程...
-
25
Java 是一门不断发展的语言,这是一件好事。然而,其他语言的一些特性也是值得研究的。语言的结构是人们思考问题的方式,也是人们设计解决方案的方式。学习或至少熟悉其他语言是借鉴其设计的好方法。 Java 是我学习的第一门语言并...
-
6
GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。 GreatSQL是MySQL的国产分支版本,使用上与MySQL一致。 一. XDP Socket示例解析 源码参见:
-
4
LIMIT和OFFSET分页性能差!今天来介绍如何高性能分页
-
2
GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。 GreatSQL是MySQL的国产分支版本,使用上与MySQL一致。 1.结论先行 无论ibp(innodb_buffer_pool_size)是否充足,My...
-
6
带你轻松玩转MySQL Shell for GreatSQL 作者:KAiTO 2023-08-18 14:39:02 MySQL Shell 是 MySQL 的一个高级客户端和代码编辑器,是第二代 MySQL 客户端。第一代 MySQL 客户端即我们常用的 MySQL 。 1.1...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK