Mysql 8 - 检查约束

2023-08-07 18:24:22

检查约束

从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);

expr将约束条件指定为布尔表达式，该表达式必须为表的每一行计算TRUE或UNKNOWN（对于NULL值）。如果约束条件评估为FALSE，则它失败并发生违反约束。违反的影响取决于正在执行的语句。

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

如果省略或指定为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;

如果指定为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;

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

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

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');

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
);

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

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
);

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

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

允许非生成列和生成列，具有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;

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

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

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 into jack.kaven(age) value (17)

约束评估为FALSE ，发生错误。

检查约束为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;

出现警告。

跳过任何违规行的插入。

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

码农公寓

检查约束

相关文章