【MySQL】--- 表的约束

来源：华拓科技网

Welcome to 9ilk's Code World

(๑•́ ₃ •̀๑) 个人主页:

(๑•́ ₃ •̀๑) 文章专栏：

🏠 MySQL表的约束

表中一定要有各种约束来让我们要插入的数据是符合预期的。
约束本质：通过技术手段倒逼程序员插入正确数据；反过来，站在MySQL的视角上，凡是插入进来的数据都是符合我们的数据约束的。
约束最终目标：保证数据的完整性和可预期性。
数据库是维护用户数据的最后一道防线，我们必须保证数据库的数据是可预期并且完整的，表的约束越严格，表中数据就越规整。

因此我们需要更多表的约束，我们之前用desc查看表详细信息，其中Field和Type右边的就是一些表的约束相关信息。本篇博客我们主要介绍以下几个：null/not null, default, comment, zerofill, primary key, auto_increment, unique key。

🏠 空属性

（1）两个值：null(默认的列可以为空)和not null(列不为空)

（2）示例

创建一个班级表，包含班级名和班级所在的教室。站在正常的业务逻辑中：1.如果班级没有名字，你不知道你在哪个班级。 2. 如果教室名字可以为空，就不知道在哪上课。所以我们在设计数据库表的时候，一定要在表中进行，满足上面条件的数据就不能插入到表中。这就是“约束”。

create table if not exists myclass(
    class_name varchar(20) not null,
    class_room varchar(20) not null,
    other varchar(20)
);

desc和show查看表的相关信息：

数据库默认字段基本都是字段为空

实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

空属性约束测试：

insert into myclass (class_name,class_room,other) values('高三二班','101教师','普通班');
insert into myclass (class_name,class_room,) values('高三三班','103教室');
//不插入other other默认为NULL
insert into myclass (class_name) values('高三三班');
// class_room不能为空
insert into myclass (class_name,class_room) values('高三三班',NULL);
//教室设置为null不允许插入
insert into myclass (class_name,class_room) values(null,NULL); 
//也不行 两个都得指明 两个都不能为空

测试结果：

如果字段设置了非空约束，那么在插入时必须填入具体值否则会报错，这就是一种约束。

🏠 默认值

（1）什么是默认值

某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

说明 : 在tt10表中name列的默认值是NULL,age列的默认值是0,sex的默认值是男。

（2）示例

insert into tt10 (name) values('张三');

测试结果：

设置默认值的列，如果插入用户提供的具体数据就用用户的；用户如果忽略，在插入的时候不给该字段赋值，就使用默认值。

注：只有设置default的列，才可以在插入值的时候，对列进行省略,如下面对忽略name就会报错。

Q : 为什么desc查看表详细时我们发现name这一列显示Default:NULL,但是我们插入忽略name列时显示name列没有默认值?

1. Default:NULL是MySQL在字段未设置default值时的展示方式,但实际上默认值为"未设置"，并不代表字段真正有一个DEFAULT NULL约束。

2. 如果字段允许NULL但没有显示设置默认值时，MySQL会自动填充NULL，但其实并没有一个明确的DEFAULT NULL约束。

3. 如果字段不允许NULL，而且没有定义默认值，此时MySQL找不到一个可以填充的默认值，因此就会报“Field 'name' doesn't have a default value”的错误。

（3）default vs not null

先建表:

create table t (
  name varchar(20) not null,
  age tinyint default 18,
  gender char(1) not null default '男'
);

插入时忽略name列(not null)

insert into t (name,age,gender) values(NULL,20,'男'); //not null无法为空
insert into t (age,gender) values(20,'男');

测试结果:

当我们未明确哪一列要插入时,触发的是default约束;如果建表中,对应列默认没有默认值,则无法直接插入。

注：如果name列不为not null约束，此时就不会触发default约束，因此MySQL会自动填充NULL为默认值。

插入时忽略gender列(not null,default)

insert into t (name,age,gender) values ('张三',20,'男');
insert into t (name,age,gender) values ('张三',20,null); //无法插入因为不能为空
insert into t (name,age) values ('张三',20);

测试结果：

default和not null约束并不冲突。not null约束时机是用户想插入数据的时候(要么null 要么合法数据)；default约束时机是当用户忽略这一列时(如果设置使用默认值，没有则报错)。

插入时忽略age列(default)

insert into t (name,age,gender) values('张三',NULL,'女'); //可以为空 没有not null
insert into t (name,age,gender) values('张三','30','女'); 
insert into t (name,gender) values('张三','女'); //使用默认值

测试结果：

