功能說(shuō)明

在MySQL 8.0.16以前， CREATE TABLE允許從語(yǔ)法層面輸入下列CHECK約束，但實(shí)際沒有效果：

CHECK (expr)

在 MySQL 8.0.16，CREATE TABLE添加了針對(duì)所有存儲(chǔ)引擎的表和列的CHECK約束的核心特性。CREATE TABLE允許如下針對(duì)表或列的約束語(yǔ)法：

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

• 可選的symbol指定了約束的名稱，如果省略，MySQL會(huì)自動(dòng)生成一個(gè)類似：${table_name}_check_${seq_num}的約束名稱，約束名稱是大小寫敏感的，且最長(zhǎng)可以到64個(gè)字符
• expr設(shè)定了一個(gè)返回值為boolean類型的約束條件，表達(dá)式對(duì)所有的數(shù)據(jù)行評(píng)估的結(jié)果值為：TRUE或UNKNOWN（對(duì) NULL值），當(dāng)值為FALSE時(shí)，約束就被違反，產(chǎn)生的效果與執(zhí)行的語(yǔ)句有關(guān)
• 可選的執(zhí)行子句標(biāo)識(shí)約束是否需要被強(qiáng)制：
  當(dāng)未指定或指定為: ENFORCED時(shí)，約束被創(chuàng)建且生效
  當(dāng)指定為: NOT ENFORCED時(shí)，約束被創(chuàng)建但未生效
• 一個(gè)CHECK約束可以被指定為表約束或列約束
  表約束不會(huì)出現(xiàn)在列定義內(nèi)，可以引用任意多個(gè)或一個(gè)列，且允許引用后續(xù)定義的表列
  列約束出現(xiàn)在列定義內(nèi)，僅允許引用該列

示例如下：

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

以上示例包含了列約束和表約束，命名和未命名的格式：

• 第一個(gè)約束是一個(gè)不包含在任何列定義內(nèi)的表約束，所以允許引用任意列，且引用了后續(xù)定義的列，同時(shí)沒有給出約束名稱，所以MySQL會(huì)給該約束生成一個(gè)名字
• 后續(xù)的3個(gè)約束是包含在列定義內(nèi)的列約束，所有指定引用所在的列
• 最后的兩個(gè)是表約束

如果想查看上述命令所生成的約束名，可以輸入以下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規(guī)范要求：所有約束(包括：PRIMARY KEY, UNIQUE，F(xiàn)OREIGN KEY, CHECK)屬于同一個(gè)命名空間(NAMESPACE)，在MySQL實(shí)現(xiàn)中，所有的約束類型在每個(gè)schema (database)內(nèi)有自己的命名空間。所以，CHECK約束的名稱在SCHEMA內(nèi)必須唯一，也就是說(shuō)不允許有兩張表使用同一個(gè)CHECK約束名稱。(例外：一個(gè)臨時(shí)表可能使用與非臨時(shí)表一樣的約束名稱)

CHECK的條件表達(dá)式必須遵守以下規(guī)則，如果包含不允許的結(jié)構(gòu)，將會(huì)觸發(fā)錯(cuò)誤：

• 非生成列和生成列允許被添加到表達(dá)式，但包含AUTO_INCREMENT屬性的列和其他表的列不允許被加入
• 字面量和確定性(deterministic)的內(nèi)置函數(shù)以及操作符允許被添加到表達(dá)式，確定性的含義是：同樣的數(shù)據(jù)不同用戶的多次調(diào)用的結(jié)果是一致的，非確定性的函數(shù)包括：CONNECTION_ID()，CURRENT_USER()，NOW()
• 存儲(chǔ)函數(shù)和用戶自定義函數(shù)不被允許
• 存儲(chǔ)過(guò)程不被允許
• 變量：系統(tǒng)變量、用戶自定義變量和存儲(chǔ)過(guò)程的本地變量均不被允許使用
• 子查詢不應(yīng)許被使用
• 外鍵參考動(dòng)作，如：ON UPDATE， ON DELETE被禁止在包含CHECK約束的列使用，相應(yīng)的，CHECK約束也被禁止在使用外鍵參考動(dòng)作的列使用
• CHECK約束在插入、更新、替換(REPLACE)和LOAD DATA/XML語(yǔ)句的時(shí)候被評(píng)估，如果評(píng)估結(jié)果是FALSE將觸發(fā)錯(cuò)誤，如果錯(cuò)誤發(fā)生，已經(jīng)提交的數(shù)據(jù)的處理與對(duì)應(yīng)存儲(chǔ)引擎是否支持事務(wù)有關(guān)，也依賴嚴(yán)格SQL模式是否生效
• 如果約束表達(dá)式所需的數(shù)據(jù)類型與聲明的列類型不一致，數(shù)據(jù)將參考MySQL的類型轉(zhuǎn)換規(guī)則被隱式的轉(zhuǎn)換

另外，在INFORMATION_SCHEMA的CHECK_CONSTRAINTS表中存放著所有表中定義的CHECK約束的信息。

建議使用CHECK約束的場(chǎng)景

復(fù)雜業(yè)務(wù)場(chǎng)景下的約束，從架構(gòu)角度看，允許有不同的實(shí)現(xiàn)方式：

放在數(shù)據(jù)庫(kù)表中，通過(guò)約束實(shí)現(xiàn)，但不支持子查詢

放在數(shù)據(jù)庫(kù)中，通過(guò)觸發(fā)器(TRIGGER)實(shí)現(xiàn)

放在應(yīng)用程序的邏輯中，在提前數(shù)據(jù)庫(kù)前檢查

一般性的，選擇不同方式的原則如下：

如果CHECK約束可以實(shí)現(xiàn)，且約束比較穩(wěn)定，一般用CHECK約束實(shí)現(xiàn)，比如：年齡不允許為負(fù)數(shù)，不允許>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;

如果屬于數(shù)據(jù)庫(kù)邏輯，比如：審計(jì)，外鍵可以使用觸發(fā)器

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_check?
BEFORE INSERT ON department?
FOR EACH ROW?
BEGIN
? 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;
END


CREATE TRIGGER pid_delete_check?
BEFORE DELETE ON department?
FOR EACH ROW?
BEGIN
? 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

如果屬于業(yè)務(wù)邏輯，建議放在應(yīng)用層處理，方便開發(fā)者：理解和維護(hù)，但是：也需要通過(guò)強(qiáng)化業(yè)務(wù)管理，避免特權(quán)用戶偶發(fā)操作引起對(duì)數(shù)據(jù)完整性的破壞

到此這篇關(guān)于Mysql中的CHECK約束特性詳解的文章就介紹到這了,更多相關(guān)Mysql中CHECK約束內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論