【MySQL数据库基础】（六）MySQL 表的约束详解：从基础到实战，拿捏数据合法性！

前言

        在 MySQL 数据库开发中，我们总希望存入表中的数据是合法、规范、符合业务逻辑的。虽然数据类型能对字段做基础限制，但面对复杂的业务需求，仅靠数据类型远远不够。比如要求邮箱唯一、用户名不能为空、学生的班级必须是已存在的班级…… 这些需求都需要靠表的约束来实现。

        表的约束是数据库保证数据完整性的核心手段，它能从业务逻辑层面过滤无效数据，避免脏数据进入数据库。今天这篇文章就带大家全面吃透 MySQL 中最常用的表约束，包括null/not null、default、comment、zerofill、primary key、auto_increment、unique key、foreign key，从基础概念到实操案例，手把手教你用约束拿捏数据合法性！下面就让我们正式开始吧！

一、为什么需要表的约束？

        先看一个简单的例子：如果我们创建一个班级表，只定义字段和数据类型，不添加任何约束，会发生什么？

-- 无约束的班级表 create table myclass( class_name varchar(20), class_room varchar(10) ); -- 可以插入无班级名、无教室的无效数据 insert into myclass values(null, null); 

        这样插入的数据完全没有业务意义 —— 没有班级名，不知道是哪个班级；没有教室，不知道在哪上课。而如果我们添加了约束，MySQL 就会直接拒绝这类无效数据的插入，从源头保证数据质量。

        简单来说，数据类型约束是 “语法层” 的，表的约束是 “业务层” 的。数据类型决定字段能存什么类型的数据（比如字符串还是数字），表的约束决定字段能存什么值（比如是否为空、是否唯一）。

二、基础约束：搞定字段的基础规则

        基础约束主要针对单个字段的基础属性做限制，是日常开发中使用频率最高的约束，包括空属性null/not null、默认值default、列描述comment、零填充zerofill，上手简单，效果显著。

2.1 空属性：null /not null

        MySQL 中字段的空属性只有两个值：null（默认值，允许字段为空）和not null（禁止字段为空）。

        核心原则：实际开发中，尽可能让字段设置为not null！因为null值无法参与任何运算，会导致查询和统计出现意想不到的问题。

-- 测试null值的运算 select 1 + null; -- 结果为NULL select concat('hello', null); -- 结果为NULL 

        如果字段存了null，后续做求和、拼接、筛选时都要额外处理，增加开发成本。

实操案例：创建有非空约束的班级表，要求班级名和教室都不能为空：

-- 创建带not null约束的班级表 create table myclass( class_name varchar(20) not null, class_room varchar(10) not null ); -- 正常插入：字段都有值，插入成功 insert into myclass values('Python01班', '301教室'); -- 无效插入：缺少class_room字段，插入失败 insert into myclass(class_name) values('Python02班'); -- 报错：ERROR 1364 (HY000): Field 'class_room' doesn't have a default value 

        通过desc 表名可以查看字段的空属性约束：

desc myclass; 

        查询结果中Null列显示NO，代表该字段禁止为空。

2.2 默认值：default

        在业务中，某些字段会经常性出现固定值，比如性别默认是 “男”、年龄默认是 0、购买数量默认是 1。这时就可以用default约束为字段设置默认值，插入数据时如果不指定该字段的值，MySQL 会自动填充默认值。

        核心特点：只有设置了default的字段，插入数据时才能省略该字段；not null和default一般不同时使用（因为default本身有值，字段不会为空）。

        实操案例：创建用户表，设置年龄默认 0、性别默认男：

-- 创建带default约束的用户表 create table tt10 ( name varchar(20) not null, -- 用户名不能为空，无默认值 age tinyint unsigned default 0, -- 年龄默认0，无符号 sex char(2) default '男' -- 性别默认男 ); -- 插入数据：只传用户名，年龄和性别使用默认值 insert into tt10(name) values('张三'); -- 查询结果：name=张三，age=0，sex=男 select * from tt10; -- 插入数据：手动指定值，覆盖默认值 insert into tt10 values('李四', 20, '女'); 

        通过desc tt10可以看到Default列显示了字段的默认值，直观清晰。

