初识mysql数据库之表的约束

最新推荐文章于 2024-02-27 14:44:40 发布

网络天使莱娜酱

最新推荐文章于 2024-02-27 14:44:40 发布

阅读量631

点赞数

分类专栏： mysql数据库文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/Masquerena114514/article/details/131141218

版权

mysql数据库专栏收录该内容

17 篇文章 2 订阅

订阅专栏

3.2 default自动生成与not null的关系

一、表的约束的概念

表的约束，简单来讲就是mysql为了防止用户输入非法数据而对用户的一些限制。如果表不对用户输入进行限制，就可能出现用户在性别栏填手机号，在手机号填性别这类不合法的数据。因此，表中一定要有各种约束，通过约束，让用户在未来插入数据库的表中的数据是符合预期的。

其实，在之前的文章中介绍数据类型的时候，讲过在float后面的括号中是数据的长度和小数点的位数，在char后面的括号中是字符长度，同时每个数据类型里面要填的值都是有要求的，这些就是表的约束。

约束本质是通过技术手段，倒逼程序员插入正确的数据。反过来说，只要是插入数据库中的数据，一定都是符合数据库的约束的，即保证数据的完整性和可预期性。

真正约束字段的是数据类型，但是数据类型约束很单一，无法满足需求。此时就需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。

表的约束有很多，这里不会一一介绍，而是选择几个比较常用的介绍。即表的属性中后面的NULL等属性的作用。

二、非空约束（空属性）

null想必大家都不陌生了，在C++中，null无论是在字符上还是在数字上，它表示的含义都是0。但是在mysql中，null的含义就和C++中不太一样了。

在表的属性中，有一个空属性，这个空属性中有两个值，分别是null（默认的）和not null（不为空）。

它的含义就是“是否允许某一列为空”。如果是null，表示允许该列为空；如果是not null，则不允许该列为空。

这就好比我们在填一些调查问卷的时候，有些题是必做的，它可能就会用*标识出来，这些题的答案其实就是数据库中必须要的，为not null；而有些题则不是必须做的，即数据库对这些数据可要可不要，即null。

2. 空属性的使用

为了方便演示，这里创建一个简单的班级表，这个表中有班级和教室两个不能为空的信息和一个other，可以为空的信息。

创建如下一个myclass表：

查看它的详细属性：

可以看到，在属性栏中，null一栏中class_name和class_room都是NO，而other则是YES。意思就是class_name和class_room是不允许为空的，而other则允许为空。

首先先向这个表中的三列都插入数据：

三个全部插入是没有问题的。

再测试只插入前两列：

同样可以正常插入。

再来测试只插入第一列：

此时就无法插入了。因为class_room列是not null的，不允许为空。

既然无法省略，那我们能不能向里面插入null呢？

可以看到，此时就报错说不能为空。这也就验证了上文中说的，为not null属性的列是不允许为空的。这就叫做“非空约束”。

注意，数据库默认字段基本都是字段为空，但是实际开发时，因为数据为空是不会参与运算的，所以大家最好尽可能保持字段不为空。

三、default约束（默认值）

1. 默认值的含义

默认值，即某一种数据会经常性的出现某个具体的值，这种值就可以在一开始就指定好，在需要真实数据的时候，用户就可以选择性的使用默认值。这个默认值大家可以理解为C++中在类中使用的缺省值，两者的作用都是差不多的。

默认值的生效条件也会C++中的缺省值一样，数据在插入时不给指定了默认值的字段赋值，就会使用默认值。

举个例子，假设现在有一个表，表中有一项数据是性别，程序员对这个字段指定了默认值为“男”。如果用户输入了性别，表中就使用用户输入的性别；如果用户没有输入性别，表中就填默认值“男”。

2. 默认值的使用

要使用默认值很简单，在创建表时在对应字段的后面带上“default 指定值”即可。为了方便演示，先创建如下一个t1表：

在这个表中，就为age和sex指定了默认值。查看这个表的详细属性：

可以看到，在详细属性中的default列中，name是null，age和sex则是我们创建表时填入的默认值。

为了看到结果，我们分别插入两条数据，分别是指定了age、sex和没有指定age、sex的。

插入完后查看表中的值：

表中的数据就分别是我们自己填的值和默认值，印证了上文说的在有默认值时，如果用户填了值就使用用户的值，如果没有则使用默认值的说法。

3. 默认值与非空约束的关系

3.1 默认值与非空约束的生效问题

从上文中可知，当一列被设置为not null时，这一列在插入的时候就必须要有值。但是默认值可以在用户不填入值时默认使用。既然如此，那默认值和非空约束之间有什么关系呢，是否会互相影响呢？

