Mysql 8 - 检查约束

Dear 丶 2023-01-23 03:56 10阅读 0赞

## 检查约束 ##

从`MySQL 8.0.16`开始，`CREATE TABLE`允许所有存储引擎的表和列使用检查约束的核心功能 。 `CREATE TABLE`允许以下检查约束语法，用于表约束和列约束：

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

可选`symbol`指定约束的名称。如果省略，MySQL会使用**表名\_chk\_序数**（1、2、3…）生成一个名称。约束名称的最大长度为 `64`个字符，区分大小写。

举例：`age`字段不小于`18`。

drop database if exists jack;
    create database jack;
    create table jack.kaven (
        id int auto_increment primary key ,
        age int not null ,
        check ( age >= 18 )
    );
    show create table jack.kaven;
    insert into jack.kaven(age) value (20);
    insert into jack.kaven(age) value (18);
    insert into jack.kaven(age) value (17);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70]  
`expr`将约束条件指定为布尔表达式，该表达式必须为表的每一行数据进行评估，得到`TRUE`或`UNKNOWN`（对于`NULL`值）。如果约束条件评估为`FALSE`，则它失败并违反了约束。违反约束的影响取决于正在执行的语句。

举例：`age`字段不小于`18`。

drop database if exists jack;
    create database jack;
    create table jack.kaven (
        id int auto_increment primary key ,
        age int ,
        constraint age_check check ( age >= 18 ) enforced
    );
    insert into jack.kaven(age) value (null);
    insert into jack.kaven(age) value (18);
    select * from jack.kaven;
    insert into jack.kaven(age) value (17);
    select * from jack.kaven;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 1]

可选的执行子句指示是否强制执行约束：

*  如果省略或指定为`ENFORCED`，则会创建并强制执行约束。

不满足约束的数据行不能插入成功，其他同理。

drop database if exists jack;
    create database jack;
    create table jack.kaven (
        id int auto_increment primary key ,
        age int not null ,
        check ( age >= 18 ) enforced
    );
    insert into jack.kaven(age) value (20);
    insert into jack.kaven(age) value (18);
    select * from jack.kaven;
    insert into jack.kaven(age) value (17);
    select * from jack.kaven;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 2]

*  如果指定为`NOT ENFORCED`，则会创建约束但不强制执行。

不满足约束的数据行也能插入成功，其他同理。

drop database if exists jack;
    create database jack;
    create table jack.kaven (
        id int auto_increment primary key ,
        age int not null ,
        check ( age >= 18 ) not enforced
    );
    insert into jack.kaven(age) value (20);
    insert into jack.kaven(age) value (18);
    insert into jack.kaven(age) value (17);
    select * from jack.kaven;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 3]

一个检查约束被指定为任一表约束或列约束：

*  表约束不会出现在列定义中，并且可以引用表中的列。允许引用出现在表定义后面的列。
 *  列约束出现在列定义中，并且只能引用该列。

举例：`password`字段长度满足区间`[8,16]`，`age`字段不小于`18`。

drop database if exists jack;
    create database jack;
    create table jack.kaven (
        constraint password_check check ( length(password) >=8 and length(password) <= 16) ,
        id int auto_increment primary key ,
        age int not null constraint age_check check ( age >= 18 ),
        password varchar(256) not null
    );
    show create table jack.kaven;
    insert into jack.kaven(age , password) value (20 , '12345678');
    insert into jack.kaven(age , password) value (18 , '1234567890');
    insert into jack.kaven(age , password) value (25 , '12345678_12345678');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 4]

SQL 标准规定所有类型的约束（主键、唯一索引、外键、检查）都属于同一个命名空间。在 MySQL 中，每个视图（数据库）的每种约束类型都有自己的命名空间。因此， 每个视图的检查约束名称必须是唯一的；同一视图中的两个表不能共享检查约束名称。

drop database if exists jack;
    create database jack;
    create table jack.kaven (
        id int auto_increment primary key ,
        age int not null ,
        constraint age_check check ( age >= 18 ) enforced
    );
    create table jack.kaven2 (
        id int auto_increment primary key ,
        age int not null ,
        constraint age_check check ( age >= 20 ) enforced
    );

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 5]