2.3 列描述：comment

    comment约束是“程序员友好型”约束，它没有任何实际的约束效果，仅仅是用来描述字段的含义，方便程序员或 DBA 理解表结构。比如name varchar(20) comment '用户名'，一眼就能知道这个字段存的是什么。

        注意点：通过desc 表名无法查看comment注释，需要通过show create table 表名\G查看（\G是将查询结果按行垂直显示，更易读）。

        实操案例：创建带字段注释的用户表：

-- 创建带comment的用户表 create table tt12 ( name varchar(20) not null comment '姓名', age tinyint unsigned default 0 comment '年龄', sex char(2) default '男' comment '性别' ); -- 查看表的完整创建语句，包含注释 show create table tt12\G; 

        查询结果中会清晰显示每个字段的COMMENT属性，这在团队协作中尤为重要 —— 别人接手你的数据库时，能通过注释快速理解表结构。

2.4 零填充：zerofill

        很多初学者会对 MySQL 数字类型后的长度产生疑惑，比如int(10)，难道代表只能存 10 位数字？其实如果没有 zerofill 属性，数字类型后的长度毫无意义，它既不限制字段的存储长度，也不限制显示长度。

    zerofill的作用是格式化数字显示：当字段的值的长度小于设定的宽度时，自动在左侧填充 0，让显示长度等于设定的宽度。注意：这只是显示效果，MySQL 底层实际存储的还是原始数值，并非填充后的数值。

        实操案例：测试 zerofill 的零填充效果：

-- 先创建无zerofill的表 create table tt3 ( a int(10) unsigned DEFAULT NULL, b int(10) unsigned DEFAULT NULL ); -- 插入数据 insert into tt3 values(1,2); -- 查询结果：a=1，b=2，无填充 select * from tt3; -- 为a字段添加zerofill约束，设置宽度5 alter table tt3 change a a int(5) unsigned zerofill; -- 再次查询：a=00001，b=2，a字段左侧填充0至5位 select * from tt3; -- 验证底层存储的是原始值：用hex函数查看十六进制 select a, hex(a) from tt3; -- hex(1)=1，证明底层存的是1 

    zerofill通常用于需要固定长度显示数字的场景，比如订单号、编号等，让数据显示更规范。

三、核心唯一约束：主键与自增长

        在数据库中，我们需要一个字段来唯一标识表中的每一行数据，比如学生的学号、用户的 ID、商品的编号。这个字段就需要用到主键（primary key），而配合主键使用的自增长（auto_increment）能自动生成唯一的标识值，是开发中最常用的组合约束。

3.1 主键：primary key

        主键是 MySQL 中最严格的约束，它的核心规则：

主键字段的值不能重复、不能为空；一张表最多只能有一个主键；主键字段通常设置为整数类型（查询效率更高）；可以由单个字段作为主键，也可以由多个字段组成复合主键。

        主键的作用是唯一标识表的每一行，让数据库能快速定位到某一条数据，同时保证数据的唯一性。

3.1.1 单字段主键

        创建表时，直接在字段后指定primary key即可设置单字段主键。

-- 创建学生表，学号id作为主键 create table tt13 ( id int unsigned primary key comment '学号，主键不能为空', name varchar(20) not null comment '姓名' ); -- 正常插入：id唯一且非空 insert into tt13 values(1, '张三'); -- 插入失败：id重复，主键约束生效 insert into tt13 values(1, '李四'); -- 报错：ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' -- 插入失败：id为空，主键约束生效 insert into tt13(name) values('王五'); 

        通过desc tt13可以看到Key列显示PRI，代表该字段是主键。

3.1.2 主键的追加与删除

        如果表创建后没有设置主键，后续可以通过alter table语句追加主键；如果主键设置错误，也可以删除主键。

-- 追加主键：alter table 表名 add primary key(字段名) alter table 表名 add primary key(id); -- 删除主键：alter table 表名 drop primary key; alter table tt13 drop primary key; 

        注意：删除主键后，字段的not null约束不会消失，需要单独修改。

