MySQL表约束体系全解：从基础语法到实战设计，吃透所有约束类型与核心坑点

前言

在MySQL中，数据类型是对字段存储格式的第一层约束，但仅靠数据类型无法保障数据的业务合法性与完整性。比如用户邮箱必须唯一、用户姓名不能为空、订单所属用户必须在用户表中存在……这些业务层面的规则，都需要通过表的约束来实现。

表约束是MySQL在数据写入层面的最后一道校验关卡，能够从数据库层面强制保证数据的正确性、一致性和完整性。本文将系统讲解MySQL中所有常用约束的语法、原理、实战场景与常见坑点，帮助你从“会写”到“懂原理”，设计出健壮的数据库表结构。

一、约束的概念与分类

1.1 什么是约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据类型、取值范围、唯一性等特性。如果插入/修改的数据违反了约束规则，操作会被直接拒绝，从而保证数据库中数据的正确性和可靠性。

1.2 常用约束类型总览

我们可以把MySQL常用约束分为几大类：

二、非空约束（NOT NULL）

2.1 基本概念

非空约束用于限制字段的值不能为NULL。MySQL默认所有字段都允许为NULL（即默认是NULL属性），但在实际开发中，绝大多数业务字段都应该设置为NOT NULL。

2.2 为什么要尽量避免NULL？

很多新手觉得NULL和空值没区别，这是最大的误区。NULL在MySQL中是一个特殊值，会带来很多问题：

SELECT 1 + NULL; — 结果为NULL
SELECT CONCAT('hello', NULL); — 结果为NULL

— 若name字段有一行NULL
SELECT COUNT(*) FROM user; — 统计总行数，包含NULL行
SELECT COUNT(name) FROM user; — 统计name非空的行数，不包含NULL行

2.3 语法与实战示例

创建表时直接在字段后加 NOT NULL 即可：

