mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

9.2 一对多关系

如果说一对一关系是两个实体之间的“婚姻”，那么一对多关系就是现实世界中最常见的关系——就像“母亲与孩子”、“班级与学生”、“作者与文章”。在这种关系中，“一”方的每一条记录可以对应“多”方的多条记录，而“多”方的每一条记录只能对应“一”方的一条记录。

一、什么是一对多关系？

一对多关系（1:N）是指：表A中的一条记录，在表B中可以有多条记录与之关联；反过来，表B中的一条记录，只能与表A中的一条记录关联。

典型的例子：

一个班级有多个学生，每个学生只属于一个班级。
一个作者可以写多篇文章，每篇文章只有一个作者。
一个商品分类下可以有多个商品，每个商品只属于一个分类。

一对多关系的应用场景

一对多关系几乎出现在任何业务系统中：

场景

说明

主从表结构

主表（一）和明细表（多），如订单与订单明细。

分类与内容

一个分类对应多个商品、文章、帖子等。

用户与行为

一个用户可以有多个订单、多个评论、多个收藏。

层级关系

部门与员工、角色与用户（实际上角色与用户可以是多对多，但也可以简化为一对多，如果每个用户只有一个角色）。

二、如何在MySQL中实现一对多关系

实现一对多关系非常简单：在“多”方的表中添加一个外键列，引用“一”方的主键。

步骤解析：

设计“一”方表：包含主键，通常是自增ID。
设计“多”方表：包含一个外键列，类型与“一”方主键一致，并添加外键约束，指向“一”方的主键。

示例：班级与学生

假设我们有两个实体：班级（classes）和学生（students）。一个班级可以有多个学生，一个学生只能属于一个班级。

建表语句：

-- 1. 创建班级表（一）
CREATE TABLE classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(50) NOT NULL,
    grade INT NOT NULL
);

-- 2. 创建学生表（多）
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL,
    age TINYINT,
    class_id INT,  -- 外键列
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

解释：

students 表中的 class_id 列用于存储该学生所属班级的ID。
通过 FOREIGN KEY 约束，确保 class_id 的值必须在 classes 表的 class_id 列中存在（或为 NULL）。
ON DELETE SET NULL 表示如果某个班级被删除，该班级学生的 class_id 会被设置为 NULL（避免删除班级导致学生记录丢失）。
ON UPDATE CASCADE 表示如果班级ID发生更新，学生表中的 class_id 也会同步更新。

插入数据测试

-- 插入班级
INSERT INTO classes (class_name, grade) VALUES
('计算机1班', 1),
('软件工程2班', 2);

-- 插入学生，关联班级
INSERT INTO students (student_name, age, class_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 22, 2),
('赵六', 20, 2);

-- 插入一个未分配班级的学生（class_id 为 NULL）
INSERT INTO students (student_name, age) VALUES ('钱七', 19);

1. 查询所有学生及其班级信息（使用 INNER JOIN）

SELECT s.student_id, s.student_name, s.age, c.class_name, c.grade
FROM students s
JOIN classes c ON s.class_id = c.class_id;

结果只显示有班级的学生。

2. 查询所有学生，包括未分配班级的（使用 LEFT JOIN）

SELECT s.student_id, s.student_name, s.age, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id;

3. 查询某个班级的所有学生

SELECT * FROM students WHERE class_id = 1;

4. 统计每个班级的学生人数

SELECT c.class_name, COUNT(s.student_id) AS student_count
FROM classes c
LEFT JOIN students s ON c.class_id = s.class_id
GROUP BY c.class_id;

三、外键约束的选项详解

在定义一对多关系时，外键约束的 ON DELETE 和 ON UPDATE 选项非常重要，它们决定了当父表记录被删除或更新时，子表该如何响应。

选项

含义

适用场景

CASCADE

级联：父表删除/更新时，子表中匹配的行也自动删除/更新。

订单与订单明细（删除订单时自动删除明细）。

