MySQL之表的约束

目录

前言

not null约束

default约束

同时设置not null约束和default约束

comment约束

zerofill约束

primary key约束（又称主键约束）

复合主键约束

auto_increment约束（又称自增长约束）

unique约束（又称唯一键约束）

---

前言

在<<MySQL数据类型>>一文中说过，MySQL的数据类型本质也是一种约束，其约束的是属性的值，比如如果插入的数据超出了对应属性的属性类型的取值范围，那么数据将会插入失败。

然后要知道的是，光有数据类型的约束还不够，MySQL为了更好地保证数据的合法性，为了更好地从业务逻辑角度上保证数据的正确性，为了尽可能保证数据安全，减少用户的误操作可能性，于是MySQL中出现了表的约束这种概念。

not null约束

说一下，如下图所示，不要认为一片空白就表示空，一片空白可能是一个空字符，可能是一个空字符串，它们其中都是有值的，比如都有一个\0，而只有NULL才表示空，才表示这个位置上没有任何值。

在MySQL中默认是允许一个属性的值为空的，但在实际开发中我们要尽可能保证某个属性的值不为空，因为空值无法参与运算，比如通过select可以看到null的值为null。如下：

由于空值无法参与运算，因此null值加一后得到的还是null。如下：

如果不允许某个属性的值为空，在创建表的时候就可以给该属性设置not null约束。

比如说，如下图1所示，我们创建一个班级表，表当中包含班级名class_name属性和该班级所在的教室class_room属性，因为在插入一条数据时，不管是class_name属性还是class_room属性，都不允许为空，所以我们在创建表时就需要给这两个属性设置not null约束。

创建表成功后desc查看表结构，如下图1红框处所示，可以看到Null约束上的值是NO，这就证明了我们已经成功地给class_name属性和class_room属性设置了not null约束，往后用户在插入数据时就必须给这两个属性设置一个值了，所以如下图2所示，向表中插入数据时只有这两个属性的值都不为空时才能插入成功，否则将会插入失败（如果没有not null约束，则我们无视其中一个也是能成功完成插入的）。

• 图1如下。
• 
• 图2如下。

default约束

如果表中的某个属性会经常性的等于某个值，那么就可以将这个值设置成该属性的默认值（或者说将这个值设置成该属性的default约束）。这样一来，每次向表中插入数据时就可以不用再手动设置这个属性的值了，因为向表中插入数据时如果不手动给带有default约束（或者说带有默认值）的属性赋值，那么MySQL就会使用default约束上的值自动完成对该属性的赋值。

举个例子，如下：

• 如下图所示，创建一个用户表，表当中包含用户的姓名（name属性）、年龄（age属性）和性别（gender属性），将age属性的default约束（或者说默认值）设置成18，将gender属性的default约束（或者说默认值）设置成男。
• 创建表完毕后desc查看表结构，如下图红框处所示，可以看到age对应的Default字段上的值已经是18，而不是NULL；gender对应的Default字段上的值已经是男，而不是NULL，这就说明default约束或者说默认值已经设置成功了。
• 说一下，如果default约束字段上的值是NULL，则表示该属性的默认值为NULL，而不表示该属性没有默认值。

这时再向表中插入数据时，如果不指明用户的年龄（即如果不指明age属性的值），如果不指明用户的性别（即不指明gender属性的值），那么就会使用对应的default约束字段上的值；反之如果指明了就会使用用户指定的值。如下：?

同时设置not null约束和default约束

是可以给一个属性同时设置not null约束和default约束的，如下图所示，创建表完毕后查看表结构，可以看到id属性对应的Null约束字段上的值是NO而不是YES，说明设置not null约束成功，id属性的值已经不允许为空了；并且可以看到id属性对应的Default约束字段上的值是0而不是NULL，说明设置default约束也成功了，id属性已经具有默认值0了。

此时在向表中插入数据时可以不指明id属性的值进行插入，此时会使用id属性的默认值。如下：

