五月花无码视频在线观看,亚洲午夜国产精品无码老牛影视

主頁 > 知識庫 > MySQL 8.0 新特性之檢查約束的實現(xiàn)

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

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

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

我們常見的數據庫都實現(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 選項，但最終的表結構中不包含任何檢查約束。這也意味著我們可以插入非法的數據：

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 選項的視圖，然后通過視圖插入或修改數據。

MySQL 8.0.16 之后

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

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

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

expr 是一個布爾表達式，用于指定約束的條件；表中的每行數據都必須滿足 expr 的結果為 TRUE 或者 UNKNOWN（NULL）。如果表達式的結果為 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 標準中所有的約束（主鍵、唯一約束、外鍵、檢查約束等）都屬于相同的命名空間，意味著它們相互不能重名。但在 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 值并不會違反檢查約束。

表級檢查約束

表級約束獨立于字段的定義，而且可以針對多個字段進行約束，甚至可以出現(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 在所有字段定義之后。

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

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 選項創(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)。我們插入一條違反該約束的數據：

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

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

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

檢查約束限制

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

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

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

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

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

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

總結

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

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

您可能感興趣的文章:

MySQL 8.0新特性 — 檢查性約束的使用簡介
MySQL 8.0新特性之隱藏字段的深入講解
解析MySQL8.0新特性——事務性數據字典與原子DDL
MySQL 8.0 新特性之哈希連接（Hash Join）
淺談mysql8.0新特性的坑和解決辦法(小結)
MySQL8.0新特性之支持原子DDL語句
MySQL 8.0的關系數據庫新特性詳解
IDEA連接不上MySQL端口號占用的解決
在Ubuntu/Linux環(huán)境下使用MySQL開放/修改3306端口和開放訪問權限
完美解決phpstudy安裝后mysql無法啟動（無需刪除原數據庫，無需更改任何配置，無需更改端口）直接共存
linux下mysql開啟遠程訪問權限防火墻開放3306端口
MySQL 8.0新特性 — 管理端口的使用簡介

標簽：移動珠海徐州鹽城沈陽黔東沈陽拉薩

巨人網絡通訊聲明：本文標題《MySQL 8.0 新特性之檢查約束的實現(xiàn)》，本文關鍵詞 MySQL,8.0,新特性,新,特性,；如發(fā)現(xiàn)本文內容存在版權問題，煩請?zhí)峁┫嚓P信息告之我們，我們將及時溝通與處理。本站內容系統(tǒng)采集于網絡，涉及言論、版權與本站無關。