check mysql is_MySQL 检查约束(check constraint)

什么是检查约束(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 

检查约束(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: 0mysql> 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: 0mysql> 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)