柚子快報邀請碼778899分享：MySQL CHECK約束

http://yzkb.51969.com/

在 MySQL 中，直到 MySQL 8.0.16 版本之前，標準 SQL 的 CHECK 約束并未被完全支持。然而，從 MySQL 8.0.16 開始，CHECK 約束在表定義中得到了支持，允許你定義列中必須滿足的條件。

使用 CHECK 約束，你可以確保在插入或更新記錄時，某列或某組列的值滿足特定的條件。

以下是如何在 MySQL 中使用 CHECK 約束

1、MySQL5.7.X CHECK 約束

MySQL5.7.X CHECK 約束是無效的，只做檢查CHECK ，不強制CHECK

1.1、測試用例

在這個示例中，我們創(chuàng)建了一個名為 t_check 的表，其中有一個c_age 列。CHECK約束確保c_age 列的值在大于等于18。

select @@version 檢查MySQL版本信息

mysql> select @@version;

+------------+

| @@version |

+------------+

| 5.7.28-log |

+------------+

1 row in set (0.00 sec)

mysql> status

--------------

mysql Ver 14.14 Distrib 5.7.28, for linux-glibc2.12 (x86_64) using EditLine wrapper

Connection id: 2

Current database: superdb

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.28-log MySQL Community Server (GPL)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /tmp/mysql.sock

Uptime: 6 min 59 sec

Threads: 1 Questions: 18 Slow queries: 0 Opens: 111 Flush tables: 1 Open tables: 26 Queries per second avg: 0.042

--------------

create table t_check

( id int not null primary key,

c_name varchar(32),

c_age int check(c_age>=18)

);

-- insert

mysql> insert into t_check values(1,'column_check_001',18);

Query OK, 1 row affected (0.04 sec)

mysql> insert into t_check values(2,'column_check_002',17);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_check values(3,'column_check_003',30);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_check;

+----+------------------+-------+

| id | c_name | c_age |

+----+------------------+-------+

| 1 | column_check_001 | 18 |

| 2 | column_check_002 | 17 |

| 3 | column_check_003 | 30 |

+----+------------------+-------+

3 rows in set (0.00 sec)

2、MySQL8.X CHECK 約束

MySQL8.0.X check是有效的，做檢查CHECK ,強制CHECK

2.1、創(chuàng)建表t_check，插入滿足 CHECK 約束的數(shù)據(jù)

在這個示例中，我們創(chuàng)建了一個名為 t_check 的表，其中有一個c_age 列。CHECK約束確保c_age 列的值在大于等于18。

mysql> create table t_check

-> ( id int not null primary key,

-> c_name varchar(32),

-> c_age int check(c_age>=18)

-> );

Query OK, 0 rows affected (0.15 sec)

mysql> insert into t_check values(1,'column_check_001',18);

Query OK, 1 row affected (0.01 sec)

2.2、嘗試插入不滿足 CHECK 約束的數(shù)據(jù)：

mysql> insert into t_check values(2,'column_check_002',17);

ERROR 3819 (HY000): Check constraint 't_check_chk_1' is violated.

2.3、插入滿足 CHECK 約束的數(shù)據(jù)

mysql> insert into t_check values(3,'column_check_003',30);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from t_check;

+----+------------------+-------+

| id | c_name | c_age |

+----+------------------+-------+

| 1 | column_check_001 | 18 |

| 3 | column_check_003 | 30 |

+----+------------------+-------+

2 rows in set (0.00 sec)

2.4、給已經(jīng)創(chuàng)建好的表增加約束

create table t_check2

( id int not null primary key,

c_name varchar(32),

c_age int

);

alter table t_check2 add constraint check(c_age>=18);

insert into t_check2 values(1,'column_check_001',18);

insert into t_check2 values(2,'column_check_002',17);

insert into t_check2 values(3,'column_check_003',30);

commit;

select * from t_check2;

