實戰 4：如何使用中間表

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 (1,'pedro',23);
INSERT INTO imooc_user_score(id,user_id,score) VALUES (1,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 (1,'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     |
+---------+----------+

不錯，大家都買了這門課?。

4.2 弱外鍵總結

可以看到，中間表的存在讓數據的查詢變得更為方便和有效了。當然你也可以選擇不要中間表，而在兩張主表中各自添加對方外鍵的方式來達到同樣的效果，不過這樣的方式顯然不推薦。

5. 小結

• 業務開發中的很大一部分業務都是通過中間表來實現的，請務必熟練掌握和理解它。
• 連接操作是驅動中間表數據的核心操作，如果你還不夠熟悉連接，可以再次閱讀連接小節，并著手操練一番。