MySQL系列表约束的学习

「前言」文章内容大致是MySQL的表的约束。

「归属专栏」MySQL

「主页链接」个人主页

「笔者」枫叶先生(fy)

一、MySQL表的约束

• 数据类型也可以进行约束插入的值，如果插入的数据超出了对应数据类型的取值范围，那么数据将会插入失败。
• 但是数据类型的约束很单一，为了更好的保证数据的合法性，从业务逻辑角度保证数据的正确性，MySQL中出现了表的约束。
• 目的就是为了尽可能保证数据合法性，数据是符合预期的。表的约束是通过技术手段，倒逼插入数据的人，插入的数据是合法的，还有一个就是减少用户的误操作可能性。
• 反过来，站在MySQL的视角，对于成功插入数据库的数据一定是合法的。
• 约束的目的：保证数据的完整性和可预期性
• 表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primary key，auto_increment，unique key

注意：MySQL的命令对大小写不敏感的，即不区分大小写。

1.1 空属性

• 空属性有两个值，分别是null（空）和not null（不为空）
• 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办 法参与运算

空值是无法参与运算，例如：

注意：null代表的是不存在，不是C语言、C 里面的NULL，注意区别。

例如：创建一个班级表，包含班级名和班级所在的教室

mysql> create table if not exists class(
    -> class_name varchar(20),
    -> class_room varchar(10));

查看表的属性，这些类型字段默认是null

尝试插入数据，是允许插入为空的数据的

站在正常的业务逻辑中：

• 如果班级没有名字，你不知道你在哪个班级
• 如果教室名字可以为空，就不知道在哪上课

所以我们在设计数据库表的时候，一定要在表中进行限制，不满足条件的数据就不能插入到表中。这就是“约束”。

如果要让某个字段不允许为空，在创建表的时候就可以给对应字段设置not null属性。

依旧是创建一个班级表，包含班级名和班级所在的教室，但是这两个属性不允许为空

mysql> create table if not exists class2(
    -> class_name varchar(20) not null,
    -> class_room varchar(10) not null);

创建表完毕后查看表结构，可以看到这两个字段是不允许为空的。

查看表创建时的细节

向表中插入数据时，只有这两个字段都不为空时才能插入成功，否则将会插入失败。

1.2 默认值（default）

• 如果某一个字段会经常性的出现某个值，那么就可以考虑将这个值设置成该字段的默认值。
• 向表中插入数据时如果不给带有默认值的字段赋值，那么就会使用默认值进行插入。

比如，创建一个表，表当中包含用户的姓名、年龄和性别，年龄和性别分别设置为18，男

mysql> create table if not exists t1(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 18,
    -> sex char(2) default '男');

创建之后，查看表是属性

向表中插入数据时，如果不指明用户的年龄或性别，那么就会使用对应的默认值（缺省值）

还有一种就是一个字段同时设置not null和default两个值：

• 一旦给某一字段设置了默认值，那么该字段将不会出现空值，因为就算插入数据时没有指明该字段的值，也会使用该字段的默认值进行填充。
• 而给某一字段设置not null属性的目的是约束该字段不能为空，因此一个字段设置了default属性后，再设置not null属性就没有意义了。

1.3 列描述（comment）

列描述comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解，相当于C语言、C 的注释。

比如创建一个表，表当中包含用户名、用户的年龄和用户的性别，在每一个字段后面添加上对应的列描述。

mysql> create table if not exists t2(
    -> name varchar(20) not null comment '这是用户的名字',
    -> age tinyint unsigned default 18 comment '这是用户的年龄',
    -> sex char(1) default '男' comment '这是用户的性别');

创建之后，查看表创建时的详细信息(通过desc查看不到注释信息)

show create table t2;

注意：MySQL使用单引号也可以代表字符串，与C语言、C 的有区别

1.4 zerofill

数值类型后面的圆括号中的数字，代表的是显示宽度，对应数值类型设置zerofill属性后，如果数据的宽度小于设定的宽度则自动填充0

例如：创建一个表，表当中包含a和b两列整型数据，将它们的显示宽度都设置成5，但是没有设置zerofill属性

mysql> create table if not exists t3(
    -> a int(5),
    -> b int(5));

向表中插入一条记录，正常显示

修改表结构，给a列添加上zerofill属性，由于a列数据的显示宽度为5，因此查看表中数据可以看到a列数据中宽度不足5位的数据都自动在前面填充0

要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1，00001只是设置了zerofill属性后的一种格式化输出而已。

简单说一下索引，后序详谈

• 索引：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
• 索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
• 索引提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针排序。
• 数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

1.5 主键（primary key）

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

比如创建一个学生表，表当中包含学生的学号和姓名，由于学生的学号是不会重复的，因此可以将其设置成主键

mysql> create table if not exists student(
    -> stu_id int unsigned primary key comment '学生ID',
    -> stu_name varchar(20) not null comment '学生姓名');

创建表成功后查看表结构，可以看到id对应的Key列出现了PRI，PRI就是主键。

查看表的创建详细信息，主键也可以这样设置，图中圈起来的

进行插入，插入表中的记录的主键字段不能重复，如果插入记录的主键与表中已有记录的主键重复，这时就会因为主键冲突而插入失败

当表创建好以后但是没有主键的时候，可以再次追加主键

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

需要注意：只有列当中的值不为空并且不重复的列才能被设置成主键，如果列中已经有了重复的的值，则设置主键失败。这也说明要先把表结构设计好，不然后面插入有数据很难进行操作

