9.2 一对多关系

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

一、什么是一对多关系？

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

典型的例子：

• 一个班级有多个学生，每个学生只属于一个班级。

• 一个作者可以写多篇文章，每篇文章只有一个作者。

• 一个商品分类下可以有多个商品，每个商品只属于一个分类。

一对多关系的应用场景

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

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

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

步骤解析：

1. 设计“一”方表：包含主键，通常是自增ID。

2. 设计“多”方表：包含一个外键列，类型与“一”方主键一致，并添加外键约束，指向“一”方的主键。

示例：班级与学生

假设我们有两个实体：班级（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）。

• 如果不允许删除被引用的父记录，用 RESTRICT。

四、注意事项

1. 外键列的类型必须与父表主键完全一致（包括长度、符号、是否无符号）。例如父表主键是 INT UNSIGNED，子表外键也必须是 INT UNSIGNED。

2. 索引：外键列会自动创建索引，但如果你需要频繁基于外键查询，可以显式创建索引以优化性能（外键本身已创建，一般无需额外创建）。

3. NULL 值的处理：如果外键列允许为 NULL，则表示该子记录不关联任何父记录。这代表“零或一”的可选关系，在业务上可能合理（如未分配班级的学生）。

4. 存储引擎：只有 InnoDB 支持外键约束，MyISAM 不支持（可以定义但不会生效）。

5. 性能影响：外键约束会带来额外的检查开销，但对大多数应用来说可以接受。在极高并发的写入场景，可能需要权衡是否在应用层维护数据一致性。

6. 命名规范：为外键起一个有意义的名字，便于管理和错误排查，如 fk_students_class。

五、最佳实践

• 尽量为每个一对多关系建立外键约束，以保持数据的完整性。

• 选择适当的级联操作，避免意外删除重要数据。

• 设计时考虑外键是否允许 NULL，反映业务上的可选性。

• 在查询时合理使用 JOIN，理解 INNER JOIN、LEFT JOIN 的区别。

• 为经常用于查询的外键列建立索引（虽然外键本身已建索引，但如果有组合查询需求，可考虑复合索引）。

六、实战练习

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

• 作者表（authors）：author_id（主键）、name、email

• 文章表（posts）：post_id（主键）、title、content、publish_date、author_id（外键）

要求：

1. 写出建表语句，外键设为 ON DELETE CASCADE（删除作者时同时删除其文章）。

2. 插入若干测试数据。

3. 编写查询：显示所有文章及其作者姓名。

4. 编写查询：统计每个作者的文章数量。

参考答案：

-- 创建作者表
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 查询来获取关联数据。

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