问题：一般来说，一旦给某一属性设置了默认值，该属性将不会等于空（即NULL），因为就算插入数据时没有指明该属性的值，MySQL也会使用该属性的默认值自动完成插入。问题来了，给某一属性设置not null约束是为了约束该属性的值不能为NULL，而在前面又说过一旦给某一属性设置了默认值，该属性将不会等于空（即NULL），这样一看，好像给一个属性设置了default约束后，再设置not null约束就没有意义了，那为什么还要同时设置not null约束和default约束呢？

答案：因为有一种特殊情况，比如给一个属性设置了default约束后，如果不设置not null约束，那么用户是可以指定把这个属性的值设置成NULL的，如下图就是只给属性id设置了default约束，没有设置not null约束，所以最后如下图红框处所示，是可以把id属性的值手动设置成NULL的。也正是因为存在这种特殊情况，所以上一段中的开头部分才只是说【一般来说】。

comment约束

在创建表时可以给各个属性设置comment描述，其作用一般是给程序员或DBA了解表中的各个属性表示什么含义，相当于一种注释。

比如如下图所示，在创建一个用户表user时，在表中设置用户名（即name属性）、用户的年龄（即age属性）和用户的性别（即gender属性）时，就可以在每一个属性后面添加上对应的comment注释。创建表完毕后，通过【show create table 表名】即可看到曾经创建表时所写的SQL语句和comment注释。

问题：

• 属性的名称也可以说明该属性表示什么含义，比如如果属性的名称是age，那么我们一看就知道该属性是表示年龄的。问题来了，那么为什么还要有comment注释呢？

答案如下：

• 其一是因为没有人规定说属性的名称必须和属性所表示的含义有关，比如我就想让属性的名称为x，但我又想在将来让其他用户或者自己能够知道属性x所表示的含义，这时就必须通过comment注释才能做到这一点了。
• 其二是因为光靠属性的名称有可能不足以表示其含义，就拿age举例，比如如果有一种网站是不允许18岁以下的用户访问的，那么就需要通过一种方式提示程序员不能把18岁以下的用户插入到数据库对应的表中，此时很显然光靠一个属性的名称age是起不到提示作用的，这时就必须通过comment注释才能做到这一点了。

为什么comment注释也是一种约束呢？比如说，其实从上一段中我们就理应能体会出答案，比如在上一段中的comment注释就能够提示用户在插入数据时不应该把一个属性的值设置成多少，这本质上就是对用户的一种约束。说一下，可以看到comment注释只是一种软性约束，而并不是一种强制性约束，比如说如果程序员或者DBA非要把18岁以上的用户插入到MySQL中，那么MySQL也没办法，它只能照办。

zerofill约束

如下图所示，我们创建一个表，在创建表时设置一个类型为int的属性a并设置一个类型为int unsigned的属性b，创建完后desc查看表的结构，如下图红框处所示可以发现int后面有个（11），unsigned int后面有个（10），这是什么东西呢？

先说一下，如果在设置一个int、bigint、tinyint等类型的属性时，没有给该属性设置zerofill约束，则这些类型后面的圆括号加数字是没有意义的（即不会起到任何作用），比如上图红框处的int（11）和int（10）unsigned，因为在上图中create创建表时、在设置属性a和属性b时，没有为a和b设置zerofill约束，所以（11）和（10）就没有意义，不会起任何作用。

然后要说的是，圆括号中的数字，比如上图红框处的int（11）中的（11），其代表的是显示宽度。给int、bigint、tinyint等数值类型的属性设置zerofill约束后，往后在select查表时，如果该属性的值的宽度小于设定的宽度，则MySQL在显示该属性的值时会自动在其的高位填充0直到补齐宽度；如果该属性的值的宽度大于设定的宽度，则MySQL在显示该属性的值时就正常显示。咱们来实操证明一下本段和上一段的理论，如下：