— 创建班级表：班级名和教室都不允许为空
CREATE TABLE my_class (
class_name VARCHAR(20) NOT NULL COMMENT '班级名称',
class_room VARCHAR(10) NOT NULL COMMENT '教室编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

如果插入时不给非空字段赋值，会直接报错：

INSERT INTO my_class(class_name) VALUES('计算机1班');
— 报错：ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

2.4 易错点提醒

• ❌ 误区：空字符串 '' 等于NULL
  ✅ 事实：空字符串是一个长度为0的有效字符串，不属于NULL；NOT NULL 约束允许插入空字符串。
• ❌ 误区：所有字段都没必要加NOT NULL
  ✅ 事实：除了明确允许为空的业务字段（比如用户的可选备注信息），其余字段都建议设置NOT NULL，避免NULL带来的各种异常。

三、默认值约束（DEFAULT）

3.1 基本概念

当插入数据时，如果没有给该字段赋值，就会自动使用DEFAULT指定的默认值。默认值约束可以大幅减少插入语句的冗余，同时配合NOT NULL保证字段非空。

3.2 语法与实战示例

CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
name VARCHAR(20) NOT NULL COMMENT '用户名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄，默认0',
sex CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别，默认男',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，默认当前时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入时省略带默认值的字段，自动填充默认值：

— 只插入name字段，age、sex、create_time自动使用默认值
INSERT INTO user_info(name) VALUES('张三');

— 查询验证
SELECT * FROM user_info;

3.3 重点补充：常用默认值场景

• 数值型字段：默认 0，比如状态、数量、年龄
• 字符串字段：默认 '' 空字符串，避免NULL
• 时间字段：DEFAULT CURRENT_TIMESTAMP 记录创建时间，ON UPDATE CURRENT_TIMESTAMP 自动记录更新时间（开发高频使用）update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

3.4 易错点提醒

• ❌ 误区：设置了DEFAULT的字段就不用加NOT NULL了
  ✅ 事实：DEFAULT只在“插入时不赋值”时生效，如果显式插入NULL，依然会写入NULL。因此规范写法是 NOT NULL DEFAULT xxx，既保证默认值，又禁止显式插入NULL。
• ❌ 误区：默认值只能是常量
  ✅ 事实：时间类型可以使用 CURRENT_TIMESTAMP 等函数，但普通字段不能直接用函数生成默认值。

四、列描述注释（COMMENT）

4.1 基本概念

COMMENT是字段的注释说明，不会影响数据存储和约束逻辑，专门用来描述字段的业务含义，方便后续开发和DBA维护表结构。企业级开发规范要求：所有表和所有字段都必须加COMMENT。

4.2 查看注释的两种方式

SHOW CREATE TABLE user_info\\G

执行后会显示完整的建表SQL，包含所有字段的COMMENT。

SELECT
COLUMN_NAME '字段名',
COLUMN_COMMENT '字段注释'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名'
AND TABLE_NAME = 'user_info';

4.3 易错点提醒

• DESC 表名 命令无法查看字段注释，只能通过上面两种方式查看。
• 注释不属于强制约束，但属于开发规范，是表可维护性的关键。

五、零填充约束（ZEROFILL）

5.1 基本概念

ZEROFILL是数值型字段的显示格式约束：当数值的位数小于字段定义的显示宽度时，自动在左侧补0。

注意：ZEROFILL只影响查询显示的格式，不影响实际存储的值，数据库内部存储的依然是原始数值。

5.2 核心原理：int(M)的真正含义

很多新手会疑惑 int(5) 和 int(11) 有什么区别，是不是存储范围不一样？
答案是：没有ZEROFILL时，括号里的数字没有任何意义，int类型的存储范围永远是4字节（-2147483648~2147483647）。
括号里的M叫做“显示宽度”，只有配合ZEROFILL时才会生效，用来指定补零后的总位数。

5.3 实战示例

CREATE TABLE test_zerofill (
a INT(5) ZEROFILL COMMENT '5位宽度零填充',
b INT(10) COMMENT '无零填充'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

— 插入数据
INSERT INTO test_zerofill VALUES(1, 2);

— 查询查看效果
SELECT * FROM test_zerofill;

查询结果：

我们可以用HEX函数验证存储的真实值：

SELECT a, HEX(a) FROM test_zerofill;

结果显示HEX(a)为1，证明内部存储的就是数值1，00001只是显示格式化的效果。

5.4 易错点提醒

六、主键约束（PRIMARY KEY）

6.1 核心概念

主键是表中一行数据的唯一标识，具备两个核心特性：

• 非空：主键字段的值不能为NULL
• 唯一：主键字段的值不能重复

一张表最多只能有一个主键（可以是多个字段组成的复合主键）。
从索引角度看，主键对应的是聚簇索引，数据在磁盘上就是按照主键顺序存储的，因此主键查询效率是最高的。

6.2 单字段主键

创建表时直接在字段后加 PRIMARY KEY 即可：

CREATE TABLE student (
id INT UNSIGNED PRIMARY KEY COMMENT '学号，主键',
name VARCHAR(20) NOT NULL COMMENT '姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入重复主键会直接报错：

INSERT INTO student VALUES(1, '张三');
INSERT INTO student VALUES(1, '李四');
— 报错：ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

6.3 复合主键

由多个字段共同组成一个主键，多个字段组合起来不能重复，单个字段可以重复。多用于多对多中间表、联合主键场景。

语法：在所有字段定义完成后，用 PRIMARY KEY(字段1, 字段2) 指定

— 学生成绩表：学号+课程号作为复合主键
CREATE TABLE student_score (
student_id INT UNSIGNED NOT NULL COMMENT '学号',
course_id CHAR(10) NOT NULL COMMENT '课程号',
score TINYINT UNSIGNED DEFAULT 60 COMMENT '成绩',
PRIMARY KEY(student_id, course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复合主键的规则：两个字段的值完全相同时才算冲突，单个字段重复是允许的。

INSERT INTO student_score VALUES(1, 'C001', 90);
INSERT INTO student_score VALUES(1, 'C002', 85); — 正常，学号重复，课程号不同
INSERT INTO student_score VALUES(2, 'C001', 95); — 正常，课程号重复，学号不同
INSERT INTO student_score VALUES(1, 'C001', 88); — 报错，组合重复

6.4 主键的增删改

表创建完成后，也可以通过ALTER语句追加或删除主键：

— 给无主键的表添加主键
ALTER TABLE student ADD PRIMARY KEY(id);

ALTER TABLE student DROP PRIMARY KEY;

6.5 主键设计最佳实践

企业级开发中，主键设计遵循一个核心原则：使用与业务无关的代理主键，不使用业务字段作为主键

• 比如不要用身份证号、手机号、工号作为主键，因为业务字段可能会随着业务调整而变化，主键修改的成本极高。
• 推荐使用自增INT、BIGINT或者雪花ID作为主键，和业务完全解耦。

6.6 易错点提醒

• ❌ 误区：一张表可以有多个主键
  ✅ 事实：一张表只能有一个主键，但可以是多字段组成的复合主键。
• ❌ 误区：删除主键直接DROP就行
  ✅ 事实：如果主键字段有AUTO_INCREMENT属性，必须先去掉自增长属性，才能删除主键，否则会报错。

七、自增长约束（AUTO_INCREMENT）

7.1 基本概念

自增长约束作用于数值型字段，当插入数据不给该字段赋值时，系统会自动从当前最大值+1生成一个新值。通常和主键搭配使用，作为逻辑主键。

7.2 基本语法

CREATE TABLE user (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID，自增主键',
username VARCHAR(20) NOT NULL COMMENT '用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据时，自增字段可以赋值为NULL或者省略，自动递增：

INSERT INTO user(username) VALUES('user1');
INSERT INTO user(username) VALUES('user2');

SELECT * FROM user;
— id自动变成1、2

7.3 获取最后插入的自增ID

插入数据后，可以通过 LAST_INSERT_ID() 函数获取本次连接最后插入的自增ID：

SELECT LAST_INSERT_ID();

注意：该函数是连接级别的，不同连接之间互不影响；批量插入时，返回的是第一条数据的自增ID。

7.4 深度解析：自增长的特性与坑点

7.5 易错点提醒

• ❌ 误区：自增长一定是从1开始连续递增
  ✅ 事实：自增长只能保证递增，不能保证连续，不要依赖自增ID的连续性做业务逻辑。
• ❌ 误区：自增长字段不能手动赋值
  ✅ 事实：可以手动赋值，只要不重复就行，后续自增会从手动插入的最大值开始+1。

八、唯一键约束（UNIQUE KEY）

8.1 基本概念

唯一键约束保证字段的值在表中不能重复，解决了“一张表只能有一个主键，但有多个字段需要保证唯一性”的问题。比如用户表中，手机号、邮箱、身份证号都需要唯一，这时候就可以用唯一键。

8.2 基本语法

CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
username VARCHAR(20) NOT NULL COMMENT '用户名',
email VARCHAR(64) UNIQUE COMMENT '邮箱，唯一',
phone CHAR(11) UNIQUE KEY COMMENT '手机号，唯一'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入重复值会报错：

INSERT INTO user(username, email) VALUES('张三', 'zhangsan@test.com');
INSERT INTO user(username, email) VALUES('李四', 'zhangsan@test.com');
— 报错：ERROR 1062 (23000): Duplicate entry 'zhangsan@test.com' for key 'email'

8.3 联合唯一键

和复合主键类似，多个字段组合起来保证唯一：

— 用户收藏表：同一个用户不能重复收藏同一个商品
CREATE TABLE user_collect (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
goods_id INT NOT NULL,
UNIQUE KEY uk_user_goods(user_id, goods_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

8.4 核心特性：NULL值的处理

唯一键允许字段值为NULL，并且多个NULL值不会冲突。
因为在MySQL中，NULL和任何值都不相等，包括NULL本身，所以多条数据的唯一键字段为NULL时，不会触发唯一约束冲突。

INSERT INTO user(username, email) VALUES('王五', NULL);
INSERT INTO user(username, email) VALUES('赵六', NULL);
— 执行成功，两个NULL不会冲突

8.5 主键 vs 唯一键 核心区别

8.6 易错点提醒

• ❌ 误区：唯一键不能有NULL值
  ✅ 事实：唯一键允许多个NULL值，这是和主键最核心的区别之一。
• ❌ 误区：UNIQUE和UNIQUE KEY不一样
  ✅ 事实：两者语法完全等价，没有区别。

九、外键约束（FOREIGN KEY）

9.1 基本概念

外键用于建立两张表之间的关联关系，保证从表的外键字段值，必须在主表的主键/唯一键中存在，或者为NULL。从而保证数据引用的一致性，避免脏数据。

• 主表（父表）：被引用的表，必须有主键或唯一键
• 从表（子表）：设置外键的表，外键字段引用主表的主键/唯一键

9.2 基本语法

语法：FOREIGN KEY (从表字段) REFERENCES 主表(主表字段)

示例：班级表（主表）和学生表（从表）

CREATE TABLE class (
id INT PRIMARY KEY COMMENT '班级ID',
class_name VARCHAR(30) NOT NULL COMMENT '班级名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student (
id INT PRIMARY KEY COMMENT '学生ID',
name VARCHAR(30) NOT NULL COMMENT '学生姓名',
class_id INT COMMENT '所属班级ID',
— 外键约束：class_id 引用 class表的id字段
FOREIGN KEY (class_id) REFERENCES class(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

9.3 约束效果验证

INSERT INTO class VALUES(10, 'Java班'), (20, 'C++班');
INSERT INTO student VALUES(1001, '张三', 10), (1002, '李四', 20);

INSERT INTO student VALUES(1003, '王五', 30);
— 报错：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

INSERT INTO student VALUES(1003, '王五', NULL);
— 执行成功

9.4 补充：外键的级联操作

基础外键默认是限制模式：主表的记录被从表引用时，无法直接删除/修改主表记录。通过设置级联行为，可以定义主表数据变更时，从表的处理逻辑：

示例：带级联删除和级联更新的外键

CREATE TABLE student (
id INT PRIMARY KEY COMMENT '学生ID',
name VARCHAR(30) NOT NULL COMMENT '学生姓名',
class_id INT COMMENT '所属班级ID',
FOREIGN KEY (class_id) REFERENCES class(id)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

9.5 深度讨论：物理外键 vs 逻辑外键

虽然外键能保证数据一致性，但在互联网大厂的开发规范中，普遍不推荐使用物理外键（数据库层面的FOREIGN KEY），而是使用逻辑外键（应用层维护关联关系），原因如下：

建议：小项目、内部系统可以用物理外键保证数据安全；高并发、大型系统推荐使用逻辑外键，在应用层保证数据一致性。

9.6 易错点提醒

十、综合实战：电商商城表设计

我们通过一个经典的电商场景，综合运用所有约束，设计三张表：商品表、用户表、订单表。

需求说明

完整建表SQL

— 创建数据库
CREATE DATABASE IF NOT EXISTS shop_mall
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE shop_mall;

— 1. 商品表
CREATE TABLE IF NOT EXISTS goods (
goods_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID，主键',
goods_name VARCHAR(64) NOT NULL COMMENT '商品名称',
unit_price INT NOT NULL DEFAULT 0 COMMENT '单价，单位：分',
category VARCHAR(32) NOT NULL COMMENT '商品分类',
provider VARCHAR(64) NOT NULL COMMENT '供应商名称',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '商品表';

— 2. 用户表
CREATE TABLE IF NOT EXISTS customer (
customer_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID，主键',
username VARCHAR(32) NOT NULL COMMENT '用户名',
email VARCHAR(64) UNIQUE COMMENT '邮箱，唯一',
phone CHAR(11) NOT NULL UNIQUE COMMENT '手机号，唯一非空',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
id_card CHAR(18) UNIQUE COMMENT '身份证号，唯一',
address VARCHAR(256) DEFAULT '' COMMENT '收货地址',
register_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '客户表';

— 3. 订单表
CREATE TABLE IF NOT EXISTS purchase (
order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID，主键',
customer_id INT NOT NULL COMMENT '下单用户ID',
goods_id INT NOT NULL COMMENT '购买商品ID',
nums INT NOT NULL DEFAULT 1 COMMENT '购买数量',
order_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
— 逻辑外键，不创建物理外键约束，应用层维护
INDEX idx_customer_id(customer_id),
INDEX idx_goods_id(goods_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '购买订单表';

设计说明

• 所有表和字段都加了COMMENT，符合企业开发规范
• 核心业务字段都设置了NOT NULL+DEFAULT，避免NULL值带来的异常
• 邮箱、手机号、身份证号用唯一键保证业务唯一性
• 订单表没有创建物理外键，而是加了普通索引提升查询效率，符合互联网开发规范
• 时间字段都设置了默认当前时间，减少插入代码冗余

十一、常见易错点汇总

十二、总结

MySQL的表约束是数据完整性的基石，不同约束有不同的适用场景：

• 基础约束（NOT NULL、DEFAULT、COMMENT）：属于开发规范，建议所有表都合理使用
• 主键+自增长：是绝大多数表的标准配置，优先使用代理主键
• 唯一键：用于业务字段的唯一性校验，注意NULL值特性
• 外键：根据项目场景选择，小型系统可用物理外键，高并发系统推荐逻辑外键

合理设计表约束，能够在数据库层面提前拦截非法数据，大幅降低业务代码的校验成本，同时提升数据的一致性和可维护性。

如果觉得本文对你有帮助，欢迎点赞、收藏、评论交流～🎉