3.1.3 复合主键

        某些场景下，单个字段无法唯一标识数据，需要多个字段组合才能唯一标识，这就是复合主键。比如学生的成绩表，学号id和课程代码course组合起来才能唯一标识某一个学生的某一门课程成绩（一个学生可以有多门课程，一门课程可以有多个学生）。

        创建复合主键的方式：在所有字段后，通过primary key(字段1, 字段2, ...)指定。

-- 创建成绩表，id和course组成复合主键 create table tt14( id int unsigned, course char(10) comment '课程代码', score tinyint unsigned default 60 comment '成绩', primary key(id, course) -- 复合主键 ); -- 正常插入：id和course组合唯一 insert into tt14 (id,course)values(1, '123'); -- 插入失败：id和course组合重复 insert into tt14 (id,course)values(1, '123'); -- 报错：ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY' 

        复合主键的约束规则：组合的字段值整体唯一，单个字段可以重复。比如可以插入(1, '456')、(2, '123')，这些组合都是唯一的。

3.2 自增长：auto_increment

        自增长auto_increment是配合主键使用的辅助约束，它的作用是：当主键字段插入数据时不给值，MySQL 会自动从当前字段的最大值 + 1，生成一个唯一的新值，避免手动设置主键时出现重复。

3.2.1 自增长的核心特点

自增长字段必须是索引（Key列有值，主键本身就是索引，所以完美适配）；自增长字段必须是整数类型；一张表最多只能有一个自增长字段；批量插入数据时，last_insert_id()获取的是第一个自增长值。

3.2.2 实操案例

        创建用户表，id 作为主键并设置自增长，自动生成唯一 ID：

-- 创建表，id主键+自增长 create table tt21( id int unsigned primary key auto_increment comment '唯一ID，自增长', name varchar(10) not null default '' comment '姓名' ); -- 插入数据：不指定id，自动生成 insert into tt21(name) values('张三'); insert into tt21(name) values('李四'); -- 查询结果：id=1（张三）、id=2（李四） select * from tt21; -- 获取上次插入的自增长值 select last_insert_id(); -- 结果为2 

        自增长是开发中最常用的主键生成方式，比如订单号、用户 ID、商品 ID，都可以通过自增长自动生成，简单高效且保证唯一。

四、业务唯一约束：唯一键 unique key

        主键虽然能保证唯一性，但一张表只能有一个主键。而实际业务中，很多字段都需要保证唯一性，比如用户的邮箱、身份证号、手机号、商品的编号。这时就需要用到唯一键（unique key），解决多字段的唯一性约束问题。

4.1 唯一键与主键的区别

        很多同学会混淆主键和唯一键，两者的核心区别在于约束严格程度和业务含义：

        关键要点：唯一键的空值不参与唯一性比较，也就是说，一个唯一键字段可以插入多个null值。

4.2 实操案例

        创建学生表，学号id设置为唯一键（允许为空，不能重复），满足 “学号唯一，未分配学号的学生可以为空” 的业务需求：

-- 创建学生表，id为唯一键 create table student ( id char(10) unique comment '学号，唯一可空', name varchar(10) comment '姓名' ); -- 正常插入：id唯一 insert into student(id, name) values('01', '张三'); -- 插入失败：id重复，唯一键约束生效 insert into student(id, name) values('01', '李四'); -- 正常插入：id为空，唯一键允许空值 insert into student(id, name) values(null, '王五'); -- 再次插入：id为空，多个空值允许 insert into student(id, name) values(null, '赵六'); -- 查询结果：两个null值的记录都存在 select * from student; 

4.3 主键的设计建议

        开发中推荐将主键设计为与业务无关的字段（比如自增长的 id），而将业务上需要唯一的字段设置为唯一键。比如员工表中，用自增长的id作为主键，将身份证号、员工工号设置为唯一键。

        这样做的好处是：当业务调整时，不会影响主键。比如员工工号规则改变，只需修改唯一键的字段值，而主键保持不变，避免因主键修改导致的关联数据异常。

五、表间关联约束：外键 foreign key

        前面的约束都是针对单表的，而实际开发中，数据库中的表之间是相互关联的，比如班级表和学生表、商品表和订单表、用户表和购物车表。这时就需要用到外键（foreign key），定义主表和从表之间的关联关系，保证关联数据的合法性。

5.1 外键的核心规则

