数据库 / 实战4:如何使用中间表

实战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`))')

数据库告诉我们idimooc_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     |
+---------+----------+

我们发现,pedromary都购买了课程,这与上述一致。

管理员还想知道,哪些课程被购买了:

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. 小结

  • 业务开发中的很大一部分业务都是通过中间表来实现的,请务必熟练掌握和理解它。
  • 连接操作是驱动中间表数据的核心操作,如果你还不够熟悉连接,可以再次阅读连接小节,并着手操练一番。