有趣的特性：CHECK約束

功能說明

在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時，約束就被違反，產生的效果與執行的陳述句有關
可選的執行子句標識約束是否需要被強制：
- 當未指定或指定為: 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)
);

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

第一個約束是一個不包含在任何列定義內的表約束，所以允許參考任意列，且參考了后續定義的列，同時沒有給出約束名稱，所以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()
存盤函式和用戶自定義函式不被允許
存盤程序不被允許
變數：系統變數、用戶自定義變數和存盤程序的本地變數均不被允許使用
子查詢不應許被使用
外鍵參考動作，如：ON UPDATE， ON DELETE被禁止在包含CHECK約束的列使用，相應的，CHECK約束也被禁止在使用外鍵參考動作的列使用
CHECK約束在插入、更新、替換(REPLACE)和LOAD DATA/XML陳述句的時候被評估，如果評估結果是FALSE將觸發錯誤，如果錯誤發生，已經提交的資料的處理與對應存盤引擎是否支持事務有關，也依賴嚴格SQL模式是否生效
如果約束運算式所需的資料型別與宣告的列型別不一致，資料將參考MySQL的型別轉換規則被隱式的轉換

約束運算式在不同的SQL模式下，可能回傳不同的結果

另外，在INFORMATION_SCHEMA的CHECK_CONSTRAINTS表中存放著所有表中定義的CHECK約束的資訊，

建議使用CHECK約束的場景

復雜業務場景下的約束，從架構角度看，允許有不同的實作方式：

放在資料庫表中，通過約束實作，但不支持子查詢
放在資料庫中，通過觸發器(TRIGGER)實作
放在應用程式的邏輯中，在提前資料庫前檢查

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

如果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;

如果屬于資料庫邏輯，比如：審計，外鍵可以使用觸發器

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

如果屬于業務邏輯，建議放在應用層處理，方便開發者：理解和維護，但是：也需要通過強化業務管理，避免特權用戶偶發操作引起對資料完整性的破壞

Enjoy GreatSQL ??

文章推薦：

面向金融級應用的GreatSQL正式開源

Changes in GreatSQL 8.0.25 (2021-8-18)

MGR及GreatSQL資源匯總

GreatSQL MGR FAQ

在Linux下原始碼編譯安裝GreatSQL/MySQL

關于 GreatSQL

GreatSQL是由萬里資料庫維護的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級應用的MySQL分支版本，

Gitee:
https://gitee.com/GreatSQL/GreatSQL

GitHub:
https://github.com/GreatSQL/GreatSQL

Bilibili：
https://space.bilibili.com/1363850082/favlist

微信&QQ群：

QQ群：533341697

微信群：可搜索添加GreatSQL社區助手微信好友，發送驗證資訊“加群”加入GreatSQL/MGR交流微信群

GreatSQL社區助手：wanlidbc

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/501219.html

標籤：其他

上一篇：SQL Server 遞回+向上統計

下一篇：我說MySQL聯合索引遵循最左前綴匹配原則，面試官讓我回去等通知