**例外： 临时表隐藏了同名的非临时表，因此它可以有相同的检查约束名。**

drop database if exists jack;
    create database jack;
    create table jack.kaven (
        id int auto_increment primary key ,
        age int not null ,
        constraint age_check check ( age >= 18 ) enforced
    );
    create temporary table jack.kaven2 (
        id int auto_increment primary key ,
        age int not null ,
        constraint age_check check ( age >= 20 ) enforced
    );

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 6]

以表名开头生成的约束名称有助于确保在视图中的唯一性，因为表名在视图中也必须是唯一的。

检查条件表达式必须遵守以下规则。如果表达式包含不允许的构造，则会发生错误。

*  允许非生成列和生成列，具有`AUTO_INCREMENT`属性的列和其他表中的列除外。
 *  允许使用文字、确定性内置函数和运算符。如果给定表中具有相同的数据，多个独立于连接的用户的调用都会产生相同的结果，则函数是确定性的。非确定性函数和未通过此定义的函数如：`CONNECTION_ID()`、 `CURRENT_USER()`、 `NOW()`。
 *  不允许存储函数和可加载函数。
 *  不允许存储过程和函数参数。
 *  不允许使用变量（系统变量、用户定义的变量和存储的程序局部变量）。
 *  不允许子查询。

`AUTO_INCREMENT`属性的列不能使用检查约束。

drop database if exists jack;
    create database jack;
    create table jack.kaven (
        id int auto_increment primary key ,
        constraint id_check check ( id > 0 ) enforced
    );
    insert ignore into jack.kaven(id) value (1);
    insert ignore into jack.kaven(id) value (10);
    select * from jack.kaven;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 7]

检查约束使用的列上禁止外键引用操作(`ON UPDATE`, `ON DELETE`)。同样，外键引用操作使用的列也禁止使用检查约束。

检查约束为 `INSERT`、 `UPDATE`、 `REPLACE`、 `LOAD DATA`和 `LOAD XML`语句评估约束，如果约束评估为`FALSE` ，则会发生错误。如果发生错误，事务性和非事务性存储引擎对已应用更改的处理会有所不同（比如是否回滚），并且还取决于严格SQL模式是否有效。

约束评估为`FALSE` ，发生错误。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 8]

检查约束为`INSERT IGNORE`、`UPDATE IGNORE`、 `LOAD DATA ... IGNORE`和 `LOAD XML ... IGNORE`语句评估约束 ， 如果约束评估为`FALSE`，则会出现警告。跳过任何违规行的插入或更新。

drop database if exists jack;
    create database jack;
    create table jack.kaven (
        id int auto_increment primary key ,
        age int not null ,
        constraint age_check check ( age >= 18 ) enforced
    );
    insert ignore into jack.kaven(age) value (17);
    insert ignore into jack.kaven(age) value (18);
    select * from jack.kaven;

出现警告。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 9]  
跳过任何违规行的插入。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 10]

如有错误，欢迎大家评论指正。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70]: /images/20221004/1dc43223e7524104a70e69b33a7a3ed3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 1]: /images/20221004/f4a072c29b51429f9ac37561cabf27a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 2]: /images/20221004/fca4590e02084b73b852a7a7e3426432.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 3]: /images/20221004/0f0c7bbdad114fe58acb235b94accff2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 4]: /images/20221004/fd6835941b64439786dca60d5b95359d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 5]: /images/20221004/dd56d478b6254fc3aefd1913943da325.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 6]: /images/20221004/86359de19f0d488e8c90b54992303e25.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 7]: /images/20221004/350f22c120eb43129454c968af7ee06e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 8]: /images/20221004/a1ebd296223c4535ac7466eb79e52186.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 9]: /images/20221004/e7b82bc2183444cd8ddac6a055fe2f27.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3OTYwNjAz_size_16_color_FFFFFF_t_70 10]: /images/20221004/35b088f14a0f488c9fa8698117eac035.png