【MySQL】表的约束

> 作者：დ旧言~
> 座右铭：松树千年终是朽，槿花一日自为荣。

> 目标：了解表约束关键字，并能熟练使用。

> 毒鸡汤：有些事情，总是不明白，所以我不会坚持。早安!

> 专栏选自：带你玩转MySQL

> 望小伙伴们点赞👍收藏✨加关注哟💕💕

​​

一、前言

想必大家在学校也学习过MySQL，可能学的懵懵懂懂，这个板块我们从入门开始，从最新的安装MySQL到学习MySQL语句，一步一步开始，一切都是新的，新的板块新的开始，大家一起努力，一起进步！！！

 二、主体

学习【MySQL】表的约束咱们按照下面的图解：

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

2.1空属性

概念：

在MySQL中，空属性约束指定了某一列是否可以包含NULL值。它们用于各种目的，例如数据验证和限制数据的输入格式。以下是空属性约束的详细解释：

• NOT NULL: 当使用NOT NULL属性约束时，将禁止该列包含NULL值。这意味着在插入或更新数据时，该列必须包含有效的数值或字符，不能为NULL。
• NULL: 如果没有显式地指定NULL或NOT NULL属性约束，那么默认情况下，列可以包含NULL值。这意味着在插入或更新数据时，如果没有提供有效的值，可以将该列的值设置为NULL。

举个栗子：

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

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

解释：

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

演示代码：

mysql> create table if not exists myclass(
    -> class_name varchar(20) not null,
    -> class_room varchar(20) not null,
    -> other varchar(20)
    -> );
Query OK, 0 rows 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    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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

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

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

mysql> insert into 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

结果：

• 从上图中可以看到，当对字段设置了不能为空时，插入的时候就不能再插入NULL值。当可以为空时，插入NULL值和其对应的类型的值均为可以的。
• 在实际应用开发中，比如我们在插入数据并不能准确的知道数据的值，就可以暂时插入NULL值。当设置了不能为空时，这就约束着必须插入有效的值。
• 通过合理地应用空属性约束，可以有效地避免数据中的空值或缺失值，从而提高数据质量和可靠性。

2.2默认值

概念：

1. 在MySQL中，默认值约束用于定义表列的默认值。当插入新记录时，如果没有为该列提供值，则可以使用默认值来填充该列。这有助于减少数据冗余并提高数据库的一致性。
2. 当某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

举个栗子：

mysql> create table if not exists t2(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 18,
    -> gender char(2) default '男'
    -> );
Query OK, 0 rows 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  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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

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

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

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

解释：

那么 default约束 和 空属性约束 是不是冲突的呢？当我们设置了default时，这就意味着即使我们不插入数据也不为空，因为会默认使用默认值填充。default约束和not null约束同时出现并没有太大的意义。因为一但设置的default约束，就可以保证了 not null 约束。但是我们要区分的是：

• default 是我们不显示的向指定列插入，default会自动插入。
• NULL 是显示的向一列插入。如果插入正常值，就正常工作。如果不确定就插入NULL。但是有not null约束时，就必须插入有效值。

2.3列描述

概念：

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

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

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

举个栗子：

