【MySQL】表的约束 -- 详解

• 表中一定要有各种约束，通过约束让我们在未来插入数据库表中的数据是符合预期的。约束本质是通过技术手段倒逼程序员插入正确的数据，反过来站在 MySQL 的角度，凡是插入进来的数据都是符合数据约束的。
• 约束的最终目标：保证数据的完整性和可预期性。
• 真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是 email，要求是唯一的。
• 表的约束很多，这里主要介绍如下几个： null/not null，default，comment，zerofill，primary key，auto_increment，unique key 。

---

一、非空约束

1、空属性

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

 

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

插入数据时，没有给教室数据插入失败。 

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

---

二、default 约束

1、默认值

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

如果设置 default 后，用户插入了具体的数据，那么就用用户所提供的数据。 如果 数据在插入的时候不给该字段赋值，那么就使用默认值。

• 如果没有明确指定某一列要插入，默认用的是 default。
• 如果在建表时，对应列不能为空且默认没有设置 default 值，则无法直接插入数据。 

  • 当用户忽略某一列时，使用 default 默认值（如果设置了）。如果没有设置 default 值，则直接报错。
  • null 和 not null 主要是用来约束用户想插入的内容。
  • default 和 not null 二者并不冲突，而是相互补充的。

    注意：只有设置了 default 的列，才可以在插入值时，对列进行省略。

  这里的 age 没有 not null 的约束，同时具有 default 默认值。

  总结：

  • 如果用户想显示的插入，要么就插入一个具体的值，要么就插入 NULL。
  • 如果设置了 not null，那么插入 NULL 就会被拦截。
  • 如果没有设置 default，那么这一列就不能省略。
  • 如果在建表时没有设置 default 默认值（前提是没有设置 not null），那么 MySQL 会对我们的 sql 做优化，默认加了 default null。
  • not null 和 defalut 一般不需要同时出现，因为 default 本身有默认值，不会为空。

  ---

  三、comment

  1、列描述

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

  通过 desc 查看不到注释信息： 

  通过  show  可以看到：

  ---

  四、zerofill

  下面通过 show 看看 t17 表的建表语句：

  可以看到 int(10)，这个代表什么意思呢？整型不是 4 字节码吗？那这个 10 又代表什么呢？

  其实没有  zerofill  这个属性，括号内的数字是毫无意义的。a  和  b  列就是前面插入的数据，如下： 但是对列添加了  zerofill  属性后，显示的结果就有所不同了。修改  t17  表的属性： 对 b   列添加了  zerofill  属性，再进行查找，返回如下结果：

  这次可以看到 b 的值由原来的 2 变成 0000000002，这就是 zerofill 属性的作用，如果宽度小于设定的宽度（这里设置的是 10），自动填充 0。

  要注意的是，这只是最后显示的结果，在 MySQL 中实际存储的还是 2。

  为什么是这样呢？

  我们可以用 hex（十六进制） 函数来证明。 可以看出数据库内部存储的还是 2，0000000002 只是设置了  zerofill  属性后的一种格式化输出而已。 修改括号内的值： 插入数据：

  不够就补 0，补到括号内的数字位数为止，如果够了就直接显示。

  为什么建表时默认是 int(10)？

  因为一个整数占 4 个字节，4 个字节的取值范围：有符号是 -2^31~2^31-1，无符号是 0~2^32-1。

  2^31-1 和 2^32-1 最后表示出来都是十位，所以用 int(10) 就足以把所有对应的整数的数据位表示出来。

  有符号是 11 位，多了 1 是因为多了一个符号位。

  ---

  五、主键

  主键 ： primary key   用来 唯一 的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键，但不意味着一个表中的主键只能添加给一列。说明一个主键可以被添加到一列或多列，添加给多列则叫作复合主键。 主键所在的列通常是整数类型。

  ---

  1、指定主键

  • 创建表的时候直接在字段上指定主键。

    Key 中 PRI 表示该字段是主键。

    ---

    2、主键约束

    • 主键约束：主键对应的字段中不能重复，一旦重复，操作失败。

      ---

      3、追加主键

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

        ---

        4、删除主键

        ---

        5、复合主键

        在创建表的时候，在所有字段之后，使用  primary key(主键字段列表)   来创建主键，如果有多个字段作为主键，可以使用复合主键。

        复合主键中的字段有个别发生冲突并不影响，但如果主键中多列的列信息都与历史的信息发生冲突了才会触发主键约束。

        ---

        六、自增长

        1、auto_increment

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

        自增长的特点：

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

        在插入后获取上次插入的 auto_increment 的值（批量插入获取的是第一个值）

        索引：

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

        ---

        七、唯一键约束

        一张表中往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。 唯一键的本质和主键差不多，唯一键 允许为空 ，而且可以多个为空，空字段不做唯一性比较。

        ---

        1、唯一键和主键的区别

        可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上不要和别的信息出现重复。 乍一听好像没啥区别，下面给大家作一个假设来帮助理解：

        在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个是身份证号码，一个是员工工号，我们可以选择身份号码作为主键。 那么我们在设计员工工号时，需要一种约束：所有的员工工号都不能重复。具体指的是在公司的业务上不能重复。 我们在设计表时，需要这个约束，那么就可以将员工工号设计成为唯一键。一般而言，建议将主键设计成和当前业务无关的字段。这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整。

        当 id 为 NULL 时，是可以直接插入且不冲突的。

        • 对应的主键一旦设置好，不能和其它键冲突，而且主键不能为空。
        • 唯一键表达了与主键类似的功能，可以为空，NULL 不参与计算，与语言上的 NULL 不一样。

         

        上面的设计中，数据库有逻辑上的错误，在现实生活中，不同的人电话号码不同。

        毫无疑问，主键要保证唯一性，同时并不否认其它列属性不需要唯一性，如果也需要就得带上，避免出现上述情况。

        唯一约束不能重复，但可以为 null。

        总结：

        • 在技术上，唯一键可以为空，主键不能为空。
        • 主键通常用来标定某一行记录在整表中的唯一性，而唯一键的侧重点在于让用户插入的列值和表中其它的列值不要发生冲突，从而保证在业务上、在上层对应的字段的唯一性。
        • 二者不是对立的，是互相补充的。主键保证表中记录的唯一性，唯一性保证列中的信息不会出现重复。

        ---

        八、外键约束

        外键 用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或  unique  约束。 当定义外键后，要求外键列数据必须在主表的主键列存在或为 null。 外键的本质：产生关联，增加约束，来整体增加表和表之间的完整性。

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

        ---

        对上面的示意图进行设计：

        • 先创建主键表

          • ---

            再创建从表

            • ---

              正常插入数据

              • ---

                插入一个班级号为 10 的学生，因为没有这个班级，所以插入不成功

                • ---

                  插入班级id为null，比如来了一个学生，目前还没有分配班级

                  • ---

                    删除班级

                    ---

                    如何理解外键约束？

                    首先我们承认，这个世界是数据很多都是相关性的。 理论上，上面的例子，我们不创建外键约束，就正常建立学生表和班级表，该有的字段我们都有。此时，在实际使用的时候，可能会出现什么问题？ 有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中呢？ 比如只开了 16 个 班， 但是在上课的学生里面竟然有  17  班的学生（ 这个班目前并不存在） ，这很明显是有问题的。因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。 解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给 MySQL 去审核了，提前告诉 MySQL 表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，M ySQL 不允许你插入。