同上用例測試 MySQL8.0 ,check有效檢查并強制約束，執(zhí)行效果如下

mysql> insert into t_check2 values(2,'column_check_002',17);

ERROR 3819 (HY000): Check constraint 't_check2_chk_1' is violated.

3、約束的相關(guān)查詢及管理

3.1、8.0.X 視圖information_schema.CHECK_CONSTRAINTS

SELECT * FROM information_schema.CHECK_CONSTRAINTS ;

mysql> SELECT * FROM information_schema.CHECK_CONSTRAINTS ;

+--------------------+-------------------+-----------------+-----------------+

| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE |

+--------------------+-------------------+-----------------+-----------------+

| def | db01 | t_check2_chk_1 | (`c_age` >= 18) |

+--------------------+-------------------+-----------------+-----------------+

select * from information_schema.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA=‘superdb’ and TABLE_NAME=‘t_check2’;

mysql> select * from information_schema.TABLE_CONSTRAINTS

-> where CONSTRAINT_SCHEMA='superdb'

-> and TABLE_NAME='t_check2';

+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+

| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |

+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+

| def | superdb | PRIMARY | superdb | t_check2 | PRIMARY KEY | YES |

| def | superdb | t_check2_chk_1 | superdb | t_check2 | CHECK | YES |

+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+

2 rows in set (0.00 sec)

3.2、查看表的主鍵約束信息

select * from information_schema.KEY_COLUMN_USAGE kcu where CONSTRAINT_SCHEMA=‘superdb’ and TABLE_NAME=‘t_check’;

mysql> select * from information_schema.KEY_COLUMN_USAGE kcu

-> where CONSTRAINT_SCHEMA='superdb'

-> and TABLE_NAME='t_check';

+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+

| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |

+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+

| def | superdb | PRIMARY | def | superdb | t_check | id | 1 | NULL | NULL | NULL | NULL |

+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+---

3.3、查看表的外鍵約束信息

select * from information_schema.REFERENTIAL_CONSTRAINTS rc where CONSTRAINT_SCHEMA=‘superdb’ and TABLE_NAME=‘t_check’;

mysql> select * from information_schema.REFERENTIAL_CONSTRAINTS rc

-> where CONSTRAINT_SCHEMA='superdb'

-> and TABLE_NAME='t_check';

Empty set (0.00 sec)

-- 因沒有外鍵約束,查出為空

4、 刪除 CHECK 約束：

要刪除一個 CHECK 約束，你需要知道它的名字（如果在創(chuàng)建時指定了的話）。但如果你沒有指定名字，你可能需要刪除整個表并重新創(chuàng)建它，或者使用其他方法（如觸發(fā)器）來模擬 CHECK 約束的行為。

mysql> alter table t_check2 drop constraint t_check2_chk_1;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

再次插入之前不滿足條件的數(shù)據(jù)，則執(zhí)行成功

mysql> insert into t_check2 values(2,'column_check_002',17);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_check2;

+----+------------------+-------+

| id | c_name | c_age |

+----+------------------+-------+

| 1 | column_check_001 | 18 |

| 2 | column_check_002 | 17 |

| 3 | column_check_003 | 30 |

+----+------------------+-------+

3 rows in set (0.00 sec)

5、在多個列上使用 CHECK 約束：

你也可以在多個列上使用 CHECK 約束，例如：

CREATE TABLE t_students (

id INT AUTO_INCREMENT PRIMARY KEY,

age INT,

grade CHAR(1),

CHECK (age >= 0 AND age <= 100),

CHECK (grade IN ('A', 'B', 'C', 'D', 'F'))

);

在這個示例中，我們添加了一個額外的 CHECK 約束來確保 grade 列的值是 ‘A’、‘B’、‘C’、‘D’ 或 ‘F’ 中的一個。

柚子快報邀請碼778899分享：MySQL CHECK約束

http://yzkb.51969.com/

文章鏈接