MySQL 8.0新特性 — 检查性约束

原创

brightdeng@DBA

发布于 2020-12-18 10:31:40

1.3K0

发布于 2020-12-18 10:31:40

文章被收录于专栏：腾讯云数据库专家服务腾讯云数据库专家服务

前言

在MySQL 8.0版本中，引入了一个非常有用的新特性 — 检查性约束，它可以提高对非法或不合理数据写入的控制能力；接下来我们就来详细了解一下。

检查性约束

创建、删除与查看

（1）可以在建表时，创建检查性约束

mysql> CREATE TABLE t1
    -> (
    ->   CHECK (c1 <> c2),
    ->   c1 INT CHECK (c1 > 10),
    ->   c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
    ->   c3 INT CHECK (c3 < 100),
    ->   CONSTRAINT c1_nonzero CHECK (c1 <> 0),
    ->   CHECK (c1 > c3)
    -> );
Query OK, 0 rows affected (0.03 sec)

（2）也可以通过下列语句，新增检查性约束

mysql> ALTER TABLE t1 ADD CONSTRAINT c3_nonzero CHECK ((c3<>0));
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

（3）可以通过下列语句，删除检查性约束

mysql> ALTER TABLE t1 DROP CONSTRAINT c3_nonzero;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

（4）可以通过查询表结构的方式，查看检查性约束

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 `c2_positive` CHECK ((`c2` > 0)),
  CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),
  CONSTRAINT `t1_chk_2` CHECK ((`c1` > 10)),
  CONSTRAINT `t1_chk_3` CHECK ((`c3` < 100)),
  CONSTRAINT `t1_chk_4` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

（5）也可以通过下面两个视图查看，其中table_constraints查询表存在哪些约束，check_constraints查询检查性约束的具体定义

mysql> SELECT * FROM information_schema.table_constraints WHERE table_name='t1';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | test              | c1_nonzero      | test         | t1         | CHECK           | YES      |
| def                | test              | c2_positive     | test         | t1         | CHECK           | YES      |
| def                | test              | t1_chk_1        | test         | t1         | CHECK           | YES      |
| def                | test              | t1_chk_2        | test         | t1         | CHECK           | YES      |
| def                | test              | t1_chk_3        | test         | t1         | CHECK           | YES      |
| def                | test              | t1_chk_4        | test         | t1         | CHECK           | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.check_constraints WHERE constraint_name='c1_nonzero';
+--------------------+-------------------+-----------------+--------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+--------------+
| def                | test              | c1_nonzero      | (`c1` <> 0)  |
+--------------------+-------------------+-----------------+--------------+
1 row in set (0.00 sec)

（6）当插入不符合检查性约束的数据时，会直接报错

mysql> insert into t1 values(0,0,0);
ERROR 3819 (HY000): Check constraint 'c1_nonzero' is violated.

限制

（1）自增列和其他表的列，不支持检查性约束

（2）不确定的函数，如CONNECTION_ID(),CURRENT_USER(),NOW()等，不支持检查性约束

（3）用户自定义函数，不支持检查性约束

（4）存储过程，不支持检查性约束

（5）变量，不支持检查性约束

（6）子查询，不支持检查性约束

总结

检查性约束，还是一个非常不错的功能，可以实现丰富的数据校验场景，大家可以尝试一下。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

登录后参与评论

0 条评论

热度