【MySQL】表的约束

文章目录

表的约束

真正 **

约束

** 字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。

在MySQL中，约束（Constraints） 是指在创建表时规定的对表中的数据进行限制的条件。它可以对表中某列或某几列添加一些限制条件，以保证表中的数据符合要求，MySQL中的约束包括以下几种：

• **主键约束（Primary Key Constraint）**：用于标识表中每条记录的唯一性，每张表只能有一个主键，主键值不能重复且不能为空。
• **唯一约束（Unique Constraint）**：用于保证某个列的数据唯一性，每个表可以有多个唯一约束。
• **非空约束（Not Null Constraint）**：用于保证某个列的值不能为空，一个表中可以有多个非空约束。
• **外键约束（Foreign Key Constraint）**：用于关联两个表的数据，确保两个表之间的关联关系是有效的。外键约束必须在关联表的列上定义，他会限制在被关联的表中不能存在没有对应主键或唯一键值的记录。

空属性

MySQL中的 **

空属性

** 指的是一个字段没有值的情况，可以使用

null

关键字来表示。null是一个特殊的值，表示一个未知的、不存在的或不适用的值。与之相对的是空字符串（

' '

）， 它表示一个空的字符串值，不同于null。在MySQL中，可以在列定义时指定该列是否允许为空。如果列允许为空，则该列可以包含null值。否则，该列必须包含非null值。

空属性在数据库中很常见，比如一个订单表中，如果订单尚未付款，则该订单的付款时间段可能为空。在查询时，可以使用

is null

或

is not null

运算符来判断某个字段是否为空。

数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据库为空没办法参与运算。

示例：

创建一个班级表，包含班级名称和班级所在的教室。站在正常的业务逻辑中：

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

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

mysql>createtableifnotexists myclass(-> class_name varchar(20)notnull,-> class_room varchar(20)notnull,-> other varchar(20)->);
Query OK,0rows affected (0.01 sec)

mysql>desc myclass;+------------+-------------+------+-----+---------+-------+| Field      |Type|Null|Key|Default| Extra |+------------+-------------+------+-----+---------+-------+| class_name |varchar(20)|NO||NULL||| class_room |varchar(20)|NO||NULL||| other      |varchar(20)| YES  ||NULL||+------------+-------------+------+-----+---------+-------+3rowsinset(0.00 sec)

mysql>insertinto myclass (class_name, class_room, other)values('高三二班','101教师','普通班');
Query OK,1row affected (0.01 sec)

mysql>insertinto myclass (class_name, class_room)values('高三三班','1003教师');
Query OK,1row affected (0.00 sec)

mysql>select*from myclass;+--------------+------------+-----------+| class_name   | class_room | other     |+--------------+------------+-----------+| 高三二班     |101教师    | 普通班    || 高三三班     |1003教师   |NULL|+--------------+------------+-----------+

mysql>insertinto myclass (class_name)values('高三5班');
ERROR 1364(HY000): Field 'class_room' doesn't have a default value
mysql> insert into myclass (class_name, class_room) values ('高三5班', NULL);
ERROR 1048 (23000): Column 'class_room' cannot be null
mysql> insert into myclass (class_name, class_room) values (NULL, NULL);
ERROR 1048 (23000): Column 'class_name' cannot be null

这里我们可以看到班级名和班级教室不能为空。

同理，当我们插入空值时就会报错。

默认值

在MySQL中，可以为表的列指定默认值，当插入数据时，如果未显示指定该列的值，则会自动填充该列的值为默认值。这样可以方便地为表中地某些列设置默认值，避免重复性工作。

默认值可以是一个常量值，也可以是一个函数表达式，比如 CURRENT_TIMESTAMP 表示当前地时间戳。当使用函数表达式时，每次插入数据时该表达式都会被重新计算并填充为该列地默认值。

在创建表时可以通过 **

default

** 关键字来指定列地默认值。

mysql>createtableifnotexists t2(-> name varchar(20)notnull,-> age tinyintunsigneddefault18,-> gender char(2)default'男'->);
Query OK,0rows affected (0.01 sec)

