实战4：如何使用中间表

优质

小牛编辑

134浏览

2023-12-01

1. 前言

在外键一节中，我们介绍了外键的基本使用，并在末尾中给出了下面这句话：

外键是体现数据表关系的核心功能点，但主流的外键方式却都是弱外键。

不知道你是否会有些许疑惑，弱外键是什么？强外键又是什么？它与本节的中间表又有什么关系？

带着这些疑惑，我们一起来开始本小节的学习。

2. 弱外键与强外键

2.1 强外键的缺点

在外键一节中，我们介绍到外键可以通过如下的方式来创建：

FOREIGN KEY (user_id) REFERENCES imooc_user(id)

通过声明方式，数据库会自主将两张表做外键关联，我们把这样的外键称为强外键。强外键最大的特点就是数据库层面支持，数据库会自动维护外键关联的表。

但是也正是因为这个特性，强外键不够灵活，举个例子来说，当你删除某张表的数据时，如果另一张表有此表的外键，那么删除可能会被拒绝，当然你可以通过级联来同时删除另一张表中关联的数据。如下，我们新建两张存在外键关联的表：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
DROP TABLE IF EXISTS imooc_user_score;
CREATE TABLE imooc_user_score
(
  id int PRIMARY KEY,
  user_id int NOT NULL,
  score int,
  FOREIGN KEY (user_id) REFERENCES imooc_user(id)
);
INSERT INTO imooc_user(id,username,age) VALUES (NULL,'pedro',23);
INSERT INTO imooc_user_score(id,user_id,score) VALUES (NULL,1,9);

创建成功后，我们通过 Delete 来删除用户pedro：

DELETE FROM imooc_user WHERE id = 1;

数据库提示我们删除失败，并给出了如下错误信息：

(1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`imooc`.`imooc_user_score`, CONSTRAINT `imooc_user_score_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `imooc_user` (`id`))')

数据库告诉我们id是imooc_user_score表的外键，如果删除会破坏数据的完整性，因此拒绝了我们的操作。

我们改造一下外键约束，让它支持级联删除：

ALTER TABLE imooc_user_score DROP FOREIGN KEY imooc_user_score_ibfk_1;
ALTER TABLE imooc_user_score ADD CONSTRAINT imooc_user_score_ibfk_1 FOREIGN KEY(user_id) REFERENCES imooc_user(id) ON DELETE CASCADE;

我们再次删除pedro：

DELETE FROM imooc_user WHERE id = 1;

这次删除成功，且imooc_user_score中的关联数据也被删除了。

强外键虽然能够保证数据的完整性（要么都有，要么都没有），但是弊端很明显，删除了一些数据后，与之关联的数据也都被删除了，不利于数据的维护，也不利于更改和迁移；再者，强外键会因为关联来同步检测和更新两张表，无疑会拉低数据库整体的性能。因此目前大家普遍采用弱外键的方式。

2.2 什么是弱外键

在 join 一节中，我们提到外键的最终落脚点是使用 Join 来连接数据，不过 SQL 连接并非只支持强外键，它其实也支持弱外键，甚至无外键，只要连接的字段能够对应上，连接都是可行的。

那么什么是弱外键了？答案其实很简单，强外键是数据库层面上的外键，而弱外键是逻辑层面的上的外键。如下，我们新建两表：

CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
CREATE TABLE imooc_user_score
(
  id int PRIMARY KEY,
  user_id int NOT NULL,
  score int,
);

在新建 imooc_user_score 表的 SQL 语句中，我们并未声明user_id是外键，但是在逻辑层面上我们认为它就是外键，在连接的时候知道其对应关系就行了。

3. 中间表

聊完了外键，我们来介绍本节的重点——中间表。先引入一个场景，有两张数据表，分别是imooc_user（用户表）和imooc_class（课程表），对于用户来说，他（她）可以购买多门课程，而对于课程来说，它也可以被多个用户购买。这样就产生了一个难题，用户与课程之间是典型的多对多关系，因此我们需要另一张表（imooc_user_class）来记录用户与课程之间的购买关系。

类似于imooc_user_class这样的关系表，我们称之为中间表。对于它们三者，我们可以这样设计（省略诸多字段信息）：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
DROP TABLE IF EXISTS imooc_class;
CREATE TABLE imooc_class
(
  id int PRIMARY KEY,
  name varchar(50),
  description varchar(100)
);
DROP TABLE IF EXISTS imooc_user_class;
CREATE TABLE imooc_user_class
(
  id int PRIMARY KEY,
  user_id int NOT NULL,
  class_id int NOT NULL
);

从imooc_user_class表的结构上看，它的主体其实就是一些外键的组合。这也是中间表与外键的关系。

它们之间的关系如下图所示：
图片描述

4. 实践

接下来，我们以实战的角度来看imooc_user（用户表）和imooc_class（课程表）以及关系表imooc_user_class。

首先，我们新增几条用户和课程记录：

INSERT INTO imooc_user(id,username,age) VALUES (NULL,'pedro',23),(2,'tom',19),(3,'mary',22);
INSERT INTO imooc_class(id,name,description) VALUES 
(1,'SQL知多少', '一卷囊括天下SQL事'),
(2,'回首又见Java','你蓦然回首时，我依然在灯火阑珊处'),
(3,'倚Python屠虫记', '看我这把Python大刀斩尽你无数爬虫');

4.1 使用弱外键

接着，我们来模拟用户购买课程。

某一天，pedro购买了SQL知多少和回首又见Java这两门课，有了中间表，我们无需改动主表，而是添加记录至中间表即可：

INSERT INTO imooc_user_class VALUES(1,1,1), (2,1,2);

第二天，mary购买了SQL知多少和倚Python屠虫记两门课：

INSERT INTO imooc_user_class VALUES(3,3,1), (4,3,3);

现在，管理员需要查看数据。首先，他想知道谁都购买了课程，由于购买记录都记载在了imooc_user_class表中，我们只需要查询它即可（一个人可能购买多门课程，所以需要 Distinct 去重）：

SELECT DISTINCT user_id FROM imooc_user_class;

+---------+
| user_id |
+---------+
| 1       |
| 3       |
+---------+

光有user_id可不行，我们需要知道用户名，于是连接一下imooc_user即可：

SELECT DISTINCT user_id,username FROM imooc_user_class LEFT JOIN imooc_user ON imooc_user_class.user_id = imooc_user.id;

+---------+----------+
| user_id | username |
+---------+----------+
| 1       | pedro    |
| 3       | mary     |
+---------+----------+

我们发现，pedro和mary都购买了课程，这与上述一致。

管理员还想知道，哪些课程被购买了：

SELECT DISTINCT class_id,name FROM imooc_user_class LEFT JOIN imooc_class ON imooc_user_class.class_id = imooc_class.id;

+----------+----------------+
| class_id | name           |
+----------+----------------+
| 1        | SQL知多少       |
| 2        | 回首又见Java    |
| 3        | 倚Python屠虫记  |
+----------+----------------+

不错，三门课都被购买了。管理员更想知道SQL知多少这门课被谁购买了：

SELECT DISTINCT user_id,username FROM imooc_user_class LEFT JOIN imooc_user ON imooc_user_class.user_id = imooc_user.id WHERE imooc_user_class.class_id = 1;

+---------+----------+
| user_id | username |
+---------+----------+
| 1       | pedro    |
| 3       | mary     |
+---------+----------+

不错，大家都买了这门课