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

最新推荐文章于 2024-06-30 11:54:29 发布

susieforever

最新推荐文章于 2024-06-30 11:54:29 发布

阅读量136

点赞数

文章标签： mysql 域完整性

本文链接：https://blog.csdn.net/weixin_34378148/article/details/113912931

版权

原创：蒋乐兴

作者简介

蒋乐兴，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官网文档