MySQL 8.0 新特性之檢查約束的實現

Posted on by

大傢好，我是隻談技術不剪發的 Tony 老師。這次我們來介紹一個 MySQL 8.0 增加的新功能：檢查約束（CHECK ）。

SQL 中的檢查約束屬於完整性約束的一種，可以用於約束表中的某個字段或者一些字段必須滿足某個條件。例如用戶名必須大寫、餘額不能小於零等。

我們常見的數據庫都實現瞭檢查約束，例如 Oracle、SQL Server、PostgreSQL 以及 SQLite；然而 MySQL 一直以來沒有真正實現該功能，直到最新的 MySQL 8.0.16。

MySQL 8.0.15 之前

在 MySQL 8.0.15 以及之前的版本中，雖然 CREATE TABLE 語句允許CHECK (expr)形式的檢查約束語法，但實際上解析之後會忽略該子句。例如

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15  |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1
  -> (
  ->  c1 INT CHECK (c1 > 10),
  ->  c2 INT ,
  ->  c3 INT CHECK (c3 < 100),
  ->  CONSTRAINT c2_positive CHECK (c2 > 0),
  ->  CHECK (c1 > c3)
  -> );
Query OK, 0 rows affected (0.33 sec)

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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

雖然我們在定義時指定瞭各種 CHECK 選項，但最終的表結構中不包含任何檢查約束。這也意味著我們可以插入非法的數據：

mysql> insert into t1(c1, c2, c3) values(1, -1, 100);
Query OK, 1 row affected (0.06 sec)

如果我們想要在 MySQL 8.0.15 之前實現類似的檢查約束，可以使用觸發器；或者創建一個包含 WITH CHECK OPTION 選項的視圖，然後通過視圖插入或修改數據。

MySQL 8.0.16 之後

MySQL 8.0.16 於 2019 年 4 月 25 日發佈，終於帶來瞭我們期待已久的 CHECK 約束功能，而且對於所有的存儲引擎都有效。CREATE TABLE 語句允許以下形式的 CHECK 約束語法，可以指定列級約束和表級約束：

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

其中，可選的 symbol 參數用於給約束指定一個名稱。如果省略該選項，MySQL 將會產生一個以表名開頭、加上 _chk_ 以及一個數字編號（1、2、3 …）組成的名字（table_name_chk_n）。約束名稱最大長度為 64 個字符，而且區分大小寫。

expr 是一個佈爾表達式，用於指定約束的條件；表中的每行數據都必須滿足 expr 的結果為 TRUE 或者 UNKNOWN（NULL）。如果表達式的結果為 FALSE，將會違反約束。

可選的 ENFORCED 子句用於指定是否強制該約束：

  • 如果忽略或者指定瞭 ENFORCED，創建並強制該約束；
  • 如果指定瞭 NOT ENFORCED，創建但是不強制該約束。這也意味著約束不會生效。

CHECK 約束可以在列級指定，也可以在表級指定。

列級檢查約束