创建如下一个t2表进行测试：

注意，在这个表中，我们特意给gender赋予了not null属性和设置默认值。这两个属性是可以同时有的，并不冲突。

查看它的详细属性：

属性和我们的预期一样。因为name和gender都设置not null，而age没有设置，所以它的NULL一栏是NO/YES/NO。而default一栏也和我们写的一样。

因为name一栏设置了not null，所以我们向里面插入一个null试试：

此时它的报错为name不能为null。

再试试不向name插入值：

可以发现，此时的报错和上面插入null就不同，这里的报错是没有默认值。

通过这两个实例就可以得出一个结论，当我们不填值时，并不是由NULL属性来判断是否拦截，而是用是否有default来判断是否拦截。这就是说，我们在插入一个设置为not null属性的列时，如果我们没有指定要插入某个值而是使用默认值，如果对应列中没有设置default值，就无法插入。

换句话说，我们在对一个设置为not null属性的列插入时，如果我们不指定值而是使用默认值，只要这个默认值存在，就可以插入。到底是不是这样呢？注意，在建表时，我们特意将gender赋予了not null属性，并添加了默认值。因此我们可以gender来测试：

可以发现，此时数据正常插入，并且从表中的数据可以发现，gender就是使用的我们创建表时给gender填的默认值。

由此我们可以得出一个结论：默认值和not null并不冲突，而是互相补充的。当用户主动插入数据时，只能从null和合法数据中选择。而null则被not null拦截，使得用户只能填合法数据。

默认值则是在用户不想主动插入数据数据时生效，如果对应列有默认值，则使用默认值；如果没有默认值，直接报错。

3.2 default自动生成与not null的关系

现在又有一个问题，如果我们既不填not null也不指定default，此时如果不向对应列插入值，会是什么现象呢？创建如下一个t3表测试：

向这个表中插入如下数据：

从结果可以看到，虽然name一栏并没有插入数据，但依然可以成功插入。并且表中的name栏甚至填上了null。可是从上面的创建表来看，我们并没有给name添加默认值啊，那为什么这里会添加null呢？

查看创建t3表时的创建指令：

可以发现，mysql中保存的创建指令中虽然我们没有填默认值，但是它自动带上了default null，即设置默认值为null。

再来看上面的t2的创建代码：

可以发现，在t2中，由于我们给name设置了not null属性，它的后面就没有自动带上default null。

原因很简单，not null的含义是不允许插入null，而default如果不设置就默认为null，此时就会产生冲突。所以如果某列中设置了not null但没有设置default，mysql就不会自动添加default null。

因此，not null和default一般不需要同时出现，因为default本身就有默认值，不会为空。当然，同时出现也并没有什么问题。

四、列描述

1. 列描述的概念

列描述是使用comment，这个字段没有实际含义，只是用来标识后面的内容是用来描述字段的。它会根随表创建语句被保存起来，用来给程序员或DBA了解数据库。

简单来讲，列描述大家可以看成C++中的注释。

2. 列描述的使用

创建如下一张t4表：

表中圈起来的部分就是列描述。

查看它的详细属性：

可以发现，表的属性并没有因为列描述而产生什么变化。

再查看表的创建指令：

可以看到，在表的创建指令中就有了我们刚刚写的列描述。列描述的作用就是在这里显示，让程序员更好的理解数据库中的各项内容。

既然列描述不会对数据库的数据构成影响，那我们怎么理解它是一种约束呢？列约束只是一种软约束，这些信息是给程序员看的，让程序员知道每一列中的内容应该是什么。

五、zerofill

如果大家看了我的上一篇文章中的数据类型，大家应该知道，在整数类型中，并没有介绍它后面带的空格是什么意思。以int为例，创建如下一个t5表：

查看它的详细属性：

可以看到，虽然我们在创建表是并没有在int后面添加(10)，但是mysql还是自动带上了。那这个括号中的内容到底是什么呢？

向里面添加一个值，先向里面添加如下数据：

显示的结果符合我们的预期。然后我们再修改一下b的属性，给它添加zerofill属性：

添加完后，再查看它的详细属性，可以发现，在type栏里b的后面就多了个zerofill。那这个字段有什么用呢？再次查看表内容：

可以发现，显示2的时候，2的前面就多了一串0。再添加如下数据：

可以发现，此时在200的前面也是有0来填充的。如果大家仔细观察一下就可以发现，通过0填充后的数字，它的位数刚好是10，和我们在上文中看到了int(10)是一样的。

既然如此，我们修改一下int后面的括号里面的值，看一下是不是会会自动填充0以显示特定位数。

修改成功后，输入以下数据进行测试：

查看表中的数据：

