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

1. 前言

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

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

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

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

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

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

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

FOREIGN KEY (user_id) REFERENCES imooc_user(id)

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

但是也正是因?yàn)檫@個(gè)特性，強(qiáng)外鍵不夠靈活，舉個(gè)例子來(lái)說(shuō)，當(dāng)你刪除某張表的數(shù)據(jù)時(shí)，如果另一張表有此表的外鍵，那么刪除可能會(huì)被拒絕，當(dāng)然你可以通過(guò)級(jí)聯(lián)來(lái)同時(shí)刪除另一張表中關(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)建成功后，我們通過(guò) Delete 來(lái)刪除用戶(hù) pedro：

DELETE FROM imooc_user WHERE id = 1; 

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

(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ù)庫(kù)告訴我們 id 是 imooc_user_score 表的外鍵，如果刪除會(huì)破壞數(shù)據(jù)的完整性，因此拒絕了我們的操作。

我們改造一下外鍵約束，讓它支持級(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;

我們?cè)俅蝿h除 pedro：

DELETE FROM imooc_user WHERE id = 1; 

這次刪除成功，且 imooc_user_score 中的關(guān)聯(lián)數(shù)據(jù)也被刪除了。

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

2.2 什么是弱外鍵

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

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

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 語(yǔ)句中，我們并未聲明 user_id 是外鍵，但是在邏輯層面上我們認(rèn)為它就是外鍵，在連接的時(shí)候知道其對(duì)應(yīng)關(guān)系就行了。

3. 中間表

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

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

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

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

4. 實(shí)踐

接下來(lái)，我們以實(shí)戰(zhàn)的角度來(lái)看 imooc_user（用戶(hù)表）和 imooc_class（課程表）以及關(guān)系表 imooc_user_class。

首先，我們新增幾條用戶(hù)和課程記錄：

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,'回首又見(jiàn)Java','你驀然回首時(shí)，我依然在燈火闌珊處'),
(3,'倚Python屠蟲(chóng)記', '看我這把Python大刀斬盡你無(wú)數(shù)爬蟲(chóng)');

4.1 使用弱外鍵

接著，我們來(lái)模擬用戶(hù)購(gòu)買(mǎi)課程。

某一天，pedro 購(gòu)買(mǎi)了 SQL知多少和回首又見(jiàn)Java 這兩門(mén)課，有了中間表，我們無(wú)需改動(dòng)主表，而是添加記錄至中間表即可：

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

第二天，mary 購(gòu)買(mǎi)了 SQL知多少和倚Python屠蟲(chóng)記兩門(mén)課：

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

現(xiàn)在，管理員需要查看數(shù)據(jù)。首先，他想知道誰(shuí)都購(gòu)買(mǎi)了課程，由于購(gòu)買(mǎi)記錄都記載在了 imooc_user_class 表中，我們只需要查詢(xún)它即可（一個(gè)人可能購(gòu)買(mǎi)多門(mén)課程，所以需要 Distinct 去重）：

SELECT DISTINCT user_id FROM imooc_user_class;

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

光有 user_id 可不行，我們需要知道用戶(hù)名，于是連接一下 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 都購(gòu)買(mǎi)了課程，這與上述一致。

管理員還想知道，哪些課程被購(gòu)買(mǎi)了：

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        | 回首又見(jiàn)Java    |
| 3        | 倚Python屠蟲(chóng)記  |
+----------+----------------+

不錯(cuò)，三門(mén)課都被購(gòu)買(mǎi)了。管理員更想知道 SQL知多少這門(mén)課被誰(shuí)購(gòu)買(mǎi)了：

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     |
+---------+----------+

不錯(cuò)，大家都買(mǎi)了這門(mén)課?。

4.2 弱外鍵總結(jié)

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

5. 小結(jié)

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