外键约束定义在从表上，主表必须有主键或唯一键；从表的外键字段值必须在主表的主键 / 唯一键字段中存在，或者为null；主表的主键 / 唯一键字段被从表引用后，删除主表数据时需要考虑外键约束（可以设置级联删除 / 级联更新）。

5.2 外键的作用：解决数据冗余与数据不一致

        为什么需要外键？举个例子：如果我们把班级信息（班级 ID、班级名）直接存在学生表中，每个学生都会存储一遍班级名，会造成数据冗余；如果后续修改班级名，需要修改所有学生的班级名字段，容易造成数据不一致。

        而通过外键关联班级表（主表）和学生表（从表），学生表只需要存储班级 ID（外键），班级名只在班级表中存储一次，既解决了数据冗余，又保证了数据一致性 —— 修改班级名时，只需修改班级表的一条数据即可。

5.3 实操案例：班级表与学生表的外键关联

步骤 1：创建主表（班级表）

        主表需要有主键或唯一键，这里以班级 IDid作为主键。

-- 主表：班级表 create table myclass ( id int primary key comment '班级ID', name varchar(30) not null comment '班级名' ); 

步骤 2：创建从表（学生表），设置外键

        从表的class_id字段作为外键，引用主表myclass的id字段。

-- 从表：学生表，设置外键 create table stu ( id int primary key comment '学生ID', name varchar(30) not null comment '学生名', class_id int comment '班级ID，外键', -- 外键约束：class_id引用myclass的id foreign key (class_id) references myclass(id) ); 

步骤 3：测试外键约束

-- 先插入主表数据：添加班级 insert into myclass values(10, 'C++大牛班'),(20, 'Java大神班'); -- 正常插入从表数据：class_id在主表中存在 insert into stu values(100, '张三', 10),(101, '李四',20); -- 插入失败：class_id=30在主表中不存在，外键约束生效 insert into stu values(102, '王五',30); -- 报错：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails -- 正常插入：class_id为null（未分配班级的学生） insert into stu values(102, '王五', null); 

        外键的本质是把表之间的关联关系交给 MySQL 审核，提前告诉 MySQL “从表的某个字段必须依赖主表的某个字段”，当插入不符合关联关系的数据时，MySQL 直接拒绝，从源头保证关联数据的合法性。

六、综合实战：设计电商购物数据库表

        学到这里，我们已经掌握了所有常用的 MySQL 表约束，接下来通过一个电商购物的综合案例，将所有约束整合使用，设计出规范、合理的数据库表。

6.1 需求分析

        设计一个商店的购物数据库，包含三张表：

商品表（goods）：存储商品信息，包含商品编号、商品名、单价、商品类别、供应商；客户表（customer）：存储客户信息，包含客户编号、姓名、住址、邮箱、性别、身份证号；购买表（purchase）：存储客户的购买记录，包含订单号、客户编号、商品编号、购买数量。

6.2 约束要求

每张表设置主键，且主键为自增长；客户姓名不能为空，性别只能是 “男” 或 “女”；客户的邮箱和身份证号必须唯一；商品名、单价、供应商不能为空，单价默认 0；购买数量默认 0；购买表的客户编号引用客户表的主键，商品编号引用商品表的主键（外键关联）。

6.3 完整 SQL 代码

-- 1. 创建数据库，设置字符集为utf8 create database if not exists bit32mall default character set utf8 ; -- 2. 选择数据库 use bit32mall; -- 3. 创建商品表（goods） 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 '单价（单位：分），非空默认0', category varchar(12) comment '商品分类', provider varchar(64) not null comment '供应商名称，非空' ); -- 4. 创建客户表（customer） 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 '身份证号，唯一' ); -- 5. 创建购买表（purchase），外键关联客户表和商品表 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 '购买数量，默认0', -- 外键：客户编号引用客户表的主键 foreign key (customer_id) references customer(customer_id), -- 外键：商品编号引用商品表的主键 foreign key (goods_id) references goods(goods_id) ); 

总结

        MySQL 表的约束是保证数据完整性、一致性、合法性的核心手段，不同的约束有不同的适用场景。

        掌握了 MySQL 表的约束，就能从源头过滤脏数据，让你的数据库更规范、更健壮，为后续的开发和维护打下坚实的基础！