mysql>desc t2;+--------+---------------------+------+-----+---------+-------+| Field  |Type|Null|Key|Default| Extra |+--------+---------------------+------+-----+---------+-------+| name   |varchar(20)|NO||NULL||| age    |tinyint(3)unsigned| YES  ||18||| gender |char(2)| YES  || 男      ||+--------+---------------------+------+-----+---------+-------+3rowsinset(0.00 sec)

mysql>insertinto t2 (name)values('张三');
Query OK,1row affected (0.01 sec)

mysql>insertinto t2 (name,age)values('李四',25);
Query OK,1row affected (0.00 sec)

mysql>insertinto t2 (name,gender)values('王五','女');
Query OK,1row affected (0.01 sec)

mysql>select*from t2;+--------+------+--------+| name   | age  | gender |+--------+------+--------+| 张三   |18| 男     || 李四   |25| 男     || 王五   |18| 女     |+--------+------+--------+3rowsinset(0.00 sec)

这里我们在创建表时，指定了姓名不能为空，年龄默认为18岁，性别为默认值。因此我们在插入’李四’时，未指定性别、默认为男；在插入’王五’时，未指定年龄，默认为18岁。

**

not null

和

default

结合**

• default 的意义是不显示地向指定列插入数据，default会起效果插入默认值。
• not null 的意义是如果显示地向指定列插入null，not null 进行约束不允许插入 null。如果只有default约束，没有not null约束，则可以向该列插入null。
• not null 和 default 一般不需要同时出现，因为default本身有默认值，不会为空。

列描述

**列描述（

comment

）** 是一个可选的属性，可以用来描述表中每个列的含义、作用、限制等信息。它通常在创建表时定义，并可以通过 show create table 语句查看。列描述不会影响数据库的结构和功能，它只是一个用于更好的理解和维护表结构的工具。

列描述通常用于以下目的：

• 说明列的含义和作用，方便开发人员和维护人员理解表的结构。
• 限制或规定数据的输入范围、格式、长度等，防止数据异常或错误的输入。
• 记录表或列的修改历史，方便维护人员进行版本管理和和回溯。

mysql>createtableifnotexists t3(-> name varchar(20)notnull,-> age tinyintdefault18comment'禁止18岁以下的用户注册',-> gender char(1)notnulldefault'男'comment'用户的性别'->);
Query OK,0rows affected (0.02 sec)

mysql>showcreatetable t3\G
***************************1.row***************************Table: t3
CreateTable: CREATETABLE`t3`(`name`varchar(20)NOTNULL,`age`tinyint(4)DEFAULT'18'COMMENT'禁止18岁以下的用户注册',`gender`char(1)NOTNULLDEFAULT'男'COMMENT'用户的性别')ENGINE=InnoDBDEFAULTCHARSET=utf8
1rowinset(0.00 sec)

mysql>insertinto t3 (name,age,gender)values('猪八戒',19,'男');
Query OK,1row affected (0.01 sec)

mysql>insertinto t3 (name,age)values('猪八戒',null);
Query OK,1row affected (0.00 sec)

mysql>select*from t3;+-----------+------+--------+| name      | age  | gender |+-----------+------+--------+| 猪八戒    |19| 男     || 猪八戒    |NULL| 男     |+-----------+------+--------+2rowsinset(0.00 sec)

列描述说明了每个列的含义和作用，以及表的含义。这可以帮助开发人员更好地理解表的结构，规范数据的输入，并且方便维护人员进行版本管理和回溯。

zerofill

通常情况下，MySQL会省略数值类型数据类型中的前导零。**

zerofill

** 是一种列属性，用于将数字类型的列填充为固定长度，填充的内容为0。在使用zerofill属性时，如果插入的数据长度小于指定长度。这个属性通常用于需要固定长度列，比如身份证号码等。需要注意的是，zerofill只对数值类型的数据类型有效，对于其他类型的列属性，无法设置zerofill约束。

int(len) 中的len表示整数类型字段的显示宽度，即在输出时该字段最多显示的字符数，它只是为了控制该字段在输出时的显示效果，而不会影响该字段在存储时的大小和范围。如果超过了定义的显示宽度，则将该数字进行原样显示。int(len)需要设置了zerofill属性，才会起作用。注：10位能够覆盖unsigned int的全部数据，而 int 比 unsigned int 多一位符号位。

mysql>createtableifnotexists t4(-> num1 int,-> num2 int(5)unsigned zerofill
    ->);
Query OK,0rows affected (0.01 sec)

