Mysql中的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

如果属于业务逻辑,建议放在应用层处理,方便开发者:理解和维护,但是:也需要通过强化业务管理,避免特权用户偶发操作引起对数据完整性的破坏

到此这篇关于Mysql中的CHECK约束特性详解的文章就介绍到这了,更多相关Mysql中CHECK约束内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • SQL Server中Check约束的学习教程

    0.什么是Check约束? CHECK约束指在表的列中增加额外的限制条件. 注: CHECK约束不能在VIEW中定义.CHECK约束只能定义的列必须包含在所指定的表中.CHECK约束不能包含子查询. 创建表时定义CHECK约束 1.1 语法: CREATE TABLE table_name ( column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT constraint_name CHECK (

  • 关于sql脚本导入Oracle时重复生成check约束的问题解决

    前言 最近在工作中一位细心的同事发现产品的全量sql脚本中有一些重复的check约束检查,就像下图这样的 重复脚本 怪异之处还在于,每次执行一遍该脚本,然后导出脚本,在导出脚本中重复的次数就会增加一遍.通过navicat,最终确认每导入一次就会新增加一条重复的check约束,如下图所示 navicat 这个全量脚本是直接从数据库中导出的,为了方便导入其他的Oracle数据库中,从产品的出货库导出时手动去掉了服务名.双引号. 通过如下步骤可复现该问题: 1.创建表 CREATE TABLE PD_

  • PostgreSQL 删除check约束的实现

    删除check约束: 1.\d table_name 查找约束名称 \d dm_alarms_fct_20170613 "dm_alarms_fct_20170613_dm_transct_date_key_check" 2.alter table .. drop constraint .. ALTER TABLE "dm_alarms_fct_20170613" Drop constraint "dm_alarms_fct_20170613_dm_tra

  • Mysql中的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会自动生成一个类似:$

  • Android通过json向MySQL中读写数据的方法详解【写入篇】

    本文实例讲述了Android通过json向MySQL中写入数据的方法.分享给大家供大家参考,具体如下: 先说一下如何通过json将Android程序中的数据上传到MySQL中: 首先定义一个类JSONParser.Java类,将json上传数据的方法封装好,可以直接在主程序中调用该类,代码如下 public class JSONParser { static InputStream is = null; static JSONObject jObj = null; static String j

  • MySQL InnoDB引擎的缓存特性详解

    目录 1. 背景 2. 存储器性能差异 3. Buffer Pool 4. Free链表 5. Flush链表 6. LRU链表 7. 其它 1. 背景 对于各种用户数据.索引数据等各种数据都是需要持久化存储到磁盘,然后以“页”为单位进行读写. 相对于直接读写缓存,磁盘IO的成本相当高昂. 对于读取的页面数据,并不是使用完就释放掉,而是放到缓冲区,因为下一次操作有可能还需要读区该页面. 对于修改过的页面数据,也不是马上同步到磁盘,也是放到缓冲区,因为下一次有可能还会修改该页面的数据. 但是缓存的

  • Android通过json向MySQL中读写数据的方法详解【读取篇】

    本文实例讲述了Android通过json向MySQL中读取数据的方法.分享给大家供大家参考,具体如下: 首先 要定义几个解析json的方法parseJsonMulti,代码如下: private void parseJsonMulti(String strResult) { try { Log.v("strResult11","strResult11="+strResult); int index=strResult.indexOf("[");

  • Mysql中Join的使用实例详解

    在前几章节中,我们已经学会了如果在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据. 本章节我们将向大家介绍如何使用MySQL 的 JOIN 在两个或多个表中查询数据. 你可以在SELECT, UPDATE 和 DELETE 语句中使用Mysql 的 join 来联合多表查询. 以下我们将演示MySQL LEFT JOIN 和 JOIN 的使用的不同之处. 在命令提示符中使用JOIN 我们在RUNOOB数据库中有两张表 tcount_tbl 和 runoob_t

  • 在MySQL中自定义参数的使用详解

    MySQL变量包括系统变量和系统变量.这次的学习任务是用户自定义变量.用户变量主要包括局部变量和会话变量. 用户自定义变量的声明方法形如:@var_name,其中变量名称由字母.数字."."."_"和"$"组成.当然,在以字符串或者标识符引用时也可以包含其他字符(例如:@'my-var',@"my-var",或者@my-var). 用户自定义变量是会话级别的变量.其变量的作用域仅限于声明其的客户端链接.当这个客户端断开时,其所

  • mysql中find_in_set()函数的使用详解

    首先举个例子来说: 有个文章表里面有个type字段,它存储的是文章类型,有 1头条.2推荐.3热点.4图文等等 . 现在有篇文章他既是头条,又是热点,还是图文,type中以 1,3,4 的格式存储.那我们如何用sql查找所有type中有4的图文类型的文章呢?? 这就要我们的 find_in_set 出马的时候到了.以下为引用的内容: select * from article where FIND_IN_SET('4',type) --------------------------------

  • MySQL中ESCAPE关键字的用法详解

    MySQL转义 转义即表示转义字符原来的语义,一个转义字符的目的是开始一个字符序列,使得转义字符开头的该字符序列具有不同于该字符序列单独出现时的语义. MySQL中,转义字符以"\"开头,编程中常见的转义字符,在MySQL均是有效的,在此不做赘述和讨论.在此,主要通过"%" 和 "_"来对ESCAPE关键字的作用进行说明. %:匹配任意多个字符. _:匹配单一字符. 如果我们要匹配"%"或者"_"时,就必须

  • MySQL中in和exists区别详解

    一.提前准备 为了大家学习方便,我在这里面建立两张表并为其添加一些数据. 一张水果表,一张供应商表. 水果表 fruits表 f_id f_name f_price a1 apple 5 a2 appricot 2 b1 blackberry 10 b2 berry 8 c1 cocount 9 供应商表 suppliers表 s_id s_name 101 天虹 102 沃尔玛 103 家乐福 104 华润万家 我们将用这两张表做演示. 二.什么是exists exists关键字后面的参数是一

  • MySQL 中 blob 和 text 数据类型详解

    目录 前言 1. blob 类型 2. text 类型 总结 前言 前面文章我们介绍过一些常用数据类型的用法,比如 int.char.varchar 等.一直没详细介绍过 blob 及 text 类型,虽然这两类数据类型不太常用,但在某些场景下还是会用到的.本篇文章将主要介绍 blob 及 text 数据类型的相关知识. 1. blob 类型 blob(binary large object) 是一个可以存储二进制文件的容器,主要用于存储二进制大对象,例如可以存储图片,音视频等文件.按照可存储容

随机推荐