SET NULL

置空：父表删除/更新时，将子表的外键列设为 NULL。

班级与学生（删除班级后，学生仍保留，但班级ID置空）。

RESTRICT

限制：如果子表有引用，则拒绝父表的删除/更新操作。

默认行为（如果未指定）。适用于不允许删除被引用的父记录的场景。

NO ACTION

与 RESTRICT 类似（MySQL 中同 RESTRICT）。

SET DEFAULT

MySQL 不支持 InnoDB 的 SET DEFAULT（忽略）。

选择建议：

如果子表记录依赖于父表，父表删除时子表也应删除，用 CASCADE。
如果子表记录需要保留，但关联关系不再存在，用 SET NULL（外键列必须允许 NULL）。
如果不允许删除被引用的父记录，用 RESTRICT。

四、注意事项

外键列的类型必须与父表主键完全一致（包括长度、符号、是否无符号）。例如父表主键是 INT UNSIGNED，子表外键也必须是 INT UNSIGNED。
索引：外键列会自动创建索引，但如果你需要频繁基于外键查询，可以显式创建索引以优化性能（外键本身已创建，一般无需额外创建）。
NULL 值的处理：如果外键列允许为 NULL，则表示该子记录不关联任何父记录。这代表“零或一”的可选关系，在业务上可能合理（如未分配班级的学生）。
存储引擎：只有 InnoDB 支持外键约束，MyISAM 不支持（可以定义但不会生效）。
性能影响：外键约束会带来额外的检查开销，但对大多数应用来说可以接受。在极高并发的写入场景，可能需要权衡是否在应用层维护数据一致性。
命名规范：为外键起一个有意义的名字，便于管理和错误排查，如 fk_students_class。

五、最佳实践

尽量为每个一对多关系建立外键约束，以保持数据的完整性。
选择适当的级联操作，避免意外删除重要数据。
设计时考虑外键是否允许 NULL，反映业务上的可选性。
在查询时合理使用 JOIN，理解 INNER JOIN、LEFT JOIN 的区别。
为经常用于查询的外键列建立索引（虽然外键本身已建索引，但如果有组合查询需求，可考虑复合索引）。

六、实战练习

场景：设计一个简单的博客系统

作者表（authors）：author_id（主键）、name、email
文章表（posts）：post_id（主键）、title、content、publish_date、author_id（外键）

要求：

写出建表语句，外键设为 ON DELETE CASCADE（删除作者时同时删除其文章）。
插入若干测试数据。
编写查询：显示所有文章及其作者姓名。
编写查询：统计每个作者的文章数量。

参考答案：

-- 创建作者表
CREATE TABLE authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

-- 创建文章表
CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    author_id INT NOT NULL,
    CONSTRAINT fk_posts_author FOREIGN KEY (author_id) 
        REFERENCES authors(author_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 插入作者
INSERT INTO authors (name, email) VALUES
('张三', 'zhang@example.com'),
('李四', 'li@example.com');

-- 插入文章
INSERT INTO posts (title, content, author_id) VALUES
('MySQL 入门', '内容...', 1),
('数据库设计', '内容...', 1),
('Python 基础', '内容...', 2);

-- 查询文章及其作者
SELECT p.title, p.publish_date, a.name AS author_name
FROM posts p
JOIN authors a ON p.author_id = a.author_id;

-- 统计每个作者的文章数
SELECT a.name, COUNT(p.post_id) AS post_count
FROM authors a
LEFT JOIN posts p ON a.author_id = p.author_id
GROUP BY a.author_id;

总结

一对多关系是数据库设计中最常见的关系，通过“多”方表中的外键实现。
外键约束保证了数据的参照完整性，并可通过 ON DELETE 和 ON UPDATE 定义级联行为。
设计时要考虑外键列是否允许 NULL，以及选择适当的级联策略。
熟练使用 JOIN 查询来获取关联数据。

理解了一对多关系，你就能轻松应对大多数业务场景。