跳到主要内容
极客日志极客日志面向AI+效率的开发者社区
首页博客我的书AI学习GitHub 精选镜像AI工具UI配色美学关于
搜索内容 / 工具 / 仓库 / 镜像...⌘K搜索
注册
博客列表
SQL

MySQL 表约束核心指南：从基础到外键实战

MySQL 表约束是保障数据完整性与准确性的关键机制。系统讲解了空属性、默认值、主键、自增长、唯一键及外键等核心约束的用法与区别。通过班级表、学生表及电商订单表的实战案例，演示了如何设计健壮的数据库结构。重点分析了外键级联规则、主键选择策略及高并发场景下的约束取舍，帮助开发者减少程序层校验逻辑，提升数据可靠性。

片刻发布于 2026/3/27更新于 2026/10/2588 浏览
MySQL 表约束核心指南：从基础到外键实战

MySQL 表约束核心指南

在 MySQL 数据库设计中，数据类型定义了字段的存储格式，而表约束则从业务逻辑层面保证数据的合法性和完整性。没有约束的表可能出现空值、重复数据、逻辑冲突等问题（如学生所属班级不存在），而合理使用约束能让数据库'自我校验'，减少程序中的数据校验逻辑。

一、表约束核心概念

表约束是对表中字段的规则限制，用于保证数据的准确性、唯一性和关联性。MySQL 支持的核心约束包括：

  • 空属性约束（NULL/NOT NULL）：限制字段是否允许为空；
  • 默认值约束（DEFAULT）：字段未赋值时自动使用默认值；
  • 列描述（COMMENT）：字段说明（无校验作用）；
  • 零填充约束（ZEROFILL）：数字类型不足指定宽度时填充 0；
  • 主键约束（PRIMARY KEY）：唯一标识记录，非空且唯一；
  • 自增长约束（AUTO_INCREMENT）：整数字段自动递增；
  • 唯一键约束（UNIQUE KEY）：字段值唯一，允许为空；
  • 外键约束（FOREIGN KEY）：关联两张表，保证数据逻辑一致性。
约束类型描述
空属性约束（NULL/NOT NULL）限制字段是否允许为空值。
默认值约束（DEFAULT）字段未赋值时自动使用默认值。
列描述（COMMENT）用于字段说明，没有校验作用。
零填充约束（ZEROFILL）数字类型不足指定宽度时在前面填充零。
主键约束（PRIMARY KEY）唯一标识表中的每一行记录，字段值非空且唯一。
自增长约束（AUTO_INCREMENT）整数字段在插入新记录时自动递增。
唯一键约束（UNIQUE KEY）保证字段值唯一，但允许为空值（通常只允许一个空值）。
外键约束（FOREIGN KEY）用于关联两张表，保证数据的一致性和完整性。

二、基础约束：NULL/NOT NULL 与 DEFAULT

基础约束主要控制字段的空值和默认值，是表设计的基础要求。

2.1 空属性约束（NULL/NOT NULL）

  • NULL：默认值，字段允许为空（空值无法参与运算，如 1+NULL=NULL）；
  • NOT NULL：字段不允许为空，插入 / 更新时必须赋值。

实战案例： 创建班级表，要求班级名和教室不能为空。

-- 创建表（班级名和教室非空）
CREATE TABLE myclass (
    class_name VARCHAR(20) NOT NULL,
    class_room VARCHAR(10) NOT NULL
);


 myclass  (, );


 myclass(class_name)  ();
-- 插入合法数据（成功）
INSERT INTO
VALUES
'class1'
'301'
-- 插入非法数据（未给 class_room 赋值，报错）
INSERT INTO
VALUES
'class2'
-- 报错：ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

2.2 默认值约束（DEFAULT）

当字段经常性出现某个固定值时，可设置 DEFAULT，插入时省略该字段则自动使用默认值。

实战案例： 创建用户表，年龄默认 0，性别默认'男'。

CREATE TABLE tt10 (
    name VARCHAR(20) NOT NULL, -- 非空（必须赋值）
    age TINYINT UNSIGNED DEFAULT 0, -- 默认 0
    sex CHAR(2) DEFAULT '男' -- 默认男
);

-- 插入时仅赋值 name，age 和 sex 使用默认值
INSERT INTO tt10(name) VALUES ('zhangsan');

-- 查询结果
SELECT * FROM tt10;
-- +----------+-----+-----+
-- | name     | age | sex |
-- +----------+-----+-----+
-- | zhangsan | 0   | 男  |
-- +----------+-----+-----+