mysql>insertinto t4 values(10,20);
Query OK,1row affected (0.00 sec)

mysql>insertinto t4 values(12345,54321);
Query OK,1row affected (0.00 sec)

使用zerofill 属性的主要优点是可以确保数据以相同的格式存储，这在某些情况下可以提高查询和排序的性能。此外，它还可以是数据更易于阅读和比较。

主键

**

主键（Primary Key）

**：用于唯一标识表中每一条记录，确保记录的唯一性和完整性。主键列不允许重复，不允许为空。一个表中最多只能有一个主键，主键的所在列通常是整数类型。

mysql>createtableifnotexists t5(-> id intunsignedprimarykeycomment'学生的学号是主键',-> name varchar(20)notnull->);
Query OK,0rows affected (0.02 sec)

• 主键约束：主键对应的字段中不能重复，一旦重复，操作失败。
• 当表创建好之后但没有主键的时候，可以再次追加主键。

💕 追加主键

altertable 表名 addprimarykey(字段列表);

💕 删除主键

altertable 表名 dropprimarykey;

💕 复合主键

**

复合主键

是指在一个表中，主键由多个列组成而不是单独的一列**。这样的设计可以更准确地表示实际数据地唯一性，因为在复杂地应用场景中，单独的一列很可能不能完全确定唯一性。

注意，复合主键不同于单一地主键，其顺序是有意义的。因此需要根据实际情况来确定复合主键的顺序，以确保能够把正确地反映数据的唯一性。

mysql>createtableifnotexists t6(-> id varchar(20)comment'学号',-> course varchar(30)comment'课程编号',-> score tinyintunsigneddefault60comment'成绩',->primarykey(id, course)->);
Query OK,0rows affected (0.01 sec)

mysql>desc t6;+--------+---------------------+------+-----+---------+-------+| Field  |Type|Null|Key|Default| Extra |+--------+---------------------+------+-----+---------+-------+| id     |varchar(20)|NO| PRI |NULL||| course |varchar(30)|NO| PRI |NULL||| score  |tinyint(3)unsigned| YES  ||60||+--------+---------------------+------+-----+---------+-------+3rowsinset(0.00 sec)

mysql>showcreatetable t6\G
***************************1.row***************************Table: t6
CreateTable: CREATETABLE`t6`(`id`varchar(20)NOTNULLCOMMENT'学号',`course`varchar(30)NOTNULLCOMMENT'课程编号',`score`tinyint(3)unsignedDEFAULT'60'COMMENT'成绩',PRIMARYKEY(`id`,`course`))ENGINE=InnoDBDEFAULTCHARSET=utf8
1rowinset(0.00 sec)

💕 设计主键的原则：

• 唯一性：主键必须保证表中每一行数据都有唯一的标识符，避免数据冲突。
• 稳定性：主键应该是一个稳定的标识符，不随数据的变化而变化。
• 简洁性：主键应该尽可能的简洁，使其在索引、关联、聚合等操作中具有高效性。
• 可读性：主键可以是自然键（如身份证号码、学号等）或人工键（如自增长 ID），需要根据实际业务情况进行选择。注：可以将选择与业务无关的值作为主键，这样的好处是业务调整不会影响主键的整体表结构。

设计主键时需要根据具体的业务需求来确定，一般情况下可以选择使用自增长 ID 作为主键，也可以选择一个稳定、唯一、简洁的自然键作为主键。

自增长

**

自增长（Auto Increment）

** 是MySQL中一种常见的特殊类型的列属性，用于再插入数据时自动分配递增的值。一般来说，自增长属性适用于那些需要每次插入一条新纪录时，自动生成一个唯一的、递增的编号的表中的列。自增长通常和主键搭配使用，作为逻辑主键。

自增长的特点：

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

mysql>createtableifnotexists t7(-> id intunsignedprimarykeyauto_increment,-> name varchar(20)notnull->);

当我们显示插入id值时，会影响到 auto_increment，auto_increment 等于这列的最大值+1，为下一次插入的自增值。

💕 指定自增起始值

altertable table_name auto_increment= 起始值;

💕 设置自增步长

# mysql自增的步长showsession variables like'auto_inc%';# 基于会话级别setsession auto_increment_increment=2# 修改会话级别的步长# 基于全局级别的setglobal auto_increment_increment=2# 修改全局级别的步长(所有会话都生效)

