MySQL 8.0 新特性之檢查約束的實現(xiàn)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。這次我們來介紹一個 MySQL 8.0 增加的新功能：檢查約束（CHECK ）。

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

我們常見的數(shù)據(jù)庫都實現(xiàn)了檢查約束，例如 Oracle、SQL Server、PostgreSQL 以及 SQLite；然而 MySQL 一直以來沒有真正實現(xiàn)該功能，直到最新的 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 選項，但最終的表結(jié)構(gòu)中不包含任何檢查約束。這也意味著我們可以插入非法的數(shù)據(jù)：

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

如果我們想要在 MySQL 8.0.15 之前實現(xiàn)類似的檢查約束，可以使用觸發(fā)器；或者創(chuàng)建一個包含 WITH CHECK OPTION 選項的視圖，然后通過視圖插入或修改數(shù)據(jù)。

MySQL 8.0.16 之后

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

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

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

expr 是一個布爾表達(dá)式，用于指定約束的條件；表中的每行數(shù)據(jù)都必須滿足 expr 的結(jié)果為 TRUE 或者 UNKNOWN（NULL）。如果表達(dá)式的結(jié)果為 FALSE，將會違反約束。

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

• 如果忽略或者指定了 ENFORCED，創(chuàng)建并強制該約束；
• 如果指定了 NOT ENFORCED，創(chuàng)建但是不強制該約束。這也意味著約束不會生效。

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

列級檢查約束

列級約束只能出現(xiàn)在字段定義之后，而且只能針對該字段進行約束。例如：

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 上的檢查約束使用了系統(tǒng)生成的名稱；c2 上的檢查約束使用了自定義名稱。

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

我們插入一條測試數(shù)據(jù)：

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

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

我們再插入一條測試數(shù)據(jù)：

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

數(shù)據(jù)插入成功，所以 NULL 值并不會違反檢查約束。

表級檢查約束

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

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 出現(xiàn)在字段定義之前，但是仍然可以引用 c1 和 c2；第二個約束 c1_nonzero 使用了自定義的名稱；第三個約束 t1_chk_2 在所有字段定義之后。

我們同樣插入一些測試數(shù)據(jù)：

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 的結(jié)果為 UNKNOWN，不違法約束。

強制選項

使用默認(rèn)方式或者 ENFORCED 選項創(chuàng)建的約束處于強制檢查狀態(tài)，我們也可以將其修改為 NOT ENFORCED，從而忽略檢查：

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

修改之后的檢查約束仍然存在，但是不會執(zhí)行檢查。例如：

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 狀態(tài)。我們插入一條違反該約束的數(shù)據(jù)：

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

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

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

檢查約束限制

MySQL 中的 CHECK 條件表達(dá)式必須滿足以下規(guī)則，否則無法創(chuàng)建檢查約束：

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

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

對于 INSERT、UPDATE、REPLACE、LOAD DATA 以及 LOAD XML 語句，如果違反檢查約束將會返回錯誤。此時，對于已經(jīng)修改的數(shù)據(jù)處理取決于存儲引擎是否支持事務(wù)，以及是否使用了嚴(yán)格 SQL 模式。

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

如果約束表達(dá)式的結(jié)果類型和字段的數(shù)據(jù)類型不同，MySQL 將會執(zhí)行隱式類型轉(zhuǎn)換；如果類型轉(zhuǎn)換失敗或者丟失精度，將會返回錯誤。

總結(jié)

MySQL 8.0.16 新增的檢查約束提高了 MySQL 實現(xiàn)業(yè)務(wù)完整性約束的能力，也使得 MySQL更加遵循 SQL 標(biāo)準(zhǔn)。

到此這篇關(guān)于MySQL 8.0 新特性之檢查約束的實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL8.0 檢查約束 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！