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

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）

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

• 主键与唯一键的区别：

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

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 COMMENT '电子邮箱（唯一）',
    sex ENUM('男','女') NOT NULL COMMENT '性别',
    card_id CHAR(18) UNIQUE 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）：保证表间数据逻辑一致。