列級約束隻能出現在字段定義之後，而且隻能針對該字段進行約束。例如：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.16  |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1
  -> (
  ->  c1 INT CHECK (c1 > 10),
  ->  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  ->  c3 INT CHECK (c3 < 100)
  -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
 `c1` int DEFAULT NULL,
 `c2` int DEFAULT NULL,
 `c3` int DEFAULT NULL,
 CONSTRAINT `c2_positive` CHECK ((`c2` > 0)),
 CONSTRAINT `t1_chk_1` CHECK ((`c1` > 10)),
 CONSTRAINT `t1_chk_2` CHECK ((`c3` < 100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

其中，字段 c1 和 c3 上的檢查約束使用瞭系統生成的名稱；c2 上的檢查約束使用瞭自定義名稱。

SQL 標準中所有的約束（主鍵、唯一約束、外鍵、檢查約束等）都屬於相同的命名空間，意味著它們相互不能重名。但在 MySQL 中，每個數據庫中的約束類型屬於自己的命名空間；因此，主鍵和檢查約束可以重名，但是兩個檢查約束不能重名。

我們插入一條測試數據：

mysql> insert into t1(c1, c2, c3) values(1, -1, 100);
ERROR 3819 (HY000): Check constraint 'c2_positive' is violated.

插入數據的三個字段都違反瞭約束，結果顯示的是違反瞭 c2_positive；因為它按照名字排在第一，由此也可以看出 MySQL 按照約束的名字排序依次進行檢查。

我們再插入一條測試數據：

mysql> insert into t1(c1, c2, c3) values(null, null, null);
Query OK, 1 row affected (0.00 sec)

數據插入成功，所以 NULL 值並不會違反檢查約束。

表級檢查約束

表級約束獨立於字段的定義，而且可以針對多個字段進行約束，甚至可以出現在字段定義之前。例如：

mysql> drop table t1;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t1
  -> (
  ->  CHECK (c1 <> c2),
  ->  c1 INT,
  ->  c2 INT,
  ->  c3 INT,
  ->  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  ->  CHECK (c1 > c3)
  -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
 `c1` int DEFAULT NULL,
 `c2` int DEFAULT NULL,
 `c3` int DEFAULT NULL,
 CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
 CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),
 CONSTRAINT `t1_chk_2` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

第一個約束 t1_chk_1 出現在字段定義之前，但是仍然可以引用 c1 和 c2；第二個約束 c1_nonzero 使用瞭自定義的名稱；第三個約束 t1_chk_2 在所有字段定義之後。

我們同樣插入一些測試數據：

mysql> insert into t1(c1, c2, c3) values(1, 2, 3);
ERROR 3819 (HY000): Check constraint 't1_chk_2' is violated.

mysql> insert into t1(c1, c2, c3) values(null, 2, 3);
Query OK, 1 row affected (0.01 sec)

第一條記錄中的 c1 小於 c3，違反瞭檢查約束 t1_chk_2；第二條記錄中的 c1 為 NULL，檢查約束 t1_chk_2 的結果為 UNKNOWN，不違法約束。

強制選項

使用默認方式或者 ENFORCED 選項創建的約束處於強制檢查狀態，我們也可以將其修改為 NOT ENFORCED，從而忽略檢查：

ALTER TABLE tbl_name
ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED

修改之後的檢查約束仍然存在，但是不會執行檢查。例如：

mysql> alter table t1 
  -> alter check t1_chk_1 not enforced;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
 `c1` int DEFAULT NULL,
 `c2` int DEFAULT NULL,
 `c3` int DEFAULT NULL,
 CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
 CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)) /*!80016 NOT ENFORCED */,
 CONSTRAINT `t1_chk_2` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

從最新的定義可以看出，t1_chk_1 處於 NOT ENFORCED 狀態。我們插入一條違反該約束的數據：

mysql> insert into t1(c1, c2, c3) values(1, 1, 0);
Query OK, 1 row affected (0.01 sec)

該記錄的 c1 和 c2 相等，但是插入成功。

如果我們需要遷移一些低版本的歷史數據時，它們可能會違反新的檢查約束；此時可以先將該約束禁用，等數據遷移並處理完成之後，再次啟用強制選項。

檢查約束限制

MySQL 中的 CHECK 條件表達式必須滿足以下規則，否則無法創建檢查約束：

  • 允許使用非計算列和計算列，但是不允許使用 AUTO_INCREMENT 字段或者其他表中的字段。
  • 允許使用字面值、確定性內置函數（即使不同用戶，多次調用該函數，隻要輸入相同結果就相同）以及運算符。非確定性函數包括：CONNECTION_ID()、CURRENT_USER()、NOW() 等等，它們不能用於檢查約束。
  • 不允許使用存儲函數或者自定義函數。
  • 不允許使用存儲過程和函數參數。
  • 不允許使用變量，包括系統變量、用戶定義變量和存儲程序的局部變量。
  • 不允許使用子查詢。

另外，禁用在 CHECK 約束字段上定義外鍵約束的參照操作（ON UPDATE、ON DELETE）；同理，存在外鍵約束參照操作的字段上也不允許創建 CHECK 約束。

對於 INSERT、UPDATE、REPLACE、LOAD DATA 以及 LOAD XML 語句，如果違反檢查約束將會返回錯誤。此時，對於已經修改的數據處理取決於存儲引擎是否支持事務，以及是否使用瞭嚴格 SQL 模式。

對於 INSERT IGNORE、UPDATE IGNORE、REPLACE、LOAD DATA … IGNORE 以及 LOAD XML … IGNORE 語句，如果違反檢查約束將會返回警告並且跳過存在問題的數據行。

如果約束表達式的結果類型和字段的數據類型不同，MySQL 將會執行隱式類型轉換；如果類型轉換失敗或者丟失精度，將會返回錯誤。

總結

MySQL 8.0.16 新增的檢查約束提高瞭 MySQL 實現業務完整性約束的能力，也使得 MySQL更加遵循 SQL 標準。

到此這篇關於MySQL 8.0 新特性之檢查約束的實現的文章就介紹到這瞭,更多相關MySQL8.0 檢查約束 內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：