檢查限制
從
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
,則它失敗并違反了限制。違反限制的影響取決于正在執行的語句。
舉例:
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;
可選的執行子句訓示是否強制執行限制:
- 如果省略或指定為
,則會建立并強制執行限制。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;
出現警告。
跳過任何違規行的插入。