在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

MySQL 8.0 新特性之檢查約束（CHECK）

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        MySQL 8.0.15 之前
        MySQL 8.0.16 之后
            列級檢查約束
            表級檢查約束
            強(qiáng)制選項(xiàng)
            檢查約束限制
        總結(jié)

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

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

我們常見的數(shù)據(jù)庫都實(shí)現(xiàn)了檢查約束，例如 Oracle、SQL Server、PostgreSQL 以及 SQLite；然而 MySQL 一直以來沒有真正實(shí)現(xiàn)該功能，直到最新的 MySQL 8.0.16。
MySQL 8.0.15 之前

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

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 選項(xiàng)，但最終的表結(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 之前實(shí)現(xiàn)類似的檢查約束，可以使用觸發(fā)器；或者創(chuàng)建一個包含 WITH CHECK OPTION 選項(xiàng)的視圖，然后通過視圖插入或修改數(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ù)用于給約束指定一個名稱。如果省略該選項(xiàng)，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 子句用于指定是否強(qiáng)制該約束：

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

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

列級約束只能出現(xiàn)在字段定義之后，而且只能針對該字段進(jì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；因?yàn)樗凑彰峙旁诘谝唬纱艘部梢钥闯?MySQL 按照約束的名字排序依次進(jìn)行檢查。

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

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


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

表級約束獨(dú)立于字段的定義，而且可以針對多個字段進(jìn)行約束，甚至可以出現(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，不違法約束。
強(qiáng)制選項(xiàng)

使用默認(rèn)方式或者 ENFORCED 選項(xiàng)創(chuàng)建的約束處于強(qiá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ù)遷移并處理完成之后，再次啟用強(qiáng)制選項(xiàng)。
檢查約束限制

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

    允許使用非計算列和計算列，但是不允許使用 AUTO_INCREMENT 字段或者其他表中的字段。
    允許使用字面值、確定性內(nèi)置函數(shù)（即使不同用戶，多次調(diào)用該函數(shù)，只要輸入相同結(jié)果就相同）以及運(yùn)算符。非確定性函數(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 實(shí)現(xiàn)業(yè)務(wù)完整性約束的能力，也使得 MySQL更加遵循 SQL 標(biāo)準(zhǔn)。??

如果覺得文章對你有用，請不要白嫖！歡迎關(guān)注??、評論??、點(diǎn)贊??！