實戰(zhàn) 4：如何使用中間表

1. 前言

在外鍵一節(jié)中，我們介紹了外鍵的基本使用，并在末尾中給出了下面這句話：

外鍵是體現(xiàn)數(shù)據(jù)表關(guān)系的核心功能點，但主流的外鍵方式卻都是弱外鍵。

不知道你是否會有些許疑惑，弱外鍵是什么？強(qiáng)外鍵又是什么？它與本節(jié)的中間表又有什么關(guān)系？

帶著這些疑惑，我們一起來開始本小節(jié)的學(xué)習(xí)。

2. 弱外鍵與強(qiáng)外鍵

2.1 強(qiáng)外鍵的缺點

在外鍵一節(jié)中，我們介紹到外鍵可以通過如下的方式來創(chuàng)建：

FOREIGN KEY (user_id) REFERENCES imooc_user(id)

通過聲明方式，數(shù)據(jù)庫會自主將兩張表做外鍵關(guān)聯(lián)，我們把這樣的外鍵稱為強(qiáng)外鍵。強(qiáng)外鍵最大的特點就是數(shù)據(jù)庫層面支持，數(shù)據(jù)庫會自動維護(hù)外鍵關(guān)聯(lián)的表。

但是也正是因為這個特性，強(qiáng)外鍵不夠靈活，舉個例子來說，當(dāng)你刪除某張表的數(shù)據(jù)時，如果另一張表有此表的外鍵，那么刪除可能會被拒絕，當(dāng)然你可以通過級聯(lián)來同時刪除另一張表中關(guān)聯(lián)的數(shù)據(jù)。如下，我們新建兩張存在外鍵關(guān)聯(lián)的表：

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);

創(chuàng)建成功后，我們通過 Delete 來刪除用戶 pedro：

DELETE FROM imooc_user WHERE id = 1; 

數(shù)據(jù)庫提示我們刪除失敗，并給出了如下錯誤信息：

(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`))')

數(shù)據(jù)庫告訴我們 id 是 imooc_user_score 表的外鍵，如果刪除會破壞數(shù)據(jù)的完整性，因此拒絕了我們的操作。

我們改造一下外鍵約束，讓它支持級聯(lián)刪除：

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 中的關(guān)聯(lián)數(shù)據(jù)也被刪除了。

強(qiáng)外鍵雖然能夠保證數(shù)據(jù)的完整性（要么都有，要么都沒有），但是弊端很明顯，刪除了一些數(shù)據(jù)后，與之關(guān)聯(lián)的數(shù)據(jù)也都被刪除了，不利于數(shù)據(jù)的維護(hù)，也不利于更改和遷移；再者，強(qiáng)外鍵會因為關(guān)聯(lián)來同步檢測和更新兩張表，無疑會拉低數(shù)據(jù)庫整體的性能。因此目前大家普遍采用弱外鍵的方式。

2.2 什么是弱外鍵

在 join 一節(jié)中，我們提到外鍵的最終落腳點是使用 Join 來連接數(shù)據(jù)，不過 SQL 連接并非只支持強(qiáng)外鍵，它其實也支持弱外鍵，甚至無外鍵，只要連接的字段能夠?qū)?yīng)上，連接都是可行的。

那么什么是弱外鍵了？答案其實很簡單，強(qiáng)外鍵是數(shù)據(jù)庫層面上的外鍵，而弱外鍵是邏輯層面的上的外鍵。如下，我們新建兩表：

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 是外鍵，但是在邏輯層面上我們認(rèn)為它就是外鍵，在連接的時候知道其對應(yīng)關(guān)系就行了。

3. 中間表

聊完了外鍵，我們來介紹本節(jié)的重點 —— 中間表。先引入一個場景，有兩張數(shù)據(jù)表，分別是 imooc_user（用戶表）和 imooc_class（課程表），對于用戶來說，他（她）可以購買多門課程，而對于課程來說，它也可以被多個用戶購買。這樣就產(chǎn)生了一個難題，用戶與課程之間是典型的多對多關(guān)系，因此我們需要另一張表（imooc_user_class）來記錄用戶與課程之間的購買關(guān)系。

類似于 imooc_user_class 這樣的關(guān)系表，我們稱之為中間表。對于它們?nèi)?，我們可以這樣設(shè)計（省略諸多字段信息）：

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 表的結(jié)構(gòu)上看，它的主體其實就是一些外鍵的組合。這也是中間表與外鍵的關(guān)系。

它們之間的關(guān)系如下圖所示：

4. 實踐

接下來，我們以實戰(zhàn)的角度來看 imooc_user（用戶表）和 imooc_class（課程表）以及關(guān)系表 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大刀斬盡你無數(shù)爬蟲');

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);

現(xiàn)在，管理員需要查看數(shù)據(jù)。首先，他想知道誰都購買了課程，由于購買記錄都記載在了 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     |
+---------+----------+

我們發(fā)現(xiàn)，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 弱外鍵總結(jié)

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

5. 小結(jié)

• 業(yè)務(wù)開發(fā)中的很大一部分業(yè)務(wù)都是通過中間表來實現(xiàn)的，請務(wù)必熟練掌握和理解它。
• 連接操作是驅(qū)動中間表數(shù)據(jù)的核心操作，如果你還不夠熟悉連接，可以再次閱讀連接小節(jié)，并著手操練一番。