只有default约束时插入可以为空；当忽略这一列时会采用默认值。

（4）优化

建表：

create table t15(
  name varchar(20),
  age tinyint unsigned
);

插入：

insert into t15(name,age) values('张三',18);
insert into t15(name,age) values(NULL,18);
insert into t15(age) values(18);

测试结果：

Q : 我建表时对name没有指明默认值，但这里不会报错，为什么？

答 : 其实之前我们已经说过了, 如果建表时我们自己没有指明默认值且字段后面没有NOT null约束时，MySQL会给我们做优化，默认带上(自动填充)default NULL。

总结：

1. not null约束指的是用户想插入时，不能插入NULL。

2. default约束指的是用户忽略某一列时用默认值。

3. 当建表时字段没有default声明时，后续插入数据忽略该列时：如果可以为空则会优化NULL为默认值进行填充；如果有not null约束则无法填充NULL为默认值，受到default约束

🏠 列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

（1）查看列描述信息

语法：

create table if not exists t16(
  name varchar(20) not null comment '用户名',
  age tinyint unsigned default 18  comment '用户年龄',
  gender char(1) default '男' comment '用户性别'
 );

用desc无法查看：

用show查看：

（2）理解

Q ：怎么理解它是一种约束？

答：列描述这个信息是给数据库管理员或维护表的程序员看的，这样他们就能确定清楚这个列的含义是说明因此可以理解为是一种软性约束。

🏠 zerofill

（1）zerofill作用

create table if not exists t17 (
    a int unsigned not null,
    b int unsigned not null
);

通过desc show查看表的详细信息(MySQL 5.0x版本) :

Q : a字段的数据类型括号里的数字代表什么含义?

我们简单地插入一些数据看看 :

insert into t17 (a,b) values(1,2);
select * from t17;
desc t17;

插入结果 :

并没有什么差别。

对列加入zerofill属性后插入数据 :

alter tabel t17 modify b int unsigned zerofill not null;
desc t17;
insert into values (100,200);
select * from t17;

测试结果 :

我们可以看到添加zerofil属性之后b的值由原来的2变成了0000000002,这就是zerofill属性的作用。

zerofill : 如果宽度小于设定的宽度(这里测试设置的是10)，自动填充0。zerofill适用于整形类型，并不适用其他类型。
对于int(10)，代表的其实是输出的宽度,如果没有zerofill这个属性,括号内的数字是毫无意义的。

注：自从MySQL 8.0.17开始，显示的宽度已经被废弃。也就是或，类型的显示宽度即使在创建时指定，也不会再被存储或显示。除非你添加zerofill属性才显示。

（2）一些细节

细节1：设置zerofill属性的字段，它们只是数据的显示受到影响，在MySQL中实际存储的还是插入的数据。

select b,hex(b) from t17;

测试结果：

细节2 : 如果宽度大于等于设定的宽度, 显示的时候就用你给的数据,不补0。

alter table t17 modify b int(4) unsigned zerofill not null;
//先修改b类型的宽度为4
insert into t17 values (1,111);
insert into t17 values (1,1111);
insert into t17 values (1,11111);

测试结果：

细节3：zerofill属性和unsigned是绑定在一起的。当你对一个字段添加zerofill属性，MySQL不允许这个字段存储负数(负数的存在会破坏zerofill统一格式化显示的目标)，因此会强制将它变成unsigned类型。

测试：先将字段设置为int，再添加zerofill属性。

alter table t17 a int not null;
desc t17;
alter table t17 a int zerofill not null;
desc t17;

测试结果：

细节4：默认创建表时int unsigned类型的默认宽度是10,而int 类型的默认宽度是11

int的整数范围最大能用10位数表示,因此创建表时int unsigned类型默认是10位;而对于有符号的,MySQL会默认(未显示指定宽度时)显示符号位+10位有效数字。

注：从MySQL 8.0.17开始，整数类型的显示宽度被完全废弃，因此字段需要设置zerofill才能显示宽度信息。但是添加zerofill之后，有符号会变成无符号，所以在新版看不到有符号的宽度显示。

总结：

1. zerofill属性与整形的显示宽度进行搭配，常被用来进行格式化输出，但是它并不影响数据的实际存储，只影响数据的显示。

2. zerofill与unsigned绑定。

3.设置zerofill属性后，当宽度小于设定宽度时，会在数据前补0。

🏠 主键

(1) 认识主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。

示例：创建表的时候直接在字段上指定主键

create table if not exists test_key (
   id   int unsigned primary key comment '学生学号',
   name varchar(20) not null comment '学生姓名' 
 );

