社区投稿 | MySQL 8.0.16 开始支持 check 完整性

原创： 蒋乐兴

---

作者简介

蒋乐兴，MySQL DBA，擅长 python 和 SQL ，目前维护着github的两个开源项目：mysqltools 、dbmc以及独立博客：https://www.sqlpy.com。

 

一直以来MySQL都只实现了实体完整性、域完整性、引用完整性、唯一键约束。

唯独 check 完整性迟迟没有到来，MySQL 8.0.16(2019-04-25 GA) 版本为这个画上了句号。

 

一、没有 check 完整性约束会怎样

1、没有 check 完整性约束可能会影响到数据的质量。

-- 创建一个 person 表来保存名字，年龄这个两个基本信息
CREATE TABLE person ( NAME VARCHAR ( 16 ), age INT );
-- 这种情况下可以插入一个年龄为 -32 的行，负的年龄明显是没有意义的
INSERT INTO person ( NAME, age )
VALUE
( '张三岁',- 32 );
select * from person;
+-----------+------+
| name | age |
+-----------+------+
| 张三岁 | -32 |
+-----------+------+
1 row in set (0.00 sec)

 

2、如果单单只是不能容忍负值，我们可以换一种非负整数类型来克服一下。

-- 先删除之前的 person 定义
drop table if exists person;
Query OK, 0 rows affected (0.01 sec)
-- 创建一个新的 person 表
create table if not exists person(name varchar(16),age int unsigned);
Query OK, 0 rows affected (0.01 sec)
-- 声明的时候说明了 age 只能是正数，所以插入负数就会报错
insert into person(name,age) value('张三岁',-32);
ERROR 1264 (22003): Out of range value for column 'age' at row 1

上面的这种解决方案其实就是通过域完整性来实现的数据验证，域完整性的能力还是有边界的。

比如说，要求age一定要18岁之上它是做不到的，而这种需求正是check完整性大显身手的地方。

 

二、看用 check 如何解决

1、MySQL  8.0.16+版本， check 完整性解决age要大于18的问题。

select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.16 |
+-----------+
1 row in set (0.00 sec)
-- 先删除之前定义的表
drop table if exists person;
Query OK, 0 rows affected (0.01 sec)
-- 创建新的表，并对 age 列进行验证，要求它一定要大于 18
create table if not exists person(
name varchar(16),
age int,
constraint ck_person_001 check (age > 18) -- 加一个 check 约束条件
);
Query OK, 0 rows affected (0.02 sec)
-- 查看表的定义
show create table person;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| person | CREATE TABLE `person` (
`name` varchar(16) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
CONSTRAINT `ck_person_001` CHECK ((`age` > 18))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 插入的数据如果不能通过 check 约束就会报错
insert into person(name,age) value('张三岁',-32);
ERROR 3819 (HY000): Check constraint 'ck_person_001' is violated.
insert into person(name,age) values('张三岁',17);
ERROR 3819 (HY000): Check constraint 'ck_person_001' is violated.
insert into person(name,age) values('张三岁',19);
Query OK, 1 row affected (0.01 sec)

 

2、MySQL 8.0.16 以下版本的 MySQL 会怎样?

select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.15 |
+-----------+
1 row in set (0.00 sec)
drop table if exists person;
Query OK, 0 rows affected (0.00 sec)
create table if not exists person(
name varchar(16),
age int,
constraint ck_person_001 check (age > 18) -- 加一个 check 约束条件
);
Query OK, 0 rows affected (0.01 sec)
-- 可以看到在低版本下 check 约束被直接无视了，也就是说低版本是没有 check 约束的
show create table person;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| person | CREATE TABLE `person` (
`name` varchar(16) COLLATE utf8mb4_general_ci DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
--
insert into person(name,age) value('张三岁',-32);
Query OK, 1 row affected (0.00 sec)

 

可以看到在 MySQL 8.0.16 以下的版本中，check直接被忽略。

新版本在CHECK Constraints功能上的完善提高了对非法或不合理数据写入的控制能力。

除了以上示例中的列约束之外，CHECK Constraints还支持表约束。

想要了解更多关于CHECK Constraints 的详细语法规则和注意事项，请参考MySQL官网文档

https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

转载于:https://my.oschina.net/actiontechoss/blog/3070083