mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

9.3 多对多关系

在现实世界中，许多关系并不是简单的“一个对应多个”，而是“多个对应多个”。例如：

一个学生可以选多门课程，一门课程也可以被多个学生选修。
一个作者可以写多本书，一本书也可以有多个作者（合著）。
一个商品可以出现在多个订单中，一个订单也包含多个商品。

这种双方都是“多”的关系，就是多对多关系（N:N）。在关系型数据库中，我们不能直接用两个表的外键来表示多对多，因为那样会造成数据的冗余和歧义。正确的做法是引入一个中间表（也称为关联表、连接表），将多对多关系拆分成两个一对多关系。

一、什么是多对多关系？

多对多关系是指：表A中的一条记录，可以与表B中的多条记录相关联；反过来，表B中的一条记录，也可以与表A中的多条记录相关联。

这种关系无法像一对多那样简单地在外键上体现，因为无论把外键放在哪一方，都会导致重复存储和逻辑混乱。

经典示例：学生与课程

学生表（students）：存储学生信息。
课程表（courses）：存储课程信息。
一个学生可以选择多门课程 → 学生对课程是“多”。
一门课程也可以被多个学生选修 → 课程对学生也是“多”。

如果我们在学生表中加一个外键指向课程，那么一个学生只能选一门课；如果在课程表中加外键指向学生，那么一门课只能有一个学生。这都不符合实际。所以必须引入第三张表——选课表（student_courses）。

二、如何实现多对多关系？

实现多对多关系的标准方法是：创建一张中间表，它包含两个外键，分别引用两个主表的主键。这两个外键的组合通常作为中间表的联合主键（或者再单独加一个自增主键）。

步骤解析：

创建主表：定义两个实体表，各自有主键。
创建中间表：至少包含两个字段，分别作为外键引用两个主表的主键。
添加联合唯一约束（或联合主键）：确保两个外键的组合值是唯一的，避免重复关联。
添加外键约束：维护数据的参照完整性。
可选：中间表可以包含额外的属性，如选课时间、成绩等。

示例：学生选课系统

第一步：创建学生表和课程表

-- 学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL,
    class VARCHAR(20)
);

-- 课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    credit INT
);

第二步：创建中间表（选课表）

CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    semester VARCHAR(20),       -- 选修学期（额外属性）
    grade DECIMAL(4,2),          -- 成绩（额外属性）
    PRIMARY KEY (student_id, course_id),   -- 联合主键，避免重复选课
    FOREIGN KEY (student_id) REFERENCES students(student_id)
        ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
        ON DELETE CASCADE
);

说明：

PRIMARY KEY (student_id, course_id) 确保一个学生不能重复选择同一门课。
两个外键都设置了 ON DELETE CASCADE，意味着删除学生或课程时，相关的选课记录也会自动删除（避免孤立数据）。

第三步：插入测试数据

-- 插入学生
INSERT INTO students (student_name, class) VALUES
('张三', '计算机1班'),
('李四', '计算机2班'),
('王五', '软件1班');

-- 插入课程
INSERT INTO courses (course_name, credit) VALUES
('数据库原理', 3),
('数据结构', 4),
('操作系统', 3);

-- 学生选课
INSERT INTO student_courses (student_id, course_id, semester, grade) VALUES
(1, 1, '2024春', 85.5),   -- 张三选数据库
(1, 2, '2024春', 90.0),   -- 张三选数据结构
(2, 1, '2024春', 78.0),   -- 李四选数据库
(2, 3, '2024秋', 88.0),   -- 李四选操作系统
(3, 2, '2024春', 92.0);   -- 王五选数据结构

三、查询多对多关系

多对多关系的查询通常需要两次 JOIN：从一张主表到中间表，再到另一张主表。

1. 查询某个学生所选的全部课程

SELECT s.student_name, c.course_name, sc.semester, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE s.student_id = 1;

2. 查询某门课程的所有选课学生

SELECT c.course_name, s.student_name, sc.grade
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
JOIN students s ON sc.student_id = s.student_id
WHERE c.course_id = 1;

3. 统计每门课程的选课人数

SELECT c.course_name, COUNT(sc.student_id) AS student_count
FROM courses c
LEFT JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id;

4. 找出同时选了“数据库”和“数据结构”的学生

SELECT s.student_name
FROM students s
JOIN student_courses sc1 ON s.student_id = sc1.student_id AND sc1.course_id = 1
JOIN student_courses sc2 ON s.student_id = sc2.student_id AND sc2.course_id = 2;

四、中间表的设计模式

中间表通常有以下两种设计模式：

模式

特点

适用场景

联合主键模式

中间表使用两个外键作为联合主键，天然避免重复。

纯粹的关联表，无需额外的自增主键。

独立主键模式

中间表增加一个自增主键，同时为两个外键建立联合唯一约束。

中间表需要被其他表引用（例如，选课记录可能再有其他关联）。

示例：独立主键模式

CREATE TABLE student_courses (
    id INT PRIMARY KEY AUTO_INCREMENT,   -- 独立自增主键
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    semester VARCHAR(20),
    grade DECIMAL(4,2),
    UNIQUE KEY uk_student_course (student_id, course_id),  -- 联合唯一约束
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

这种方式更灵活，但会多占用一些空间。

五、注意事项

避免重复关联：务必在中间表上建立联合唯一约束或联合主键，防止出现同一条关联记录多次。
外键约束：强烈建议添加外键约束，保证数据的完整性。如果性能要求极高，可以考虑不加外键（但仍需在应用层保证一致性），但新手请务必加上。
索引：中间表的外键列应该建立索引（MySQL 会自动为外键列创建索引，但如果用联合主键，两个列都在主键中，已包含索引）。
命名规范：中间表名通常由两个表名组合而成，如 student_courses、author_books，使用单数或复数均可，但保持一致。
额外属性：多对多关系往往带有额外属性（如成绩、选课时间、数量等），这些属性应放在中间表中，而不是主表。
删除策略：根据业务需求设置 ON DELETE 和 ON UPDATE。通常选课记录会随着学生或课程的删除而级联删除，比较合理。

六、实战练习：商品与订单

电商系统中，订单与商品就是典型的多对多关系。一个订单可以包含多个商品，一个商品也可以出现在多个订单中。中间表通常叫做 order_items（订单明细表），包含订单ID、商品ID、数量、单价等。

请自行设计：

订单表（orders）：订单ID、订单日期、客户ID等。
商品表（products）：商品ID、商品名、价格。
订单明细表（order_items）：订单ID、商品ID、数量、单价。

要求写出建表语句，并插入测试数据，然后查询某个订单的所有商品及总价。

参考思路：

-- 订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    customer_name VARCHAR(100)
);

-- 商品表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200),
    price DECIMAL(10,2)
);

-- 订单明细表
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,  -- 当时成交价，可能不同于现价
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

查询订单总价：

SELECT o.order_id, o.customer_name, 
       SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id;

七、总结

多对多关系必须通过中间表来实现，中间表包含两个外键，分别指向两个主表。
中间表的两个外键通常组成联合主键或加上联合唯一约束，以避免重复关联。
多对多查询需要两次 JOIN：主表 → 中间表 → 另一主表。
中间表可以携带额外的属性，如数量、时间、成绩等。
合理使用外键约束和索引，保证数据完整性和查询性能。

掌握了多对多关系，你就能够处理数据库中绝大部分关联场景。