通过desc和show查看表的详细信息：

设置主键后改字段会自动加上not null约束。

测试约束：

insert into test_pri values (1,'张飞');
insert into test_pri values (1,'刘备');

测试结果：

主键约束：主键对应的字段不能重复，一旦重复会造成主键冲突，导致操作失败。

（2）主键的意义

1. 主键约束可以约束程序员,让未来想在表中插入的数据主键列不能冲突,一旦冲突就会操作失败,不允许插入,这促使倒逼程序员规范插入,尽量不出现主键冲突。

2. 对于MySQL,凡是插入表中的数据，它们的主键列一定是不冲突的，而且它们不为空，这样我们就能根据主键拿出一条确定的记录，因此主键能用来唯一标识一条记录！

3. 通过主键的唯一性，我们能针对的对数据进行增删查改。

（3）主键的增删

1. 删除主键

alter table 表名 drop primary key; //去掉主键

示例：

alter table test_pri drop primary key; //先去掉主键

2. 主键的添加有两种方式

创建表时直接在字段后指定主键。
表创建好后无主键时，可以使用alter追加。

alter table 表名 add primary key(字段列表)

示例 :

//之前已经删除主键 插入重复id
alter table test_key add primary key(id);
//我们之前id已经重复此时报错 你得先保证这一列没重复

测试结果:

结论 : 我们要么在建立表之后还没插入数据之前明确是否需要主键(否则会出现如上情况,需要删除重复id),要么在建表时明确是否需要主键。

（4）复合主键

一个表的主键并不意味着只能添加给一列，一个主键可以被添加到一列或多列，而设置到多列的主键叫复合主键。

示例：

create table if not exists tt1 (
  id int unsigned, 
  course_id int unsigned commennt '课程编号',
  score tinyint unsigned comment '学生课程成绩', 
  primary key(id,course_id) 
);

通过desc查看表的详细信息：

注：1. 复合主键在所有字段声明后声明，primary key(复合字段列表) 2. 有两个PRI不证明有两个主键，而是他们两个合起来的整体才算一个主键。

insert into tt1 values(1234,40,90);
insert into tt1 values(1235,40,85);
insert into tt1 values(1234,41,85);
insert into tt1 values(1234,40,91); //不允许一个学生重复选同一门课

测试结果：

结论：对于复合主键来讲，多个数据不一样可以插入；任意一列的插入数据和历史数据意义==一样也可以插入；多个同时和历史数据冲突才会出现主键冲突！

总结：

1. 设置primary key的列不能重复，不能为空。

2. 主键可以用来唯一标识一条记录，方便我们对数据的增删查改。

3. 复合主键需要我们将多个列视为一个整体，当多个列同时与历史数据冲突时才会触发主键冲突。

🏠 自增长

(1) auto_increment

auto_increment:当对应的字段,不给值,会自动的被系统触发,系统会从当前字段中已有的最大值+1操作,得到一个新的不同值。auto_increment通常与主键搭配使用，作为逻辑主键。

（2）自增长特点

1. 任何一个字段要做自增长，前提本身是一个索引(key 一栏有值)。

2. 自增长字段必须是整数。

3. 一张表最多只能有一个自增长。

（3）简单示例

create table if not exists tt21 (
    id int unsigned primary key auto_increment,
    name varchar(20) not null
);

通过desc和show查看表的详细信息：

对某字段设置自增长可以在建表时在字段后声明。

insert into tt21 (name) values ('a');
insert into tt21 (name) values ('b');

测试结果：

当我们插入数据忽略设置自增长的列时，没有设置默认值，它默认从1开始插入，同时它能保证不冲突以及和其他记录是默认连续的。

insert into tt21 (id,name) values(1000,'e'); 
insert into tt21 (id,name) values(1000,'e'); //因为主键所以报错
insert into tt21 (name) values('e'); //此时id应该时1001

测试结果：

当插入一批数据之后，新插入的设置自增长列比历史数据都要大，此时往后会从新的这个起始值开始插入。
当我们指定id时，也是能插入的；但是当再插入相同id值时，确实会履行主键的职责发生主键冲突。

（4）自增原理

Q ：MySQL凭什么做到自增，凭什么知道往哪里插入？

表外auto_increment ：创建表时，除了在表内设置自增长约束外，还可以在表外设置auto_increment的值，它代表下一次插入的起始值。
插入时更新起始值：每一次新插入后，系统会自动更新表外的auto_increment的值，使其成为下次插入的起始值。

（5）last_insert_id

1. 单条插入：last_insert_id()函数能获取最后一次插入的auto_increment值。

