MySQL中六大约束详细解析

MySQL中六大约束详细解析
前言

问：约束存在的意义是什么？

答：为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束是对数据准确性的一种提前控制

例如：人员的姓名不能为空，人的年龄只能在0~150岁之间。约束可以对数据库中的数据进行保护。
约束可以在建表的时候直接声明，也可以为已建好的表添加约束。

正文

我们简单说一下约束的几种类型和使用方法

 主键约束

 主键约束理念

这个算是我们最常使用的我们在创建表的时候一般会选用ID做主键约束主键约束用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号，并且这个身份证号唯一，也就是我们的ID 全部都是不能重复也就衍生出很多主键的生成策略 UUID 雪花算法等等

 提出问题

问： 如果我们在创建的时候没有去主动设置一个主键约束怎么办？MySQL数据表使用InnoDB作为存储引擎的时候，数据结构就是使用B+树，而数据本身存储在主键索引上，也就是通常所说的聚簇索引，也就是每个表都需要有个聚簇索引树，但是，在建表的时候却发现可以不用指定主键，那么MySQL对于没有指定主键的表示如何处理的呢？

回答：

既然InnoDB对数据的存储必须依赖于主键，那么对于没有创建主键的表，该怎么办？

InnoDB对聚簇索引处理如下：
- 如果定义了主键，那么InnoDB会使用主键作为聚簇索引
- 如果没有定义主键，那么会使用第一非空的唯一索引（NOT NULL and UNIQUE INDEX）作为聚簇索引
- 如果既没有主键也找不到合适的非空索引，InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id，而且InnoDB 维护了一个全局的 dictsys.row_id，所以未定义主键的表都共享该row_id，每次插入一条数据，都把全局row_id当成主键id，然后全局row_id加1
很明显，缺少主键的表，InnoDB会内置一列用于聚簇索引来组织数据。而没有建立主键的话就没法通过主键来进行索引，查询的时候都是全表扫描，小数据量没问题，大数据量就会出现性能问题。

但是，问题真的只是查询影响吗？不是的，对于生成的ROW_ID，其自增的实现来源于一个全局的序列，而所以有ROW_ID的表共享该序列，这也意味着插入的时候生成需要共享一个序列，那么高并发插入的时候为了保持唯一性就避免不了锁的竞争，进而影响性能。

即便我们没有去主动创建一个主键约束 MySQL 数据库也会创建帮我们去创建一个

 小总结
- 如果定义了主键，那么InnoDB会使用主键作为聚簇索引
- 如果没有定义主键，那么会使用第一非空的唯一索引（NOT NULL and UNIQUE INDEX）作为聚簇索引
- 如果既没有主键也找不到合适的非空索引，那么InnoDB会自动生成一个不可见的名为row_id的列名为GEN_CLUST_INDEX的聚簇索引，该列是一个6字节的自增数值，随着插入而自增–补充：该全局row_id在代码实现上使用的是bigint unsigned类型，但实际上只给row_id留了6字节，这种设计就会存在一个问题：如果全局row_id一直涨，一直涨，直到2的48幂次-1时，这个时候再+1，row_id的低48位都为0，结果在插入新一行数据时，拿到的row_id就为0，存在主键冲突的可能性
自动生成的主键约束会有什么问题？
- 使用不了主键索引，查询会进行全表扫描
- 影响数据插入性能，插入数据需要生成ROW_ID，而生成的ROW_ID是全局共享的（InnoDB 维护了一个全局的 dictsys.row_id，所有未定义主键的表都共享该row_id），并发会导致锁竞争，影响性能
创建主键约束方式

1、通过创建表的时候创建
```
CREATE TABLE test (
//这个id 就是主键约束
id int PRIMARY key
)
1
2
3
4
```
2、通过语法去创建
```
alter table 表名 modify 列名 数据类型 primary key；
1
```
创建主键约束的限制
- 主键约束相当于唯一性约束+ 非空约束,主键约束不允许重复,也不允许出现空值
- 一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别创建
非空约束

上面那说到主键约束就等于唯一性约束 + 非空约束非空约束也就是这个值不能为空如果出现空值就会出现报错

1、作用：限定某个字段/某列的值不允许为空

2、关键值：no null

3、特点：
- 默认所有有的类型得知都可以是null 包括int，float等数据类型
- 非空约束只能出现表对象的列上，只能某个列单独限定非空，不能组合非空
- 一个表可以有很多列都分别限定了非空
- 空字符串 “” 不等于null 0也不等于null 4、添加约束：
创建数据表时：
```
　　　　CREATE TABLE test(name varchar(20) not null);
1
```
创建数据表后：
```
alter table 表名称 modify 字段名 数据类型 not null;（模板）
1
```
例子：alter table test modify name varchar(20) not null;

默认值约束

 特点