可以发现，当b中的数据的位数小于4时，会自动填0以补充为4位，但如果超过4位，则什么都不会做。这也就是说，zerofill的作用就是在数据的宽度小于设定宽度的时候，自动填充0。

上面的表中的两个值都是int unsigned的，再看一下int是什么样的：

可以看到，当时符号int时，括号内的值是11；当时无符号int时，括号内的值是10。原因很简单，我们知道，无论是有符号int还是无符号int，它们的取值范围最大也就是十亿级别的。而十亿级别表现为位数时，也就10位而已。因此，为了表现最大位数，无符号int就被设置为了10；而有符号int中多出来的一位则是符号位，用于表明正负。

注意，虽然显示的时候是全部补0的，但是在实际的存储中不会存储这些0的，而是存储未填充0的值。补0只是一种格式化显示手段。

由此可知，整数类型后面的括号中的值就是用于在有zerofill属性时填0格式化显示的，并没有其他作用。这也就意味着，如果整数类型没有zerofill属性，那么它后面的括号中的值没有任何意义。

六、主键

1. 主键的概念

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

那为什么要有主键呢？其实很简单，因为在某些情况下，我们希望表中的某些数据是不能重复的。例如大家在学校里面的学号，学校用学号来标识唯一一个学生，学号就不能够是重复的。

2. 主键的使用

创建如下一个test_key表：

查看它的详细属性：

可以看到，当设置了primary key后，在表的key栏中，id的属性就多了一个PRI，这就表明id被设置了主键。同时可以发现，id的NULL栏是NO，即not null属性。这也就印证上文中说的设置了主键的列不能为空的说法。

表创建好后，我们先插入一个数据：

可以正常插入。然后再插入一个相同的id和不同的id进行测试：

可以看到，当插入相同的id的时候，mysql就报错说1重复了。但是换成了一个不重复的id时，就可以插入了。

3. 主键的丢弃

在上文中，我们在创建表时就设置了主键。那如果我们突然不想要这个主键了呢？很简单，执行“alter table 表名 drop primary key”即可：

此时表的属性内就没有PRI字段了，即不再有主键。有人可能会疑惑，主键明明是id的属性，为什么丢弃主键的时候可以不操作id直接丢弃呢？其实是因为一个表中只有一个主键，所以无需指定列就可以让mysql自行找到主键然后丢弃。

当主键被丢弃后，就可以插入有重复id的值了：

4. 主键的添加

现在我们的test_key表中已经没有主键了，那如果我们现在又想给id添加主键呢？很简单，执行“alter table 表名 add primary key(列名)”：

当我们添加主键时，mysql却报错说表中的2重复了。查看表的内容：

确实id为2的有两个。此时由于id重复，就无法添加主键了。如果想添加主键，就需要让id不再重复。因此，我们就删除掉一个数据，让2不再重复：

当删除掉一个重复数据后，就可以向表中添加主键了。

通过这个实例，大家就应该要知道，要添加主键，最好就是在创建表的时候添加，再不济也要在向这个表插入数据之前添加主键，以避免出现要添加主键的列中有重复情况而导致主键添加失败。

5. 复合主键

上文中我们说，一个表中只能有一个主键，但是这并不代表一个主键只能用于一列。也就是说，在同一个表中，可以给多列添加主键的。而这种用于多列的主键，就叫做“复合主键”。

6. 复合主键的使用

创建如下一个带有复合主键的pick_course表：

此时就创建了一个带有复合主键的表，其中主键用于id和course_id，表示id和course_id都不能重复。

查看它的详细属性：

虽然它的属性里面id和course_id都带有PRI，表示使用了主键，但是它们使用的是同一个主键。

那么这两列在使用主键后，究竟是表示这两列的值不能同时重复，还是表示其中有一个重复都不行呢？插入如下数据进行测试：

通过上面的测试可以发现，当course_id重复，但id没有重复的时候，是可以正常插入的。然后我们再来尝试一下让这两列同时重复看能否插入：

当两列都重复的时候，就出现了报错，并且从报错中可以发现，是“2-40”出现了重复。这也就是说，当同一个表中不同列使用同一个主键时，mysql是将这些列视为一个整体，当这些列同时重复的时候才会报错；如果有一个不重复，就不会报错。

七、自增长

1. 自增长的概念

自增长，即auto_increment，当对应的字段没有给值的时候，mysql会自动将当前字段中已经有的最大值+1后填入对应列。自增长一般和主键单配使用，称为逻辑主键。

自增长的特点：

1. 任何一个字段要做自增长，前期是本事就是一个索引（key栏有值）。

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

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

2. 自增长的使用

创建如下一个t6表：

查看它的详细属性：

