MySQL 检查约束(check constraint)

原创

mb5ff980b461ced 2021-03-10 16:58:21 ©著作权

文章标签 java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者mb5ff980b461ced的原创作品，请联系作者获取转载授权，否则将追究法律责任

什么是检查约束(check constraint)？

检查约束就是在INSERT或UPDATE操作之前，会根据指定条件CHECK要INSERT或UPDATE的字段值是否满足约束
MySQL在8.0.16之后支持check constraint作为新特性，语法为：

CREATE TABLE t1(  c1 INT CHECK (c1 > 10),  c2 INT CHECK (c2 < 100)  );

在早期版本中，该语法依旧支持，但不会起作用，也就是会被解析，但不会被存储层引用
检查约束(check constraint)前置条件

Nongenerated and generated columns are permitted, except columns with the AUTO_INCREMENT attribute and columns in other tables.
Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().
Stored functions and user-defined functions are not permitted.
Stored procedure and function parameters are not permitted.
Variables (system variables, user-defined variables, and stored program local variables) are not permitted.
Subqueries are not permitted.

检查约束(check constraint)的例子

in MySQL5.7.19:

mysql> select @@version;+------------+| @@version  |+------------+| 5.7.19-log |+------------+1 row in set (0.00 sec)
mysql> create table test_check(id int primary key,name varchar(20),age int CHECK(age > 18));Query OK, 0 rows affected (0.01 sec)
mysql> insert into test_check values(1,'qwe',22);Query OK, 1 row affected (0.01 sec)
mysql> insert into test_check values(2,'asd',17);Query OK, 1 row affected (0.01 sec)

可见，在5.7.19版本中，check语法可以支持，但是并不实际生效

in MySQL8.0.20:

mysql> select @@version;+-----------+| @@version |+-----------+| 8.0.20    |+-----------+1 row in set (0.00 sec)
mysql> create table test_check(id int primary key,name varchar(20),age int CHECK(age > 18));Query OK, 0 rows affected (0.02 sec)mysql> insert into test_check values(1,'qwe',22);Query OK, 1 row affected (0.00 sec)
mysql> insert into test_check values(2,'asd',17);ERROR 3819 (HY000): Check constraint 'test_check_chk_1' is violated.

在8.0.16版本后，不满足约束的值不允许被插入或更新

建表后添加约束：

ALTER TABLE tbl_name ADD CONSTRAINT [symbol] CHECK (expr) [[NOT] ENFORCED];
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

mysql> alter table test_check add constraint chk_id check(id < 100);Query OK, 1 row affected (0.03 sec)Records: 1  Duplicates: 0  Warnings: 0
mysql> show create table test_check;+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table      | Create Table                                                                                                                                                                                                                                                                                            |+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| test_check | CREATE TABLE `test_check` (  `id` int NOT NULL,  `name` varchar(20) DEFAULT NULL,  `age` int DEFAULT NULL,  PRIMARY KEY (`id`),  CONSTRAINT `chk_id` CHECK ((`id` < 100)),  CONSTRAINT `test_check_chk_1` CHECK ((`age` > 18))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

建表后删除约束

ALTER TABLE tbl_name DROP CHECK symbol;
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

mysql> alter table test_check drop constraint chk_id;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table test_check;+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table      | Create Table                                                                                                                                                                                                                                                |+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| test_check | CREATE TABLE `test_check` (  `id` int NOT NULL,  `name` varchar(20) DEFAULT NULL,  `age` int DEFAULT NULL,  PRIMARY KEY (`id`),  CONSTRAINT `test_check_chk_1` CHECK ((`age` > 18))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)