索引

在关系型数据库中，**

索引

** 是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单，可以提高对数据库表中数据的访问速度。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

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

• **主键索引**：主键索引是一种唯一性索引，用于确保表中每一行数据的唯一性。主键索引可以自动创建，也可以手动指定，通常情况下使用自增长整数作为主键。
• **唯一索引**：唯一索引也是一种唯一性索引，与主键索引类似，但允许为空。唯一索引可以用于确保表中某一列的唯一性。
• **普通索引**：普通索引是最常用的一种索引类型，用于加速对表中数据的查找速度。普通索引可以用于单列或多列，可以使用B树或hash算法实现。
• **全文索引**：全文索引是一种特殊类型的索引，用于加速对表中文本类型数据的查找速度。全文索引支持自然语言搜索和布尔搜索等多种搜索方式。

虽然索引可以提高查询性能，但是在设计索引时也需要注意，过多或不必要的索引会影响数据更新的性能，增加数据存储空间，并且索引的设计需要结合具体业务场景和查询需求。

唯一键

**

唯一键（Unique Key）

** 是一种约束，它用于保证某个列的数据唯一性，每个表可以有多个唯一约束。与主键不同的是，唯一键允许空值，即可以在列中包含空值，但不能有重复值。

在现实生活中，我们身上有非常多具有唯一性的值，如身份证号、QQ号等。一般而言，主键只是众多具有唯一性的属性列中的一列，该列被选择成为主键。但这并不意味着其他具有唯一性的列不需要保证唯一性。但是主键只能有一个，所以MySQL就提供了另一个保证列信息唯一性的方法：唯一键。唯一键和主键并不冲突，两者时互相补充的，共同维护表的完整性！！！

举个栗子：

假设一个场景(当然，具体可能并不是这样，仅仅为了帮助大家理解)
比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，我们可以选择身份号码作为主键。

而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。

具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键。

一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整。

mysql>createtableifnotexists students( id intunsignedprimarykeyauto_increment, name varchar(20)notnull, qq varchar(30)uniquecomment'QQ号需要保证唯一性')auto_increment=1000;
Query OK,0rows affected (0.01 sec)

mysql>insertinto students(name,qq)values('张三',10001);
Query OK,1row affected (0.01 sec)

mysql>insertinto students(name,qq)values('李四',10001);
ERROR 1062(23000): Duplicate entry '10001'forkey'qq'
mysql>insertinto students(name,qq)values('李四',10002);
Query OK,1row affected (0.00 sec)

mysql>select*from students;+------+--------+-------+| id   | name   | qq    |+------+--------+-------+|1000| 张三   |10001||1002| 李四   |10002|+------+--------+-------+2rowsinset(0.00 sec)

这里我们可以看到，当我们插入(‘李四’, 10001)时，产生了唯一键冲突。

如果给唯一键加上 not null 约束，那么这个唯一键和主键就非常相似了。不过两者还有一些差别，在索引部分将进行详解。

外键

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

比如先创建一个班级表作为

主表

，表当中包含班级的id和班级名，并将班级id设置为主键。如下：

mysql>createtable class_table(-> class_id intunsignedprimarykeycomment'班级id',-> name varchar(20)notnullcomment'班级名'->);
Query OK,0rows affected (0.02 sec)

再创建一个学生表作为

从表

，表当中包含学生的id、姓名以及学生所在班级对应的id，并将学生表中的班级id列设置成外键，关联到班级表中的班级id列。如下：

mysql>createtable student_table(-> stu_id intunsignedprimarykeycomment'学生id',-> name varchar(20)notnullcomment'学生姓名',-> class_id intunsignedcomment'学生所在的班级对应的id',->foreignkey(class_id)references class_table(class_id)->);
Query OK,0rows affected (0.01 sec)

表创建完毕后查看学生表的表结构，可以看到学生表中的班级id对应的Key列出现了MUL标志，这表明class_id已经被成功设置成了外键。如下：

为了演示外键约束，我们先向班级表中插入两条记录。如下：

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

这里我们可以看到，当插入学生表的这条记录所对应的班级并不存在时，此时将会插入失败，这就是外键约束。

说明一下：