可以看到，在设置了auto_increment属性的列中的extra栏，就有了auto_increment字样。那这个属性有什么用呢？插入如下数据测试：

可以看到，我们在插入数据时并没有插入id的值，但是显示的结果中却是从1开始向后填。这也就是说，自增长的作用就是可以让某列自己在不重复的情况下向上增长值。

那如果我们此时自己插入一个id，这个值会如何变化呢？插入如下数据进行测试：

可以看到，当用户自己插入一个新的值后，自增长的值就是在这个新值的基础上增加。

通过上面的两个例子，就可以得出如下结论：带有auto_increment属性的列，如果用户没有填值，就默认从1开始向上增长；如果用户填入了值，就默认从用户填的值开始向上增长。

在上文中说了，自增长必须是整数，我们给一个不是整数的值设置自增长：

此时就报错了，说不能给那么设置自增长。至于其他两个错误就不再验证了，很容易理解。

3. 自增长的实现原因

既然自增长可以自行增长，那它是如何做到的呢？查看这个表的创建信息：

可以看到，在创建信息中，这个表的表外被mysql自动添加上了一个字段，这个字段中的内容就是当前表的最大id + 1。

既然mysql在表外会自动帮我们添加这个字段，那我们可不可以自行添加然后设置起始值呢？先创建如下一个t8表：

在这个表中，就指定了自增长值从500开始。插入数据进行测试：

可以看到，确实是从500开始插入。这也就说明我们确实可以在表外定义自增长的起始值。

八、唯一值

1. 唯一值的概念

要理解唯一值，我们首要要知道主键的缺点。上文中说了，在一张表中只能有一个主键，带有主键属性的列里面的值不能重复。

但是主键有一个问题，就是不能支持让多列的数据都不重复。虽然一个主键可以给多列用，但是只有当这些列同时重复的时候主键才会生效，如果这些列中有一个不重复，主键都不会生效。因此，如果遇到我们需要不同的列都不重复，例如一个学生的id和身份证号，我们在插入数据时想让这两列中的值都不重复，此时主键就无法满足需求了。

因此，一张表中往往有很多字段需要唯一性，保证数据不能重复。但是一张表中只能有一个主键，无法满足需求。而唯一值就可以解决表中有多个字段需要唯一性约束的问题。这也就意味着，一张表中可以存在多个唯一值。

2. 唯一值的使用

先创建如下一个stu表：

在对应列后面带上unique字段，就表明添加唯一值属性。查看它的详细属性：

在这里我们可以发现，key栏中多了UNI字段，该字段就表明对应列有唯一值属性。同时还可以发现，添加了唯一值属性的列的null栏为YES，就说明这列是可以为空的。这一点就和primary key在添加后会自动添加not null属性不同。

创建好表后就以插入如下数据：

可以看到，确实可以插入。大家应该知道，null是不参与计算的，因此，我们再来测试一下能否继续插入null：

可以看到，在带有唯一值的列为null时，确实可以重复插入。

再来看一下正常情况的插入：

此时就无法插入了，符合预期。

通过上面的例子，就可以得到一个结论：唯一键允许为空，而且允许重复的空值，空字段不做唯一性比较。

上文中说过，在一个表中可以存在多个唯一值，因此我们创建如下一个student表来测试：

在整个表中就存在多个唯一值和一个主键。查看它的详细属性：

key栏中也正确显示了对应的主键和唯一值。

先向里面插入如下一个数据：

插入完成后，逐个测试一下主键和唯一值：

可以发现，逐个测试过去时，mysql就分别将id、telphone和qq三个不能重复的值检测出来了。

通过这种方式，就可以保证插入表中的值是不重复的了。当然，仅仅只能保证数据不重复，但不能保证用户插入不重复的错误的数据时可以被检测出来。

3. 唯一值和主键的关系

唯一值和主键都可以用来保证某列的值是不重复的。对于主键，大家可以简单理解为从众多唯一值中选出一个值充当主键。是用来补充主键的不足的。

在选择唯一值和主键时，一般建议将主键设计成和当前业务无关的字段，当出现业务调整时，就可以尽量不对主键做调整。

九、外键

1. 表与表的关系

在上面的所有内容中，我们讲的都是一个表内的约束。但是要知道，表与表之间也可能是有关系，需要约束的。

例如一个学校里的学生，它有非常多的信息，如学号，姓名、年龄、入学时间、年级、班级等等信息。如果将这些信息全部放在一起，势必会导致这张表非常的冗杂，不好看。因此，我们此时就可以尝试将不同信息进行归类，放在不同的表中。例如准备一个班级表，专门存放有哪些班级；再准备一个学生表，里面就是学生的各类信息，班级栏就直接填班级表中对应的数字：