2. 批量插入：获取的是批量插入中的第一个ato_increment值。

(6) 建表和建表后修改自增起始值

1. 注意auto_increment是一个用于自动递增的属性,它的值由MySQL自动生成,因此该列不能指定默认值(default)。

2. 如果建表后想从特定值开始自增可以使用alter语句：alter table xxx auto_increment=xx;

注：如果修改的自增起始值比表中历史数据的最大值要小，此时修改时无效的，因为这样就不符合设计规定了，而且表外的auto_incremen的值也不会发生改变。

3. 建表时也可以在表外指定auto_increment的值。

CREATE TABLE test_1 (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL
) AUTO_INCREMENT = 10;

（7）索引

1. 什么是索引？

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。
它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

2. 作用

快速定位：索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。
提高查询效率：数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

总结：

1. auto_increment会自动的被系统触发，如果没指定起始自增值，默认从1开始，它保证了连续性和不冲突性，往往与主键搭配使用。

2. auto_increment可以在建表时指定自增起始值，也可以在建表后使用alter语句指定；但是建表后指定的自增起始值不能小于历史数据，否则无效。

3.我们可以通过last_insert_id()查询最后一次插入(单条)或第一次插入(批量)的auto_increment值。

🏠 唯一键

(1) unique

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。
（2）示例

create table stu (
   id char(20) unique comment'一个学生的唯一键',
   name varchar(32) not null  comment '学生姓名'
);

查看表详细信息：

insert into stu(id,name) values (12345,'张三');
insert into stu(id,name) values (12345,'李四'); //id列发生冲突
insert into stu(id,name) values (NULL,'李四'); //可以插入
insert into stu(id,name) values (NULL,'李四');
insert into stu(id,name) values (NULL,'李四'); //NULL并不冲突

测试结果：

主键一旦设置，就不能和其他键值冲突，而且主键不能为空。
唯一键表达了类似和主键一样的功能，但是可以为空，且能多个为空而不冲突。

唯一键也可以设置为该列不为空

（3）理解unique

场景1：学生信息管理系统

create table student (
    id char(20) primary key,
    name varchar(32) not null,
    telephone char(20)    //其实telephone也是需要维护唯一性的
);
insert into student (id,name,telephone) values ('123','张飞','1234567');
insert into student (id,name,telephone) values ('124','刘备','12342131');
insert into student (id,name,telephone) values ('125','孙权','12342131');

测试结果：

一个人的电话是可变的，不适合做主键，此时多个相同电话会导致有人打电话,但你无法准确定位是谁的，因为你登记多个人同一个号码，,所以电话这一列也是需要保证唯一性，因此主键需要唯一性,其他列也需要唯一性。

create table student ( 
   id char(20) primary key,
   name varchar(32) not null, 
   telephone char(20) unique key, //电话和qq也需要保证唯一性
   qq varchar() unique key
);

给电话和qq添加unique之后它们的唯一性就得到保证。

场景2：员工管理系统

比如在公司中，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列，一个是身份证号，一个是员工工号，我们可以选择身份证号作为主键。而我们设计员工工号的时候需要一种约束：员工工号不能重复。

具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键。
一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整。

总结：

1. 一张表是用来描述对象各种属性列的，建表本质是通过表来描述对象，列即属性，列内容即属性内容，表相当于一个结构体。

2. 选择主键要么从众多属性中选择具有唯一性的，要么选择与业务无关的。

3. 选择某一列作为主键，为了保证数据的一致性和逻辑性，除了主键，其他列的唯一性仍然需要我们唯一性保证，防止用户误操作。

4.某列作为主键，只是这一列被选择为主键，其他列同样可能需要保证唯一性；唯一键核心是与主键搭配，让表的约束力更强，防止用户在上层可能带来的一些未定义或误操作。

（4）unique vs 主键

1. unique与复合主键

Q：unique能否用复合主键替代？

复合主键常用于多个字段组合唯一标识一条记录的场景，比如学生课程表中student_id和course_id作为复合主键，唯一标识每个学生选的课程。
当需要对非主键的字段或字段组合进行唯一性约束时，复合主键无法满足需求，因为复合主键不允许为空，非主键字段可能允许为空或需要更新。比如email可以为空，也可能更新。

2. unique与主键区别总结

唯一键和主键两者并不冲突，而是相互补充，主键保证记录的唯一性，唯一键保证指定信息需要在对应表中不出现重复。
一个保证记录唯一性，一个保证在业务上这个字段不能出现重复。

Q：为什么主键可以标识记录的唯一性，unique可能无法标识？

1. 主键