- 一个表可以有很多的默认值约束
- 默认值约束只能针对某一个字段来说
- 默认值约束意味着，该字段如果没有手动赋值，会按默认值处理
如何在建表时指定默认值约束？
```
create table 表名称(
	字段名1 数据类型  primary key,
	字段名2 数据类型 【unique key】【not null】 default 默认值,
	字段名3 数据类型 default 默认值，
	。。。。
);
1
2
3
4
5
6
```
例如：
```
create table student(
	sid int primary key,
	sname varchar(20) not null,
	gender char default '男'
);
insert into student values(1,'变成派大星');
ERROR 1136 (21S01): Column count doesn't match value count at row 1 ' 列数与值数量
不匹配

insert into student values(1,'变成派大星',default);
insert into student(sid,sname) values(2,'海绵宝宝');
1
2
3
4
5
6
7
8
9
10
11
```
建表后如何指定某个字段有默认值呢？
```
alter table [数据库名.]表名称 modify 字段名 数据类型 default 默认值;
create table student(
	sid int primary key,
	sname varchar(20) not null,
	gender char 
);
alter table student modify gender char default '男';
1
2
3
4
5
6
7
```
提醒：如果某个字段既要非空，又要默认值，那么alter 语句后面两个都要写。
```
create table student(
	sid int primary key,
	sname varchar(20) not null,
	gender char not null
);
1
2
3
4
5
```
增加gender还有默认值
```
alter table student modify gender char default '男' not null;
1
```
如何取消某个字段的默认值约束
```
alter table [数据库名]表名称 modify 字段名 数据类型 [not nul]; #不写默认值约束

insert into t_stu values (1,'变成派大星','未知');
update t_stu set gender = default where sid = 1;
1
2
3
4
```
唯一性约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。类似与主键，但是有以下区别：
- 表可包含多个唯一约束，但每个表只允许一个主键。
- 唯一约束列可包含 NULL 值。
- 唯一约束列可修改或更新。
- 唯一约束列的值可重复使用。
- 唯一约束不能用来定义外键。
创建唯一约束

1、通过创建表的时候创建
```
CREATE TABLE test (
id int PRIMARY key auto_increment,

// 创建唯一约束
name VARCHAR(20) UNIQUE
)
1
2
3
4
5
6
```
2、通过命令创建唯一约束
```
ALTER TABLE 表名
ADD CONSTRAINT 索引名称 UNIQUE (列名);

-- 在已创建好的表上加唯一约束，指定约束名为name
ALTER TABLE student
ADD CONSTRAINT um UNIQUE (name);

-- 在已创建好的表上加唯一约束，不指定约束名
ALTER TABLE student
ADD UNIQUE (name);

创建多个唯一索引
ALTER TABLE student
ADD CONSTRAINT un UNIQUE (name,age
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
```
3、删除唯一约束

查询索引
```
SHOW INDEX FROM 表名;
1
```
删除索引
```
-- 格式一：
DROP INDEX 约束名 ON 表名;
-- 例如：
DROP INDEX name ON student;

-- 格式二：
ALTER TABLE 表名
DROP INDEX 约束名; 
-- 例如：
ALTER TABLE student
DROP INDEX name;
1
2
3
4
5
6
7
8
9
10
11
```
外键索引

1、特点如果数据表含有从属关系，比如部门和员工，教室和桌椅，班级和同学就可以使用一对多的制表关系一的一方定义为主表，多的一方定义为从表在多的一方添加一列，作为外键约束，引用少的一方的主键如果主表中的主键被应用，则该条数据无法被删除如果从表中的外键找不到相同值的主键，则无法被插入

 创建分类表
```
CREATE TABLE category
(
    cid   VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100)#分类名称
);

# 商品表
# 外键约束的添加格式
# 在创建时在最下方添加外键约束:constraint(约束) foreign key(外键字段) reference(参考) 主表(主键)
CREATE TABLE products
(
    pid         VARCHAR(32) PRIMARY KEY,
    name        VARCHAR(40),
    price         DOUBLE,
    category_id VARCHAR(32),
    CONSTRAINT FOREIGN KEY (category_id) REFERENCES category1 (cid) # 添加约束
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
```
注意事项
- 从表中添加的外键的值,一定要在主表中的id里存在,否则就会报错
- 如果想要添加从表数据,先添加主表相对应的id的内容
- 为了保证数据的完整性,如果当前主表中的数据已经被从表引用,则主表中的该数据无法被删除
不过现在外键索引用的比较少还是看个人使用

 总结

1、主键约束

主键是表中能唯一识别表中每条信息的字段。要求：唯一、非空。一个表中只能有1个主键。

2、外键约束

与主键结合使用，确保数据的一致性。

3、唯一约束

一个表中可以有多个字段设置唯一约束，允许存在空值。

4、检查约束

用来检查数据表中，字段值是否有效，减少无效数据的输入。

5、非空约束

设置了非空约束的字段，不允许出现空值。

6、默认值约束

设置了默认值约束的字段，在该字段无数据输入时，会自动添加一个默认值。通常和非空约束一起使用。

主键约束 PRIMARY KEY

