有趣的特性: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类型的约束条件,表达式对所有的数据行评估的结果值为: TRUEUNKNOWN(对 NULL值),当值为 FALSE时,约束就被违反,产生的效果与执行的语句有关

  • 可选执行子句标识是否需要强制执行约束:
    [En]

    the optional execution clause identifies whether the constraint needs to be enforced:*

  • 当未指定或指定为: 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)
);

上面的示例包含列和表约束,命名格式和未命名格式:

[En]

The above example contains column and table constraints, named and unnamed formats:

  • 第一个约束是一个不包含在任何列定义内的表约束,所以允许引用任意列,且引用了后续定义的列,同时没有给出约束名称,所以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, UNIQUEFOREIGN KEY, CHECK)属于同一个命名空间(NAMESPACE),在MySQL实现中,所有的约束类型在每个schema (database)内有自己的命名空间。所以, CHECK约束的名称在 SCHEMA内必须唯一,也就是说不允许有两张表使用同一个CHECK约束名称。(例外:一个临时表可能使用与非临时表一样的约束名称)

CHECK的条件表达式必须遵守以下规则,如果包含不允许的结构,将会触发错误:

  • 非生成列和生成列允许被添加到表达式,但包含 AUTO_INCREMENT属性的列和其他表的列不允许被加入
  • 字面量和确定性(deterministic)的内置函数以及操作符允许被添加到表达式,确定性的含义是:同样的数据不同用户的多次调用的结果是一致的,非确定性的函数包括: CONNECTION_ID()CURRENT_USER()NOW()
  • 不允许存储函数和自定义函数
    [En]

    Storage functions and user-defined functions are not allowed*

  • 存储过程不被允许
  • 变量:不允许存储过程的系统变量、用户定义变量、局部变量
    [En]

    variables: system variables, user-defined variables, and local variables of stored procedures are not allowed*

  • 子查询不应许被使用
  • 外键参考动作,如: ON UPDATEON DELETE被禁止在包含 CHECK约束的列使用,相应的, CHECK约束也被禁止在使用外键参考动作的列使用
  • CHECK约束在插入、更新、替换(REPLACE)和 LOAD DATA/XML语句的时候被评估,如果评估结果是 FALSE将触发错误,如果错误发生,已经提交的数据的处理与对应存储引擎是否支持事务有关,也依赖严格SQL模式是否生效
  • 如果约束表达式所需的数据类型与声明的列类型不一致,数据将参考MySQL的类型转换规则被隐式的转换

约束表达式在不同的SQL模式下,可能返回不同的结果

另外,在 INFORMATION_SCHEMACHECK_CONSTRAINTS表中存放着所有表中定义的 CHECK约束的信息。

建议使用CHECK约束的场景

从架构的角度来看,复杂业务场景中的约束可以通过不同的方式实现:

[En]

Constraints in complex business scenarios can be implemented in different ways from an architectural point of view:

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

    put it in the database table and implement it through constraints, but subqueries are not supported*

  • 放在数据库中,通过触发器(TRIGGER)实现
  • 把它放在应用程序的逻辑中,在推进数据库之前进行检查
    [En]

    put it in the logic of the application and check before advancing the database*

一般来说,选择不同方法的原则如下:

[En]

In general, the principles for choosing different approaches are as follows:

  • 如果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;
  • 如果属于数据库逻辑,比如AUDIT,外键可以使用触发器
    [En]

    if it belongs to database logic, such as audit, foreign keys can use triggers*

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_checkBEFORE INSERT ON departmentFOR EACH ROWBEGIN  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;ENDCREATE TRIGGER pid_delete_checkBEFORE DELETE ON departmentFOR EACH ROWBEGIN  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
  • 如果属于业务逻辑,建议在应用层处理,方便开发者理解和维护。但也要加强业务管理,避免特权用户偶尔操作造成数据完整性破坏。
    [En]

    if it belongs to business logic, it is recommended to deal with it in the application layer to facilitate developers to understand and maintain. However, it is also necessary to strengthen business management to avoid the destruction of data integrity caused by occasional operations by privileged users.*

Enjoy GreatSQL 😃

文章推荐:

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。

微信&QQ群:

QQ群: 533341697

微信群:可搜索添加 GreatSQL&#x793E;&#x533A;&#x52A9;&#x624B;微信好友,发送验证信息”加群”加入GreatSQL/MGR交流微信群

GreatSQL社区助手: wanlidbc