删除主键

alter table 表名 drop primary key;

复合主键

• 一张表里面只能有一个主键，但一个主键可以由多个字段来承担，这种主键叫做复合主键。
• 复合主键用来唯一约束多个字段里面的数据，表当中每条记录的这多个字段不能同时重复也不能为空。

比如创建一个成绩表，表当中包含学生的ID，课程ID，成绩。学生ID和课程ID可以同时设置为主键，因为这两个字段不能同时重复也不能为空。

mysql> create table if not exists t5(
    -> stu_id int unsigned,
    -> course char(10) comment '课程ID',
    -> score tinyint unsigned default 0 comment '成绩',
    -> primary key(stu_id, course) -- id和course为复合主键
    -> );

注：-- 后面也是注释

查看表信息，可以看到两列都是PRI，即主键，并且它们都是不允许为空的

向表中插入数据时，只有插入的学生ID和课程ID同时出现冲突时才会产生主键冲突，否则就允许插入。

只有两个同时冲突时才会产生主键冲突，有点像C语言的&&条件

1.6 自增长（auto_increment）

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

自增长的特点：

• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

比如创建一个表，表当中包含id和name，将id同时设置成主键和自增长字段

mysql>  create table if not exists t6(
    -> id int unsigned auto_increment,
    -> name varchar(20) not null default '',
    -> primary key(id)
    -> );

创建表完毕后查看表结构，可以看到id的Extra列中出现了auto_increment标志

向表中插入第一条记录时如果没有指明自增长字段的值，那么自增长字段的值默认将会从1开始。id不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值 1操作，得到一个新的不同的值

后续向表中插入记录时如果也不指明自增长字段的值，那么自增长字段的值就会依次递增。如下：

插入数据的时候也可以指明自增长字段的值，此时将会使用该值进行插入，但注意指明的值不能和表中已有的id值重复。

继续插入，会i从d列中找出最大值，会将最大值加一后得到的值作为自增长字段的值进行插入

这个自增长在表外就有设置，查看表创建的详细信息就可以看到，这个值就是作为下一次自增长的值。如果想指定自增长的开始值，可以在表外面进行修改这个值。

输入select last_insert_id();可以查看当前值的上一个，即表中的最大一个值

1.7 唯一键（unique）

• 一张表中往往有很多字段需要唯一性，但一张表中只能有一个主键，而唯一键就可以解决表中有多个字段需要唯一性约束的问题。
• 唯一键和主键都能保证字段中数据的唯一性，但唯一键允许字段为空，并且可以多个字段为空，空字段不做唯一性比较。
• 需要注意的是，某一字段被选择成为了主键，仅仅是因为这个字段被选择成为主键，除了主键之外，仍有其他需要标识唯一性的字段，这些字段就可以被设置成唯一键。
• 主键和唯一键是互相补充的关系

比如，创建一个学生表，表当中包含学生的id、姓名和电话号码，将我们选择id作为主键，但同时每个学生的电话号码也应该具有唯一性约束，因此应该将电话号码设置成唯一键。

mysql> create table if not exists t7(
    -> stu_id int unsigned primary key auto_increment,
    -> name varchar(20) not null,
    -> tel varchar(20) unique comment '唯一键'
    -> );

表创建完毕后查看表结构，可以看到tel的Key列出现了UNI标志，这就表明tel已经成功被设置成唯一键了。

查看表创建的详细信息

向表中插入数据时，如果插入记录中的电话号码与表中已有记录的电话号码出现重复，那么就会因为唯一键冲突而插入失败，还有唯一键是可以允许为空。

1.8 外键（foreign key）

• 外键用来定义主表和从表之间的关系，外键约束主要定义在从表上，主表必须有主键约束或唯一键约束。
• 外键定义后，要求插入外键列的数据必须在主表对应的列存在或为null。
• 外键是用于表和表之间的关联，表和表之间的约束。

语法：

foreign key (字段名) references 主表(列)

比如，先创建一个班级表作为主表，表当中包含班级的id和班级名，并将班级id设置为主键。

mysql> create table if not exists class(
    -> id varchar(10) primary key,
    -> name varchar(20) not null
    -> );

创建成功后查看表的详细信息

再创建一个学生表，表当中包含学生的id、姓名以及学生所在班级对应的id，学生ID设置为主键，班级ID设置为外键

mysql> create table if not exists student(
    -> stu_id int unsigned primary key auto_increment,
    -> name varchar(20) not null,
    -> class_id varchar(20),
    -> foreign key(class_id) references class(id)
    -> );

表创建成功后查看表的详细信息，学生表中的班级id对应的Key列出现了MUL，这表明class_id已经成功被设置成了外键。

向班级表插入两条数据

向学生表中插入记录时，如果插入的记录对应的班级id是班级表中存在的，或者插入的班级id为null，那么此时是允许进行插入的。

但是如果插入学生表的记录对应的班级id是不存在，此时将会插入失败，这就是外键约束

这里的班级表就是主表，学生表就是从表

在查看学生表创建时的详细信息时，图中圈起来的，这个字段是给外键约束起名字，不过一般都不怎么使用，默认情况下外键有自己的名字，圈起来引号的内容。

--------------------- END ----------------------

「 作者 」 枫叶先生
「 更新 」 2023.8.6
「 声明 」 余之才疏学浅，故所撰文疏漏难免，
          或有谬误或不准确之处，敬请读者批评指正。

这篇好文章是转载于：学新通技术网