英文搜索网站,一般做网站要多少钱,做包装的网站有哪些,上海公司建设网站在 MySQL 中#xff0c;直到 MySQL 8.0.16 版本之前#xff0c;标准 SQL 的 CHECK 约束并未被完全支持。然而#xff0c;从 MySQL 8.0.16 开始#xff0c;CHECK 约束在表定义中得到了支持#xff0c;允许你定义列中必须满足的条件。
使用 CHECK 约束#xff0c;你可以确…在 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、测试用例
在这个示例中我们创建了一个名为 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 wrapperConnection id: 2
Current database: superdb
Current user: rootlocalhost
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 secThreads: 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_age18)
);
-- 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、创建表t_check插入满足 CHECK 约束的数据
在这个示例中我们创建了一个名为 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_age18)
- );
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 约束的数据
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 约束的数据
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、给已经创建好的表增加约束
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_age18);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有效检查并强制约束执行效果如下
mysql insert into t_check2 values(2,column_check_002,17);
ERROR 3819 (HY000): Check constraint t_check2_chk_1 is violated.3、约束的相关查询及管理
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_SCHEMAsuperdb- and TABLE_NAMEt_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_SCHEMAsuperdb- and TABLE_NAMEt_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_SCHEMAsuperdb- and TABLE_NAMEt_check;
Empty set (0.00 sec)
-- 因没有外键约束,查出为空4、 删除 CHECK 约束
要删除一个 CHECK 约束你需要知道它的名字如果在创建时指定了的话。但如果你没有指定名字你可能需要删除整个表并重新创建它或者使用其他方法如触发器来模拟 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
再次插入之前不满足条件的数据则执行成功
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’ 中的一个。
