查看原文
其他

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

bruce 老叶茶馆 2024-07-08
* GreatSQL使
  • 1.功能说明

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

1.功能说明

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

CHECK (expr)

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

[CONSTRAINT [symbol]] CHECK (expr) [[NOTENFORCED]
  • 可选的symbol指定了约束的名称,如果省略,MySQL会自动生成一个类似:${table_name}_check_${seq_num}的约束名称,约束名称是大小写敏感的,且最长可以到64个字符
  • expr设定了一个返回值为boolean类型的约束条件,表达式对所有的数据行评估的结果值为:TRUEUNKNOWN(对 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 TableCREATE TABLE `t1` (
  `c1` int(11DEFAULT NULL,
  `c2` int(11DEFAULT NULL,
  `c3` int(11DEFAULT 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 KEYUNIQUEFOREIGN KEYCHECK)属于同一个命名空间(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_SCHEMACHECK_CONSTRAINTS表中存放着所有表中定义的CHECK约束的信息。

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

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

  • 放在数据库表中,通过约束实现,但不支持子查询

  • 放在数据库中,通过触发器(TRIGGER)实现

  • 放在应用程序的逻辑中,在提前数据库前检查

一般性的,选择不同方式的原则如下:

  • 如果CHECK约束可以实现,且约束比较稳定,一般用CHECK约束实现,比如:年龄不允许为负数,不允许>150等,比如:
CREATE TABLE Departments (
    ID int NOT NULL,
    PID int NOT NULL,
    Name varchar(255NOT 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(100NOT 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



文章推荐:



想看更多技术好文,点个“在看”吧!

继续滑动看下一个
向上滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存