注意：NOT NULL 和 DEFAULT 一般不同时使用（DEFAULT 已隐含保证字段非空）。

约束效果示意

2.3 列描述（COMMENT）

COMMENT 用于描述字段含义，无实际校验作用，仅方便开发者和 DBA 理解表结构，需通过 SHOW CREATE TABLE 查看。

CREATE TABLE tt12 (
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
    sex CHAR(2) DEFAULT '男' COMMENT '性别'
);

-- desc 无法查看注释
DESC tt12;

-- 通过 SHOW CREATE TABLE 查看注释
SHOW CREATE TABLE tt12\G
-- 结果：
-- `name` varchar(20) NOT NULL COMMENT '姓名',
-- `age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',
-- `sex` char(2) DEFAULT '男' COMMENT '性别'

2.4 零填充约束（ZEROFILL）

仅对数字类型有效，当字段值的宽度小于设定宽度时，自动在左侧填充 0（仅格式化显示，实际存储值不变）。

实战案例：

-- 创建表，a 字段设置 zerofill
CREATE TABLE tt3 (
    a INT(5) UNSIGNED ZEROFILL, -- 宽度 5，零填充
    b INT(10) UNSIGNED
);

-- 插入数据
INSERT INTO tt3 VALUES (1, 2);

-- 查询结果（a 字段填充为 00001）
SELECT * FROM tt3;
-- +-------+-----+
-- | a     | b   |
-- +-------+-----+
-- | 00001 | 2   |
-- +-------+-----+

-- 验证实际存储值（仍为 1）
SELECT a, HEX(a) FROM tt3;
-- +-------+-------+
-- | a     | HEX(a)|
-- +-------+-------+
-- | 00001 | 1     |
-- +-------+-------+
  • 关键：无 ZEROFILL 时，数字类型后的宽度（如 INT(10)）毫无意义，仅用于显示格式化。

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

主键、自增长、唯一键是保证数据唯一性的核心约束，解决'重复数据'和'逻辑主键'问题。

3.1 主键约束（PRIMARY KEY）

主键是表的'唯一标识'，核心特性：

  • 非空（NOT NULL）且唯一（UNIQUE）；
  • 一张表最多只能有一个主键；
  • 通常用于标识唯一记录（如用户 ID、学号）。

实战案例 1：单字段主键

-- 创建表时指定主键
CREATE TABLE tt13 (
    id INT UNSIGNED PRIMARY KEY COMMENT '学号（主键）',
    name VARCHAR(20) NOT NULL
);

-- 插入合法数据（成功）
INSERT INTO tt13 VALUES (1, 'aaa');

-- 插入重复主键（报错）
INSERT INTO tt13 VALUES (1, 'bbb');
-- 报错：ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

实战案例 2：复合主键（多字段联合主键） 当单个字段无法唯一标识记录时，可使用复合主键（多字段联合唯一）：

-- 学生成绩表：id（学号）+ course（课程代码）为复合主键
CREATE TABLE tt14 (
    id INT UNSIGNED,
    course CHAR(10) COMMENT '课程代码',
    score TINYINT UNSIGNED DEFAULT 60 COMMENT '成绩',
    PRIMARY KEY (id, course) -- 复合主键
);

-- 插入合法数据（成功）
INSERT INTO tt14(id, course) VALUES (1, '123');

-- 插入重复复合主键（报错）
INSERT INTO tt14(id, course) VALUES (1, '123');
-- 报错：ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY'

主键的添加与删除

-- 给已有表添加主键
ALTER TABLE tt13 ADD PRIMARY KEY (id);

-- 删除主键（注意：若主键关联自增长，需先取消自增长）
ALTER TABLE tt13 DROP PRIMARY KEY;

3.2 自增长约束（AUTO_INCREMENT）

自增长字段会 自动从当前最大值 + 1 生成新值，核心特性：

  • 必须是整数类型；
  • 必须是索引（KEY 一栏有值，通常与主键搭配）；
  • 一张表最多只能有一个自增长。

实战案例：

-- 主键 + 自增长（逻辑主键）
CREATE TABLE tt21 (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL DEFAULT ''
);

-- 插入时省略 id，自动递增
INSERT INTO tt21(name) VALUES ('a');
INSERT INTO tt21(name) VALUES ('b');

-- 查询结果
SELECT * FROM tt21;
-- +----+------+
-- | id | name |
-- +----+------+
-- | 1  | a    |
-- | 2  | b    |
-- +----+------+

-- 获取上次插入的自增长值
SELECT LAST_INSERT_ID();
-- 结果：1（批量插入返回第一个值）

自增长机制

3.3 唯一键约束（UNIQUE KEY）

唯一键用于保证字段值唯一，但允许为空（空值不参与唯一性比较），解决'一张表多个唯一字段'的需求（主键仅能有一个）。

  • 主键与唯一键的区别：
特性主键（PRIMARY KEY）唯一键（UNIQUE KEY）
唯一性是是
非空性是否（允许空值）
一张表数量最多 1 个多个
核心作用标识唯一记录保证业务字段不重复

实战案例： 创建学生表，学号唯一（允许为空）：

CREATE TABLE student (
    id CHAR(10) UNIQUE COMMENT '学号（唯一，可空）',
    name VARCHAR(10)
);

-- 插入合法数据（成功）
INSERT INTO student(id, name) VALUES ('01', 'aaa');
INSERT INTO student(id, name) VALUES (NULL, 'bbb'); -- 空值允许

-- 插入重复唯一键（报错）
INSERT INTO student(id, name) VALUES ('01', 'ccc');
-- 报错：ERROR 1062 (23000): Duplicate entry '01' for key 'id'

四、关联约束：外键（FOREIGN KEY）

外键用于定义主表和从表的关联关系，保证数据的逻辑一致性（如学生的班级必须存在于班级表中）。

4.1 外键核心规则

  • 外键定义在从表上，主表必须有主键或唯一键；
  • 从表外键字段的值必须在主表对应字段中存在，或为 NULL；
  • 主表删除 / 修改关联记录时，需处理从表关联数据（如级联删除、拒绝操作）。

外键关联图

4.2 实战案例

创建班级表（主表）和学生表（从表），学生表的 class_id 关联班级表的 id：

-- 1. 创建主表（班级表）
CREATE TABLE myclass (
    id INT PRIMARY KEY, -- 主键
    name VARCHAR(30) NOT NULL COMMENT '班级名'
);

-- 2. 创建从表（学生表），添加外键
CREATE TABLE stu (
    id INT PRIMARY KEY,
    name VARCHAR(30) NOT NULL COMMENT '学生名',
    class_id INT, -- 外键：class_id 关联 myclass 的 id
    FOREIGN KEY (class_id) REFERENCES myclass(id)
);

-- 3. 插入主表数据
INSERT INTO myclass VALUES (10, 'C++大牛班'), (20, 'Java大神班');

-- 4. 插入合法从表数据（class_id 在主表存在）
INSERT INTO stu VALUES (100, '张三', 10), (101, '李四', 20);

-- 5. 插入非法从表数据（class_id=30 在主表不存在，报错）
INSERT INTO stu VALUES (102, '王五', 30);
-- 报错：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

-- 6. 插入 class_id=NULL（未分配班级，成功）
INSERT INTO stu VALUES (102, '赵六', NULL);

4.3 外键的意义

外键的核心价值是'让数据库自动校验数据关联性'，避免出现逻辑冲突（如不存在的班级、不存在的用户订单）。若不设置外键，需在程序中手动校验，增加开发成本且易出错。

外键校验

五、综合实战：设计电商订单表

结合所有约束，设计电商系统的商品表、客户表、购买表，满足以下需求：

  • 商品表：商品编号自增主键，名称非空，单价默认 0；
  • 客户表：客户编号自增主键，姓名非空，邮箱唯一，性别枚举（男/女），身份证唯一；
  • 购买表：订单号自增主键，关联客户编号和商品编号（外键），购买数量默认 0。
-- 创建数据库
CREATE DATABASE IF NOT EXISTS bit32mall DEFAULT CHARACTER SET utf8;
USE bit32mall;

-- 1. 商品表（主表）
CREATE TABLE IF NOT EXISTS goods (
    goods_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品编号',
    goods_name VARCHAR(32) NOT NULL COMMENT '商品名称',
    unitprice FLOAT NOT NULL DEFAULT 0.1 COMMENT '单价（单位：分）',
    category VARCHAR(12) COMMENT '商品分类',
    provider VARCHAR(64) NOT NULL COMMENT '供应商名称'
);

-- 2. 客户表（主表）
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 '身份证（唯一）'
);

-- 3. 购买表（从表，关联客户表和商品表）
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 1 COMMENT '购买数量',
    -- 外键关联
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);

六、约束选型避坑指南和总结

  • 优先使用非空约束：尽量让字段 NOT NULL，空值会导致查询条件（如 WHERE age=0）失效，且无法参与运算；
  • 主键选择逻辑 ID：主键建议用与业务无关的自增整数（如 id），避免用身份证、手机号等业务字段（需频繁修改）；
  • 唯一键保证业务唯一性：如邮箱、身份证等业务字段，用 UNIQUE KEY 约束，而非主键；
  • 外键谨慎使用：外键会降低表的插入 / 更新性能，高并发场景可去掉外键，在程序中校验关联性；
  • 自增长字段注意重置：删除表中数据后，自增长值不会自动重置，需用 ALTER TABLE 表名 AUTO_INCREMENT=1 手动重置。

总结： MySQL 表约束是数据完整性的核心保障，从基础的空值 / 默认值约束，到核心的主键 / 唯一键约束，再到关联的外键约束，各自解决不同场景的问题：

  • 基础约束（NULL/DEFAULT/COMMENT/ZEROFILL）：控制字段基础属性；
  • 核心约束（PRIMARY KEY/AUTO_INCREMENT/UNIQUE KEY）：保证数据唯一性；
  • 关联约束（FOREIGN KEY）：保证表间数据逻辑一致。

合理组合约束能让数据库表结构更健壮，减少程序中的数据校验代码，同时提升数据可靠性。如果需要针对具体场景（如用户表、订单表）优化约束设计，欢迎交流。后续会持续更新 MySQL 索引、查询优化等进阶内容，带你从入门到精通数据库设计。

目录

  1. MySQL 表约束核心指南
  2. 一、表约束核心概念
  3. 二、基础约束：NULL/NOT NULL 与 DEFAULT
  4. 2.1 空属性约束（NULL/NOT NULL）
  5. 2.2 默认值约束（DEFAULT）
  6. 2.3 列描述（COMMENT）
  7. 2.4 零填充约束（ZEROFILL）
  8. 三、核心约束：主键、自增长与唯一键
  9. 3.1 主键约束（PRIMARY KEY）
  10. 3.2 自增长约束（AUTO_INCREMENT）
  11. 3.3 唯一键约束（UNIQUE KEY）
  12. 四、关联约束：外键（FOREIGN KEY）
  13. 4.1 外键核心规则
  14. 4.2 实战案例
  15. 4.3 外键的意义
  16. 五、综合实战：设计电商订单表
  17. 六、约束选型避坑指南和总结

更多推荐文章

查看全部
  • Java 入门：运算符与逻辑控制
  • Python 缓存过期机制指南：TTL 设置与 LRU 淘汰策略
  • Lostlife2.0 整合 LLama-Factory 优化 NPC 对话逻辑
  • Ubuntu 24.04 GPU 服务器测试系统盘制作指南
  • PX4+ROS 无人机 Offboard 控制：模式解析与实战
  • AI 赋能 PET-CT：诊断效率、成像质量与精准医疗
  • 基于 SpringBoot 的艺术展览网站设计与实现
  • Windows 系统 CMD 输入 python 命令跳转微软商店的解决方案
  • Oracle 基础查询语句实战示例
  • Stack-Chan 机器人开发指南：基于 M5Stack 与 JavaScript 的互动构建
  • 基于无人机遥感的植被覆盖度测量实践
  • MCP 协议详解：与 Function Call 的区别及 Python 使用指南
  • Java 8 基础知识整理：运算符、控制流与面向对象
  • ROS 机器人开发入门：第一天 Linux 终端命令与基础操作
  • Python 月相可视化系统：天文计算与 Web 界面实现
  • 国产大模型公司出门问问港股上市，连续两年实现盈利
  • 利用 QQ 私聊构建全自动化服务器运维助手
  • 如何降低 AIGC 检测率：15 个提示词优化文本自然度
  • VSCode 通过 SSH 远程连接 Ubuntu 配置指南
  • Whisper-large-v3 语音识别模型缓存加速：HuggingFace Hub 离线加载最佳实践

相关免费在线工具

  • SQL 美化和格式化

    在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online

  • SQL转CSV/JSON/XML

    解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL转CSV/JSON/XML在线工具，online

  • CSV 工具包

    CSV 与 JSON/XML/HTML/TSV/SQL 等互转，单页多 Tab。 在线工具，CSV 工具包在线工具，online

  • Base64 字符串编码/解码

    将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online

  • Base64 文件转换器

    将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online

  • Markdown转HTML

    将 Markdown（GFM）转为 HTML 片段，浏览器内 marked 解析；与 HTML转Markdown 互为补充。 在线工具，Markdown转HTML在线工具，online