用户ID(主键)是唯一的,它能够直接区分这三条记录,无论其他列的值是否重复。
即使两位用户有相同的用户名或邮箱,只要主键(用户ID)不同,这些记录仍然是唯一的。

2. unique

如果表中唯一键都是not null，唯一键就可以标识记录唯一性。

上述表中,虽然email列是唯一键,但并不能用来唯一标识记录,因为允许多个NULL值存在。

总结:

1. 唯一键也可以用来约束字段的唯一性,但是唯一键可以为空且可以多个为空。

2. 主键用来标识记录的唯一性，唯一键用来标识非主键字段的唯一性；两者相互补充，让表的约束力更强，保证数据的逻辑性以及防止用户的误操作。

🏠 外键

(1) 什么是外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

(2) 理解外键

建学生表和班级表：

create table student (
 id int unsigned primary key auto_increment,
 name varchar(20) not null, 
 telephone varchar(32) not null,
 class_id int 
);

 create table class ( 
   id int primary key, 
   name varchar(32) not null 
);

形成关联关系：

如果将班级表中的数据都设计在每个学生表的后面，那就会出现数据冗余，所以我们可以创建两张表，让stu->class_id和myclass->id产生关联即可。

插入数据：

//班级表
insert into class values(1,'通信101');
insert into class values(2,'通信102');
//学生表
insert into student (name,telephone,class_id) values ('张三','1234567',1);

此时可以通过关联关系来查询班级。

但是这样也是可能出现问题的，比如有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中

insert into student (name,telephone,class_id) values ('田七','12218297',3);

又或者是删除班级表中一个班级时，没有考虑这个班级是否有学生了，也就是没有确认学生表的情况。

delete from class where id=1;

这便是因为这两张表在业务上是有相关性的，但是在业务上没有建立约束关系：即有外键之名(关联关系)，但是没有外键之实(没有约束)。

（3）外键约束

语法：foreign key (字段名) references 主表(列)

create table student ( 
  id int unsigned primary key, 
  name varchar(20) not null, 
  telephone varchar(32) unique key, 
  class_id int, 
  foreign key(class_id) references class(id)
);
insert into student values (102,'李四','12337',3);

此时学生表是从表，班级表是主表；外键约束设置在从表，也就是学生表上。

测试结果：

由于有外界约束，此时你想插入并不存在的班级，会拦截；反过来，凡是能插入的，一定符合约束关系。此时插入的外键列数据不在主表的主键列上，因此会报错。

delete from class where id=1;

测试结果：

当删除时,班级表的这个外键列被学生表的记录引用,为了保证数据库中的引用完整性,即防止删除一个被其他表依赖的数据导致数据不一致,此时会触发外键约束。

建立外键的本质其实是把相关性交给mysql审核，提前告诉mysql表之间的约束关系，那么当用户插入不符合业务逻辑的数据时，mysql不允许你插入。

总结外键约束两个条件：

1. 表之间有关联关系

2. 产生外键约束

🏠 综合案例

1. 有一个商店的数据，记录客户及购物情况，由以下三个表组成：

商品goods(商品编号goods_id, 商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)
客户customer(客户号customer_id, 姓名name, 住址address, 邮箱email，性别sex, 身份证 card_id)。
购买purchase(购买订单号order_id, 客户号customer_id, 商品号goods_id, 购买数量nums)

2. 要求

每个表的主外键
客户的姓名不能为空
邮箱不能重复
客户的性别(男，女)

3. 示范

//创建数据库
create database if not exists trade charset=utf8;

use trade;
//创建商品表
create table if not exists goods (
    goods_id int primary key auto_increment comment'商品编号',
    goods_name varchar(32) not null comment '商品名称',
    unitprice int not null default 0 comment '单价,单位分',
    category varchar(12) comment '商品分类',
    provider varchar() not null comment '供应商名称'
);

//创建客户表
 create table if not exists customer ( 
   customer_id int primary key auto_increment comment '客户编号', 
   name varchar(32) not null comment '客户姓名', 
   address varchar(256) comment '客户地址', 
   emaiil varchar() unique key comment '客户电子邮箱', 
   sex enum('男','女') not null comment '客户性别',
   card_id char(18) unique key comment '用户身份证' 
);

//创建订单表
create table if not exists purchase (
    order_id int primary key auto_increment comment '订单号',
    customer_id int comment '客户编号',
    goods_id int comment '商品编号',
    nums int default 0 comment '购买数量',
    foreign key (customer_id) references customer(customer_id),
    foreign key (goods_id) references goods(goods_id)  
);

完。

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文