Original: https://www.cnblogs.com/greatsql/p/16558491.html
Author: GreatSQL
Title: 有趣的特性:CHECK约束

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505083/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • [Mysql]null与真值

    SQL的逻辑运算使用的是三值逻辑,逻辑表达式的计算结果有三种可能, true, false, unknown 比较运算的表达式含 NULL时会产生 unknown结果,例如 SEL…

    数据库 2023年6月16日
    0141
  • MySQL函数学习(二)—–数值型函数

    注:笔记旨在记录 二、MySQL 数值型函数 \ 函 数 名 称 作 用 完 成 1 RAND 取随机数,可设置参数种子 勾 2 ABS 求x的绝对值 勾 3 SIGN 求x的正负…

    数据库 2023年6月16日
    0139
  • mysql索引

    MySQL索引: MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 1.创建索引1.1单独创建索引 1.2修改表结构创建索引 1.3创…

    数据库 2023年6月11日
    0134
  • javaScript知识大全(基础)!!

    定义方法:即把函数放在对象里面,对象只有属性和方法 var a={ name:"111", birth:2000, age:function (){ var n…

    数据库 2023年6月16日
    0119
  • 优化 JS 程序的一个小方法

    就像在学习之前先要识字,我想在介绍优化 JavaScript 代码之前,先介绍一下自己对编程语言的理解。故事要从一只叫做 Theseus 的机械鼠和其发明人克劳德-香农(Claud…

    数据库 2023年6月14日
    0157
  • Maven配置私有仓库

    前言 当公司或个人具有自己独有的jar时,不想公开,一般就会放在自己的私有Maven仓库中,在项目中需要引用,此时就需要将公司私有仓库配置到maven当中,一般我们的maven配置…

    数据库 2023年6月16日
    0216
  • 23种设计模式之命令模式

    文章目录 概述 命令模式的优缺点 命令模式的应用场景 命令模式的结构和实现 * 模式的结构 模式的实现 总结 ; 概述 命令模式(Command Pattern)是一种数据驱动的设…

    数据库 2023年6月6日
    0171
  • 面试记录

    JVM线程属于用户态还是内核态 当进程运行在ring3级别时为用户态,ring0级别时为内核态 有些操作需要有内核权限才能进行,那么有三种由用户态切换到内核态的情况: 系统调用:操…

    数据库 2023年6月16日
    0163
  • FastDFS客户端与django自定义文件存储系统

    1. FastDFS的Python客户端 python版本的FastDFS客户端使用说明参考https://github.com/jefforeilly/fdfs_client-p…

    数据库 2023年6月14日
    0131
  • Git

    404. 抱歉,您访问的资源不存在。 可能是网址有误,或者对应的内容被删除,或者处于私有状态。 代码改变世界,联系邮箱 contact@cnblogs.com 园子的商业化努力-困…

    数据库 2023年6月16日
    0134
  • jdbc-使用工具类

    package com.cqust; import com.cqust.utils.JDBCUtil; import java.sql.Connection;import java…

    数据库 2023年6月11日
    0138
  • MySQL数据类型(精)

    数据类型(精) MySQL中的数据类型 整型类型 类型介绍 可选属性 M 显示宽度 不会影响类型的实际宽度 设置字段f1,f2,f3 f1 INT, f2 INT(5), f3 I…

    数据库 2023年5月24日
    0161
  • 一段文字

    https://book.douban.com/review/13674387/现代人的困境,其实从出生开始每个人都有强烈的感受。我们会按户口管理,强调身份的是各种标签,各种统计数…

    数据库 2023年6月11日
    0115
  • 工作中常用Less知识点实践总结

    工作中常用Less知识点实践总结,帮助你更好的使用Less 我所理解的Less的一些好处 函数式编程css 自定义变量用于整体主题调整 嵌套语法简化开发复杂度 mixin的写法 ….

    数据库 2023年6月11日
    0137
  • Windows 是最安全的操作系统

    建了一个用户交流群,我在群里说:”Windows 是最安全的操作系统。” 立刻引发了很多有意思的观点。我在群里一个人说不过大家,先篇文章把自己的论点罗列一下…

    数据库 2023年6月6日
    0301
  • MySQL实战45讲 17

    17 | 如何正确地显示随机消息? 场景:从词汇表中随机选择三个单词。 [En] Scene: three words are randomly selected from a v…

    数据库 2023年5月24日
    0151
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球