不论是单字段主键还是多字段联合主键都需要满足：
- 主键能唯一标识表中每一行数据，且一个表中只能有1个主键的唯一性；
- 主键不能有NULL值；
- 联合主键中字段名不能重复出现。
设置主键的语法格式

1、 CREATE TABLE 语句中

单字段主键在定义字段时，创建约束
```
CERATE TABLE <表名> （<字段名> <数据类型> PRIMARY KEY [默认值]，...）
1
```
2、命令创建
```
CERATE TABLE <表名> （<字段名> <数据类型> ，...，[CONSTRAINT <约束名>] PRIMARY KEY [字段名]）
1
```
3、多字段联合主键：
```
CERATE TABLE <表名> （<字段名> <数据类型> ，...，PRIMARY KEY [字段1，字段2，…,字段n]）
1
```
4、 ALTER TABLE 语句中修改约束：
```
ALTER TABLE <表名> ADD PRIMARY KEY(<字段名>);
1
```
5、删除约束：
```
ALTER TABLE <表名> DROP PRIMARY KEY;
1
```
外键约束 FOREIGN KEY

外键是与主键一起使用的，用来约束两个表的数据一致性和完整性。主键所在表为主表，外键所在表为从表。

1、外键需遵守的规则
- 一个表可以有多个外键，允许有空值；
- 主表已存在于数据库中，或为正创建的表。主表必须已指定主键；
- 外键中字段数目、对应的字段数据类型必须与主键中字段数目、对应的字段数据类型相同。
设置外键的语法格式：

1、CREATE TABLE 语句中，
```
CERATE TABLE <表名> （<字段名> <数据类型> ，...，[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]）
复制代码
1
2
3
```
2、命令创建

2.1、修改外键约束：
```
ALTER TABLE <表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
1
2
```
2.2、删除外键约束：
```
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
复制代码
1
2
```
唯一约束 UNIQUE

设置唯一约束的语法格式：

1、 CREATE TABLE 语句中，
```
CERATE TABLE <表名> （<字段名> <数据类型> UNIQUE，...)；
1
```
2、ALTER TABLE 语句中，

2.1 修改唯一约束：
```
ALTER TABLE <表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
1
```
2.2 删除唯一约束：
```
ALTER TABLE <表名> DROP INDEX <唯一约束名>;
1
```
检查约束 CHECK

设置检查约束的语法格式：

1、 CREATE TABLE 语句中，
```
CERATE TABLE <表名> （<字段名> <数据类型> ，...，CHECK <检查约束>)；

<检查约束>，可以是简单的表达式，也可以是子查询

1
2
3
4
```
2、ALTER TABLE 语句中，

2.1 修改检查约束：
```
ALTER TABLE <表名> ADD CONSTRAINT <检查约束名> CHECK(<检查约束>);
1
```
2.2 删除检查约束：
```
ALTER TABLE <表名> DROP CONSTRAINT <检查约束名>;
1
```
默认值约束 DEFAULT

设置默认值的语法格式：

1、CREATE TABLE 语句中，
```
CERATE TABLE <表名> （<字段名> <数据类型> DEFAULT <默认值>，...)；
1
```
2、ALTER TABLE 语句中，

2.1 修改默认值：
```
ALTER TABLE <表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;
1
2
```
2.2 删除默认值：
```
ALTER TABLE <表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;
1
2
```
非空约束 NOT NULL

设置非空约束的语法格式：

1、CREATE TABLE 语句中，
```
CERATE TABLE <表名> （<字段名> <数据类型> NOT NULL，...)；
1
```
2、ALTER TABLE 语句中，

2.1 修改非空约束：
```
ALTER TABLE <表名> CHANGE COLUMN <字段名>

<字段名> <数据类型> NOT NULL;
1
2
3
```
2.2 删除非空约束：
```
ALTER TABLE <表名> CHANGE COLUMN <字段名>
<字段名> <数据类型> NULL;
1
2
```
相关阅读:
Redis（链表数据结构）
问卷设计中的常见问题
 python占位符
 git:一个本地仓库绑定多个远程的方法以及遇到的问题
 使用vue写了一个音乐播放器
 DAMA学习笔记
 关于SYSTICK延时函数的两个小疑问
 Random类
 大数据管道聚合并分页有什么调优方案
 SpringBoot - @EnableGlobalMethodSecurity注解详解
原文地址：https://blog.csdn.net/Huangjiazhen711/article/details/127548902

前言

正文

主键约束

主键约束理念

提出问题

小总结

创建主键约束方式

创建主键约束的限制

非空约束

默认值约束

特点

如何在建表时指定默认值约束？

建表后如何指定某个字段有默认值呢？

如何取消某个字段的默认值约束

唯一性约束

创建唯一约束

外键索引

创建分类表

注意事项

总结

主键约束 PRIMARY KEY

设置主键的语法格式

外键约束 FOREIGN KEY

设置外键的语法格式：

唯一约束 UNIQUE

检查约束 CHECK

默认值约束 DEFAULT

非空约束 NOT NULL