* GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。
在MySQL 8.0.16以前, CREATE TABLE
允许从语法层面输入下列CHECK
约束,但实际没有效果:
CHECK (expr)
在 MySQL 8.0.16,CREATE TABLE
添加了针对所有存储引擎的表和列的CHECK
约束的核心特性。CREATE TABLE
允许如下针对表或列的约束语法:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
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)
);
以上示例包含了列约束和表约束,命名和未命名的格式:
如果想查看上述命令所生成的约束名,可以输入以下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
属性的列和其他表的列不允许被加入CONNECTION_ID()
,CURRENT_USER()
,NOW()
ON UPDATE
, ON DELETE
被禁止在包含CHECK
约束的列使用,相应的,CHECK
约束也被禁止在使用外键参考动作的列使用CHECK
约束在插入、更新、替换(REPLACE)和LOAD DATA/XML
语句的时候被评估,如果评估结果是FALSE
将触发错误,如果错误发生,已经提交的数据的处理与对应存储引擎是否支持事务有关,也依赖严格SQL模式是否生效约束表达式在不同的SQL模式下,可能返回不同的结果
另外,在INFORMATION_SCHEMA
的CHECK_CONSTRAINTS
表中存放着所有表中定义的CHECK
约束的信息。
复杂业务场景下的约束,从架构角度看,允许有不同的实现方式:
一般性的,选择不同方式的原则如下:
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 :)
点击小程序留言
深入浅出MGR》视频课程
戳此小程序即可直达B站
https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0
文章推荐:
关于 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