功能说明
在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时，约束就被违反，产生的效果与执行的语句有关 - 可选执行子句标识是否需要强制执行约束：
[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, UNIQUE， FOREIGN 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 UPDATE，ON DELETE被禁止在包含CHECK约束的列使用，相应的，CHECK约束也被禁止在使用外键参考动作的列使用 CHECK约束在插入、更新、替换(REPLACE)和LOAD DATA/XML语句的时候被评估，如果评估结果是FALSE将触发错误，如果错误发生，已经提交的数据的处理与对应存储引擎是否支持事务有关，也依赖严格SQL模式是否生效- 如果约束表达式所需的数据类型与声明的列类型不一致，数据将参考MySQL的类型转换规则被隐式的转换
约束表达式在不同的SQL模式下，可能返回不同的结果
另外，在 INFORMATION_SCHEMA的 CHECK_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社区助手微信好友，发送验证信息”加群”加入GreatSQL/MGR交流微信群
GreatSQL社区助手： wanlidbc
Original: https://www.cnblogs.com/greatsql/p/16558491.html
Author: GreatSQL
Title: 有趣的特性：CHECK约束
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505083/
转载文章受原作者版权保护。转载请注明原作者出处！