前言
在 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,上手简单,效果显著。
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,代表该字段禁止为空。
在业务中,某些字段会经常性出现固定值,比如性别默认是'男'、年龄默认是 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 列显示了字段的默认值,直观清晰。
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 属性,这在团队协作中尤为重要——别人接手你的数据库时,能通过注释快速理解表结构。
很多初学者会对 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) 能自动生成唯一的标识值,是开发中最常用的组合约束。
主键是 MySQL 中 最严格的约束,它的核心规则:
主键字段的值 不能重复、不能为空;一张表 最多只能有一个主键;主键字段通常设置为 整数类型(查询效率更高);可以由单个字段作为主键,也可以由多个字段组成 复合主键。
主键的作用是 唯一标识表的每一行,让数据库能快速定位到某一条数据,同时保证数据的唯一性。
创建表时,直接在字段后指定 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,代表该字段是主键。
如果表创建后没有设置主键,后续可以通过 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 约束不会消失,需要单独修改。
某些场景下,单个字段无法唯一标识数据,需要 多个字段组合 才能唯一标识,这就是复合主键。比如学生的成绩表,学号 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'),这些组合都是唯一的。
自增长 auto_increment 是 配合主键使用的辅助约束,它的作用是:当主键字段插入数据时 不给值,MySQL 会自动从当前字段的最大值 + 1,生成一个唯一的新值,避免手动设置主键时出现重复。
自增长字段 必须是索引(Key 列有值,主键本身就是索引,所以完美适配);自增长字段 必须是整数类型;一张表 最多只能有一个自增长字段;批量插入数据时,last_insert_id() 获取的是 第一个自增长值。
创建用户表,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),解决多字段的唯一性约束问题。
很多同学会混淆主键和唯一键,两者的核心区别在于 约束严格程度 和 业务含义:
| 特性 | 主键(primary key) | 唯一键(unique key) |
|---|---|---|
| 空值允许 | 不允许为空 | 允许为空(多个空值) |
| 数量限制 | 一张表只能一个 | 一张表可以多个 |
| 业务含义 | 标识数据的唯一性 | 保证业务字段不重复 |
| 索引类型 | 主键索引 | 唯一索引 |
关键要点:唯一键的空值不参与唯一性比较,也就是说,一个唯一键字段可以插入多个 null 值。
创建学生表,学号 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;
开发中推荐将 主键设计为与业务无关的字段(比如自增长的 id),而将业务上需要唯一的字段设置为唯一键。比如员工表中,用自增长的 id 作为主键,将身份证号、员工工号设置为唯一键。
这样做的好处是:当业务调整时,不会影响主键。比如员工工号规则改变,只需修改唯一键的字段值,而主键保持不变,避免因主键修改导致的关联数据异常。
前面的约束都是针对 单表 的,而实际开发中,数据库中的表之间是 相互关联 的,比如班级表和学生表、商品表和订单表、用户表和购物车表。这时就需要用到 外键(foreign key),定义主表和从表之间的关联关系,保证关联数据的合法性。
外键约束 定义在从表上,主表必须有 主键 或 唯一键;从表的外键字段值 必须在主表的主键 / 唯一键字段中存在,或者为 null;主表的主键 / 唯一键字段被从表引用后,删除主表数据时需要考虑外键约束(可以设置级联删除 / 级联更新)。
为什么需要外键?举个例子:如果我们把班级信息(班级 ID、班级名)直接存在学生表中,每个学生都会存储一遍班级名,会造成 数据冗余;如果后续修改班级名,需要修改所有学生的班级名字段,容易造成 数据不一致。
而通过外键关联班级表(主表)和学生表(从表),学生表只需要存储班级 ID(外键),班级名只在班级表中存储一次,既解决了数据冗余,又保证了数据一致性——修改班级名时,只需修改班级表的一条数据即可。
主表需要有主键或唯一键,这里以班级 ID id 作为主键。
-- 主表:班级表 create table myclass ( id int primary key comment '班级 ID', name varchar(30) not null comment '班级名' );
从表的 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) );
-- 先插入主表数据:添加班级 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 表约束,接下来通过一个 电商购物 的综合案例,将所有约束整合使用,设计出规范、合理的数据库表。
设计一个商店的购物数据库,包含三张表:
商品表(goods):存储商品信息,包含商品编号、商品名、单价、商品类别、供应商;客户表(customer):存储客户信息,包含客户编号、姓名、住址、邮箱、性别、身份证号;购买表(purchase):存储客户的购买记录,包含订单号、客户编号、商品编号、购买数量。
每张表设置主键,且主键为自增长;客户姓名不能为空,性别只能是'男'或'女';客户的邮箱和身份证号必须唯一;商品名、单价、供应商不能为空,单价默认 0;购买数量默认 0;购买表的客户编号引用客户表的主键,商品编号引用商品表的主键(外键关联)。
-- 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 表的约束,就能从源头过滤脏数据,让你的数据库更规范、更健壮,为后续的开发和维护打下坚实的基础!

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL 转 CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML 转 Markdown 互为补充。 在线工具,Markdown 转 HTML在线工具,online