• 如下图所示，先创建一个表，表中包含int类型的属性a和int类型的属性b，将它们的显示宽度都设置成5，但是没有设置zerofill属性。然后再向表中插入一条记录，指明属性a和属性b的值分别为123456和1。然后因为我们在create创建表时没有给属性a和属性b设置zerofill约束，所以根据上面的理论，(5）和（5）按理来说就没有意义，不会起到自动填充0的作用。然后可以看到在下图中也的确证明了这个理论是正确的，比如select查看表中数据时显示出来的都是数据本来的样子，MySQL并没有在属性b的值1的高位自动填充0。
• 
• 然后如下图所示，修改表的结构，给属性a和属性b添加上zerofill约束。这样一来，根据上面的理论，(5）和（5）按理来说就有了意义，比如如果属性a和属性b的值的宽度小于设定的宽度5，则MySQL在显示属性a和属性b的值时就会自动在其的高位填充0直到补齐宽度；如果属性a和属性b的值的宽度大于设定的宽度5，则MySQL在显示该属性的值时就正常显示。然后可以看到在下图中也的确证明了这个理论是正确的，比如select查看表中数据时显示出来的a属性的值是正常显示的，而显示出来的b属性的值则被填充0了。

需要注意的是，zerofill约束的作用仅仅只是让某属性的值以特定的方式进行显示，该属性的值并没有发生变化，比如说虽然上图中属性b的值显示的是00001，但通过hex函数可以看到在底层实际储存的属性b的值依然是1。如下：?

问题：如下图所示，在创建表时，在设置int类型的属性a和unsigned int类型的属性b时明明没有指定它们的显示宽度，但创建表完成后，通过desc查看表结构时如下图红框处所示，却发现a竟然有显示宽度11，b也有显示宽度为10，这是什么情况呢？

答案：如果在创建表设置int、bigint、tinyint类型的属性时不指明该属性的值显示宽度，则MySQL会自动根据该属性的类型，把该属性的值的显示宽度设置成默认值。至于为什么unsigned int的默认显示宽度是10，是因为unsigned int类型的最大值是42亿9千万，它就是一个10位数的整数，用10个宽度即可表示；而int的默认显示宽度是11是因为相比于unsigned int，int多一个符号位，比如在-2147483647中，2147483647共有10位，再加上符号-，所以需要用10+1=11个宽度表示。

走到这里就讲完了zerofill约束，说一下，该约束是不怎么常用的。

primary key约束（又称主键约束）

就像在现实中有一种【在10w人中找1个特定的人】的需求，在MySQL中也是有一种【在海量的数据中找1条特定的数据】的需求的，而就像在现实中找一个特定的人我们至少要知道一个独属于他的特征（比如身份证能唯一标识这个人，我们就可以通过身份证来区分、来找这个人），在MySQL中找一个特定的数据我们也至少要知道一个独属于该条数据的特征。那么如何知道呢？

答案就是通过primary key约束（又称主键约束）。主键约束的介绍如下：

• primary key主键约束用于确保表中的某个属性的值唯一标识一条数据，比如说如果给某个属性设置了主键约束，则在整个表中，每条数据在该属性字段上的值一定是不相同的，即每条数据在该属性字段上的值一定是唯一的，这样一来，就可以通过被设置了主键约束的属性区分每一条数据了。
• 因为给一个属性设置主键约束的目的就是，在所有数据中通过被设置了主键约束的属性区分每一条数据，所以被设置了主键约束的属性的值是不允许为NULL的。这确保了主键在标识数据时是完整的，没有缺失信息。

咱们来实操演示一下primary key主键约束，如下：

创建一个学生表，表当中包含学生的学号（id属性）和姓名（name属性），由于学生的学号（id属性）是不应该重复的，因此可以给id属性设置主键约束。如下：

• 方式1如下。
• 
• 方式2如下。
• 

如下图所示，创建表成功后desc查看表结构，可以看到id属性对应的Key字段上的值是PRI，这表示我们已经为id属性设置主键约束成功了。此外可以看到，虽然在创建表的时候没有给id属性设置not null约束，但因为被设置了主键约束的属性的值不能为NULL，所以MySQL会自动为该属性设置not null约束，所以下图中id属性对应的Null字段上的值依然是NO，表示id属性的值不能为NULL。

根据上面的理论我们可知，如果插入一条数据时，该数据在被设置了主键约束的属性id上的值和表中已有的数据的属性id的值重复，这时就会因为主键冲突而插入失败。如下：?

使用SQL语句【alter table 表名 drop primary key】即可删除指定表的某个属性的主键约束，注意想要删除某个属性的主键约束时只需要指明要删除主键约束的是哪张表即可，不需要指明是哪个属性。如下图所示，这里删除属性id的主键约束后再desc查看表结构，可以看到属性id对应的Key字段上的PRI已经没有了，这就证明了删除主键约束成功。

需要注意的是，虽然该属性id的主键约束被删除了，但因为曾经主键约束被设置而导致跟着被设置的not null约束并没有被随之删除，下图中属性id对应的Null字段上的值依然是NO即可证明这一点。

注意，对于已经创建好了的、没有设置主键约束的表，如果想给该表的某个属性增设主键约束，则可以使用SQL语句alter table 表名 add primary key(属性名);??。但注意，此时想要成功为某个属性增设主键约束，是需要条件的，即【在该表所有的数据中，不能有一条数据在该属性上的值等于另一条数据，也不能有一条数据在该属性上的值等于NULL】，如果不符合条件，则设置主键约束就会失败。如下图红框处所示，可以看到我们表中的数据是符合条件的，所以重新为属性id增设主键约束是可以成功的，属性id对应的Key字段上的值是PRI即可证明重新增设主键约束成功。

复合主键约束

说一下，在上文中描述的所有关于主键约束的内容都是针对【在一张表中，只给表中的某一个属性设置主键约束】这种情况。而现在我们要知道的是，可以给表中的一个属性设置主键约束，也可以给表中的多个属性设置主键约束（复合主键约束）。

比如说如果只给一个属性设置主键约束，那么每一条数据只需要在这一个属性上的值不重复，且不为NULL即可；而如果给表中的多个属性设置主键约束，则说明该表中的主键约束是复合主键约束，则每一条数据只需要在多个属性中有一个属性上的值不重复（比如每条数据都有name、sex、idcard共3个属性，并且3个属性都还设置了主键约束时，数据1和数据2可以在name和sex属性上的值相等，但此时在idcard属性上的值就不能再相等了），且每条数据的多个属性都不为NULL即可。

咱们来实操演示一下复合主键约束，如下：

创建一个进程表process_table，表当中包含进程的IP地址（即属性ip）、端口号（即属性port）和进程的相关信息（即属性info），并为属性ip和属性port设置复合主键约束，如下：

• 正确方式如下。
• 
• 错误方式如下。（注意设置复合主键约束时不能像下图这样写，但设置非复合主键约束时可以像下图这样写）

表创建完毕后desc查看表结构，可以看到属性ip和属性port对应的Key字段上的值都是PRI，这说明设置主键约束成功。并且还可以看到属性ip和属性port对应的Null字段上的值都是NO，这说明属性ip和属性port的值都是不允许为空的（给某属性设置主键约束成功后，该属性的值就不能为NULL了，所以MySQL会自动为该属性设置not null约束，所以属性ip和属性port对应的Null字段上的值都才是NO）。如下：

根据上面的理论我们可知，在向进程表中插入数据时，只有插入的数据在属性ip和属性port上的值全部和表中已有的数据冲突时才会产生主键冲突，否则就允许插入。可以看到下图中的情况也的确符合这个理论，比如只有红框处的insert into语句会失败就是因为插入的数据在属性ip和属性port上的值全部和黄框中的相等、冲突；而其他insert into语句因为只有一个属性的值互相冲突或者所有属性的值都互相不冲突，所以就能成功插入。

如下图select查看表中插入的数据时我们也可以看到，表当中有重复的IP地址，也有重复的端口号，但是不会出现IP和端口均重复的，这就是复合主键。

使用SQL语句【alter table 表名 drop primary key】即可删除指定表的复合主键，注意想要删除某些属性的复合主键约束时只需要指明要删除复合主键约束的是哪张表即可，不需要指明是哪些属性。如下图所示，这里删除属性ip和属性port的复合主键约束后再desc查看表结构，可以看到属性ip和属性port对应的Key字段上的PRI已经没有了，这就证明了删除复合主键约束成功。

需要注意的是，虽然属性ip和属性port的复合主键约束被删除了，但因为曾经复合主键约束被设置而导致跟着被设置的not null约束并没有被随之删除，下图中属性ip和属性port对应的Null字段上的值依然是NO即可证明这一点。

注意，对于已经创建好了的、没有设置复合主键约束的表，如果想给该表的某些属性增设复合主键约束，则可以使用SQL语句alter table 表名 add primary key(属性名1、属性名2，，属性名n);??。但注意，此时想要成功为某些属性增设复合主键约束，是需要条件的，即【在该表所有的数据中，不能有一条数据在这些属性上的值完全等于另一条数据（即在多个属性中至少要有一个属性的值不等于另一条数据的对应属性），也不能有一条数据在这些属性上的值等于NULL（即在多个属性中，不能有任何一个属性的值是NULL）】，如果不符合条件，则设置复合主键约束就会失败。如下图红框处所示，可以看到我们表中的数据是符合条件的，所以重新为属性ip和属性port增设复合主键约束是可以成功的，属性ip和属性port对应的Key字段上的值都是PRI即可证明重新增设复合主键约束成功。

auto_increment约束（又称自增长约束）

如下图所示，创建一个表，在创建表时设置属性id和属性name并为属性id设置主键约束和自增长约束（这里为属性id设置主键约束是因为如果不设置主键约束，则无法为属性id设置自增长约束）。创建表完毕后desc查看表结构，可以看到属性id对应的Extra字段上的值为auto_increment，这就证明了设置自增长约束成功。

向表中插入第一条数据时如果没有指明被设置了自增长约束的属性id的值，那么属性id的值将会默认等于1，而不等于NULL。如下：

后续向表中插入数据时如果也不指明被设置了自增长约束的属性id的值，那么属性id的值就会依次递增1。如下：

如下图所示，如果向表中插入数据时指明了被设置了自增长约束的属性id的值，则此时就会插入指定的值，但注意指明的值不能和表中已有的值重复。

如下图所示，如果此后向表中插入数据时又不指明被设置了自增长约束的属性id的值，那么属性id的值又将会依次递增1。

问题：MySQL为什么能知道属性id的值自增长到哪了呢？是靠遍历整个表格知道的吗？

答案：不是靠遍历，这样效率太低了。实际上如果给表中的某个属性设置自增长约束后，MySQL是会给这张表设置一个计数器的，如下图红框处所示，使用show create table查看表的创建语句就可以看到该计数器，该计数器上的值就是【下一次插入数据时，如果不指明属性id的值，MySQL会自动给属性id设置的值】，也正是因为有这个计数器的存在，所以MySQL才能知道属性id的值自增长到了哪。

最后要知道的是：

• 想要给任何一个属性设置自增长约束，前提必须是该属性被设置了主键约束，并且除此之外，该属性的属性类型必须是数值类型。
• 在一张表中，最多只能给一个属性设置自增长约束。

unique约束（又称唯一键约束）

唯一键用于在所有数据中唯一标识一条数据，比如说如果给某个属性设置了唯一键约束，则在整个表中，每条数据在该属性字段上的值一定是不相同的，即每条数据在该属性字段上的值一定是唯一的。

通过上一段的描述可以发现唯一键约束的作用和主键约束的作用是一模一样的，都是用于唯一标识一条数据，那么问题来了，通过主键约束我们已经能唯一标识一条数据了，为什么还要有唯一键约束的存在呢？

答案是，因为在实际业务中光有主键约束可能是不够的，还需要有唯一键约束来完善业务逻辑。举个例子，如下。

• 如下图所示，当我们为属性stu_id（即学号）设置了主键约束后，则我们一定可以通过属性stu_id来唯一标识每一个学生、可以通过属性stu_id找到任意一个学生，但假如发生了这样的事情，比如有一个学生觉得他的辅导员很不负责，于是通过QQ在班级群里面辱骂辅导员。辅导员看到后很生气，想拿着该同学的QQ号去教务系统看这是哪个学生，然后找到该学生进行教育，但进入教务系统一查QQ号，发现有两个同学的QQ都是这个，很明显这是教务系统的管理员曾经在往数据库中插入数据时误操作了，但此时对于辅导员来说，他就傻眼了，此时辅导员如果不把两个学生叫到现场当面对峙，就无论如何也无法找到辱骂辅导员的那个学生。可以看到在这个案例中，即使你有主键标识每一条数据，也无法找到你想要的那一条数据，这就是为什么在上面说【在实际业务中光有主键约束可能是不够的】的原因了，这时就需要有唯一键约束来完善业务逻辑。

那么在上面的案例中，我们怎样就能解决这个问题呢？答案就是给属性qq设置唯一键约束，这样一来，曾经教务系统管理员在向数据库中插入数据时，如果误操作把两个同学的QQ号输成了一个，那么数据库就会报错、不让管理员插入该数据，这样一来后序辅导员也就不会遇到两个同学的QQ号相同的情况了，问题也就解决了。说一下，至于为什么不给属性qq设置主键约束以解决问题，是因为属性stu_id已经是主键了，在一张表中不能有两个主键约束。那可不可以给属性qq和属性stu_id设置复合主键约束呢？答案是也不行，因为复合主键约束是允许【两条数据在属性stu_id上的值不相等、在属性qq上的值相等】的情况发生的。

说一下，从上文中我们也能感受到为什么把唯一键称为一种约束，即就是因为，为某个属性设置唯一键约束后，如果用户在插入数据时该数据在该属性上的值和其他数据重复，则MySQL会不让用户完成插入，以此约束用户。

然后要知道的是，唯一键约束和主键约束都能保证数据的唯一性，它们的唯一区别在于：给某个属性A设置主键约束后，该属性A的值是不能为NULL的；但如果只是给某个属性A设置唯一键约束，该属性A上的值是可以为NULL的（那么多条数据在属性A上的值都是NULL，是否是一种不唯一、是否违反了唯一键约束呢？这里要说的是NULL不做唯一性比较，所以是不影响的）。所以以后我们就要知道，如果给一个属性A设置唯一键约束后再为其设置not null约束，那么即使因为已经有其他主键的存在导致属性A没有主键之名，属性A也是有主键之实的。从这里我们也就能感受到，主键具有唯一性并不是因为它是主键，而是因为，是某个唯一键被选择成为了主键。

最后咱们来实操演示一下unique唯一键约束，如下：

如下图所示，创建一个学生表，表中包含学生的学号、姓名和QQ号，我们为属性id设置主键约束，但同时因为每个学生的QQ号也应该具有唯一性，所以我们也为属性qq设置唯一键约束。

如下图所示，表创建完毕后desc查看表结构，可以看到属性qq对应的Key字段上的值是UNI，这就说明给属性qq设置唯一键成功了。

如下图所示，向表中插入数据时，如果该数据在qq属性上的值和表中已有的数据相同，则当前要插入的这条数据就会因为唯一键冲突而插入失败。

此外，因为在上文中说过【如果只是给某个属性设置唯一键约束，该属性上的值是可以为NULL的】，所以如下图所示，向表中插入数据时，可以不指明该数据在qq属性上的值，此时qq属性的值就会默认为NULL；当然也可以指明该数据在qq属性上的值为NULL，此时qq属性上的值也会为NULL。