mysql> create table if not exists t3(
    -> name varchar(20) not null,
    -> age tinyint default 18 comment '禁止18岁以下的用户注册',
    -> gender char(1) not null default '男' comment '用户的性别'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `name` varchar(20) NOT NULL,
  `age` tinyint(4) DEFAULT '18' COMMENT '禁止18岁以下的用户注册',
  `gender` char(1) NOT NULL DEFAULT '男' COMMENT '用户的性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

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

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

2.4zerofill

概念：

• 在MySQL中，zerofill约束是一种用于数值字段的约束，它会在数值字段的值前面添加零，使其达到指定的长度。这通常用于保持数值字段的位数一致性，尤其对于需要显示位数对齐的情况非常有用。
• 当你在MySQL中创建一个数值字段，并为其添加zerofill约束时，MySQL会自动在存储数据时将数值填充为指定长度，并在需要的情况下，自动在数值前面添加零。

说明：

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

举个栗子：

mysql> create table if not exists t4(
    -> num1 int,
    -> num2 int(5) unsigned zerofill
    -> );
Query OK, 0 rows affected (0.01 sec)

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

2.5主键

概念：

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

举个栗子：

// 表结构

mysql> create table if not exists t5(
    -> id int unsigned primary key comment '学生的学号是主键',
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

总结：

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

2.5.1复合主键

概念：

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

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

举个栗子：

mysql> create table if not exists t6(
    -> id varchar(20) comment '学号',
    -> course varchar(30) comment '课程编号',
    -> score tinyint unsigned default 60 comment '成绩',
    -> primary key(id, course)
    -> );
Query OK, 0 rows 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      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `id` varchar(20) NOT NULL COMMENT '学号',
  `course` varchar(30) NOT NULL COMMENT '课程编号',
  `score` tinyint(3) unsigned DEFAULT '60' COMMENT '成绩',
  PRIMARY KEY (`id`,`course`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.5.2设计主键原则

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

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

2.6自增长

概念：

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。一般来说，自增长属性适用于那些需要每次插入一条新纪录时，自动生成一个唯一的、递增的编号的表中的列。自增长通常和主键搭配使用，作为逻辑主键。

自增长的特点：

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

举例说明：

举个栗子：

2.6.1指定自增起始值

语法：

alter table table_name auto_increment = 起始值;

举个栗子：

2.6.2设置自增步长

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

2.7唯一键

概念：

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

假设一个场景(当然，具体可能并不是这样，仅仅为了帮助大家理解)

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

举个栗子：

mysql> create table if not exists students( 
        id int unsigned primary key auto_increment, 
        name varchar(20) not null, 
        qq varchar(30) unique comment 'QQ号需要保证唯一性'
     )auto_increment=1000;
Query OK, 0 rows affected (0.01 sec)

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

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

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

2.8外键

概念：

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

举个栗子：

mysql> create table student_table(
    -> stu_id int unsigned primary key comment '学生id',
    -> name varchar(20) not null comment '学生姓名',
    -> class_id int unsigned comment '学生所在的班级对应的id',
    -> foreign key(class_id) references class_table(class_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

并将学生表中的班级id列设置成外键，关联到班级表中的班级id列

解释说明：

删除后

总结：

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

2.9综合案例

案例描述：

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

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

要求：

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

总代码：

-- 创建数据库
create database if not exists bit32mall
default character set utf8 ;
-- 选择数据库
use bit32mall;
-- 创建数据库表
-- 商品
create table if not exists goods
(
  goods_id  int primary key auto_increment comment '商品编号',
  goods_name varchar(32) not null comment '商品名称',
  unitprice  int  not null default 0 comment '单价，单位分',
  category  varchar(12) comment '商品分类',
  provider  varchar(64) not null comment '供应商名称'
);
-- 客户
create table if not exists customer
(
  customer_id  int primary key auto_increment comment '客户编号',
  name varchar(32) not null comment '客户姓名',
  address  varchar(256) comment '客户地址',
  email  varchar(64) unique key comment '电子邮箱',
  sex  enum('男','女') not null comment '性别',
  card_id char(18) unique key comment '身份证'
);
-- 购买
create table if not exists purchase
(
  order_id  int primary key auto_increment comment '订单号',
  customer_id int comment '客户编号',
  goods_id  int comment '商品编号',
  nums  int default 0 comment '购买数量',
  foreign key (customer_id) references customer(customer_id),
  foreign key (goods_id) references goods(goods_id)
);

三、结束语 

       今天内容就到这里啦，时间过得很快，大家沉下心来好好学习，会有一定的收获的，大家多多坚持，嘻嘻，成功路上注定孤独，因为坚持的人不多。那请大家举起自己的小手给博主一键三连，有你们的支持是我最大的动力💞💞💞，回见。

​​ 、