• 理论上来说，我们创建班级表和学生表后就算不设置外键，在语义上其实也已经有了外键，但这样我们没办法保证后续插入学生表的记录中的班级id的正确性。
• 而我们给学生表中的班级id设置外键后，外键约束就能保证只有班级id在班级表中存在的记录才能插入学生表，否则就会插入失败。
• 实际建立外键的本质就是把相关性交给MySQL去审核了，提前告诉MySQL表之间的约束关系，当用户插入不符合业务逻辑的数据时，MySQL就不允许我们进行插入。

综合案例

案例描述

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

• 商品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

要求：

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

SQL编写

首先我们需要创建一个数据库，然后在该数据库中完成这三张表的创建。如下：

mysql>createdatabase store_data;
Query OK,1row affected (0.00 sec)

mysql>use store_data;Database changed

创建商品表时，将商品编号设置成主键并且可以将其设置成自增长字段，其他字段的属性没有要求可以自行合理设置。如下：

mysql>createtable goods(-> goods_id intprimarykeyauto_incrementcomment'商品编号',-> goods_name varchar(32)notnullcomment'商品名称',-> unitprice intnotnulldefault0comment'单价(分)',-> category varchar(64)notnullcomment'供应商'->);
Query OK,0rows affected (0.02 sec)

mysql>desc goods;+------------+-------------+------+-----+---------+----------------+| Field      |Type|Null|Key|Default| Extra          |+------------+-------------+------+-----+---------+----------------+| goods_id   |int(11)|NO| PRI |NULL|auto_increment|| goods_name |varchar(32)|NO||NULL||| unitprice  |int(11)|NO||0||| category   |varchar(64)|NO||NULL||+------------+-------------+------+-----+---------+----------------+4rowsinset(0.00 sec)

创建客户表时，将客户编号设置成主键并且可以将其设置成自增长字段，然后给姓名设置 not null 属性，将邮箱设置成唯一键，将邮箱设置成唯一键，将性别设置成enum类型并仅提供男女性别选项，此外，题目虽然没有对身份证做要求，但正常来说身份证也应该保持唯一性，最好设置成唯一键。如下：

mysql>createtable customer(-> customer_id intprimarykeyauto_incrementcomment'客户编号',-> name varchar(32)notnullcomment'客户姓名',-> address varchar(256)notnullcomment'客户住址',-> email varchar(64)uniquecomment'客户邮箱',-> sex enum('男','女')notnullcomment'客户性别',-> card_id char(18)uniquecomment'客户身份证'->);
Query OK,0rows affected (0.02 sec)

mysql>desc customer;+-------------+-------------------+------+-----+---------+----------------+| Field       |Type|Null|Key|Default| Extra          |+-------------+-------------------+------+-----+---------+----------------+| customer_id |int(11)|NO| PRI |NULL|auto_increment|| name        |varchar(32)|NO||NULL||| address     |varchar(256)|NO||NULL||| email       |varchar(64)| YES  | UNI |NULL||| sex         |enum('男','女')|NO||NULL||| card_id     |char(18)| YES  | UNI |NULL||+-------------+-------------------+------+-----+---------+----------------+6rowsinset(0.00 sec)

创建购买表时，将订单号设置成主键并且可以将其设置成自增长字段，然后将客户编号和商品编号设置成外键，分别关联到客户表和商品表中的客户编号和商品编号，用外键约束来保证每一个订单的客户编号和商品编号都是存在的。如下：

mysql>createtable purchase(-> order_id intprimarykeyauto_incrementcomment'订单号',-> customer_id intcomment'客户编号',-> goods_id intcomment'商品编号',-> nums intdefault1comment'购买数量',->foreignkey(customer_id)references customer(customer_id),->foreignkey(goods_id)references goods(goods_id)->);
Query OK,0rows affected (0.02 sec)
mysql>desc purchase;+-------------+---------+------+-----+---------+----------------+| Field       |Type|Null|Key|Default| Extra          |+-------------+---------+------+-----+---------+----------------+| order_id    |int(11)|NO| PRI |NULL|auto_increment|| customer_id |int(11)| YES  | MUL |NULL||| goods_id    |int(11)| YES  | MUL |NULL||| nums        |int(11)| YES  ||1||+-------------+---------+------+-----+---------+----------------+4rowsinset(0.00 sec)