您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站

实战4:如何使用中间表

1. 前言

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

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

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

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

2. 弱外键与强外键

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

FOREIGN KEY (user_id) REFERENCES imooc_user(id)

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

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

DROP TABLE IF EXISTS imooc_user;CREATE TABLE imooc_user(
  id int PRIMARY KEY,
  username varchar(),
  age int);DROP TABLE IF EXISTS imooc_user_;CREATE TABLE imooc_user_(
  id int PRIMARY KEY,
  user_id int NOT NULL,
   int,
  FOREIGN KEY (user_id) REFERENCES imooc_user(id));INSERT INTO imooc_user(id,username,age) VALUES (,'pedro',);INSERT INTO imooc_user_(id,user_id,) VALUES (,,);

创建成功后,我们通过 Delete 来 pedro

DELETE FROM imooc_user WHERE id = ;

我们失败,并给出了如下信息:

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

告诉我们 idimooc_user_ 表的外键,如果会破坏数据的完整性,因此拒绝了我们的操作。

我们改造一下外键约束,让它级联:

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

我们再次 pedro

DELETE FROM imooc_user WHERE id = ;

这次成功,且 imooc_user_ 中的关联数据也被了。

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

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

那么什么是弱外键了?答案其实很简单,强外键是层面上的外键,而弱外键是逻辑层面的上的外键。如下,我们新建两表:

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

在新建 imooc_user_ 表的 语句中,我们并未声明 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(),
  age int);DROP TABLE IF EXISTS imooc_class;CREATE TABLE imooc_class(
  id int PRIMARY KEY,
  name varchar(),
  description varchar());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 (,'pedro',),(,'tom',),(,'mary',);INSERT INTO imooc_class(id,name,description) VALUES (,'知多少', '一卷囊括天下事'),(,'回首又见Java','你蓦然回首时,我依然在灯火阑珊处'),(,'倚Python屠虫记', '看我这把Python大刀斩尽你无数爬虫');

接着,我们来模拟购买课程。

某一天,pedro 购买了 知多少回首又见Java 这两门课,有了中间表,我们无需改动主表,而是至中间表即可:

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

第二天,mary 购买了 知多少倚Python屠虫记两门课:

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

现在,需要查看数据。首先,他想知道谁都购买了课程,由于购买记录都记载在了 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        | 知多少       |
| 2        | 回首又见Java    |
| 3        | 倚Python屠虫记  |
+----------+----------------+

不错,三门课都被购买了。更想知道 知多少这门课被谁购买了:

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 = ;
+---------+----------+
| user_id | username |
+---------+----------+
| 1       | pedro    |
| 3       | mary     |
+---------+----------+

不错,大家都买了这门课?。

可以看到,中间表的存在让数据的变得更为方便和有效了。当然你也可以选择不要中间表,而在两张主表中各自对方外键的方式来达到同样的,不过这样的方式显然不推荐。

5. 小结

业务开发中的很大一部分业务都是通过中间表来实现的,请务必熟练掌握和理解它。

连接操作是驱动中间表数据的核心操作,如果你还不够熟悉连接,可以再次阅读连接小节,并着手操练一番。


联系我
置顶