MySQL第五讲·关于外键和连接, 如何做到关联查询？

外键和连接：如何做关联查询？

在实际的数据库应用开发过程中，我们经常需要把2个或2个以上的表进行关联，以获取需要的数据。这是因为，为了提高存取效率，我们会把不同业务模块的信息分别存放在不同的表里面。

但是，从业务层面上看，我们需要完整全面的信息为经营决策提供数据支撑。

还是以超市项目为例，数据库里面的销售流水表一般只保存销售必需的信息，比如商品编号，数量， 价格，金额和会员卡号等。但是，在呈现给超市经营者的统计报表中，只包括这些信息是不够的，比如商品编号，会员卡号，这些数字经营者就看不懂。

因此，必须要从商品信息表中提取出商品信息，从会员表中提取出会员信息，这样才能形成一个完整的报表。这种把分散在多个不同的表里的数据查询出来的操作，就是多表查询。

不过，多表查询可不简单，我们需要建立起多个表之间的关联，然后才能去查询，同时还需要规避关联表查询中的常见错误。具体该怎么做呢，请接着看：

超市项目中的进货模块，有两个这样的数据表，分别是进货单头表（importhead）和进货单明细表(importdetailes)，我们每天都需要对这两张表进行CRUD操作。

进货单头表记录的是整个进货单的总体信息：

进货单明细表记录的是每次进货的商品明细。一条进货单头数据记录，对应多条进货商品的明细数据，也就是所谓的一对多的关系，具体如下表所示：

现在我们需要查询一次进货的所有相关数据，包括进货单的总体信息和进货商品的明细，这样一来，我们就需要把2个表关联起来，该如何进行操作呢？

在MySQL中，为了把2个表关联起来，会用到2个重要功能，分别是外键（foreign key）和连接（join）。

外键需要在创建表的阶段就定义，连接可以通过相同意义的字段把2个表连接起来，用在查询阶段。

如何创建外键？

首先我们来了解一下什么是外键？

假如我们有2个表，分别是表A和表B，它们通过一个公共字段id发生关联关系，我们把这个关联关系叫做R。如果id在表A中是主键，那么表A就是这个关系R中的主表。相应的，表B就是这个关系中的从表，表B中的id，就是表B用来引用表A中数据的，叫外键。

所以，外键就是从表中用来引用主表中数据的那个公共字段。

为了方便理解，请看下图：

在MySQL中，外键是通过外键约束来定义的。外键约束就是约束的一种，它必须在从表中定义，包括指明哪个是外键字段，以及外键字段所引用的主表中的主键字段是什么。

MySQL系统会根据外键约束的定义，监控对主表中数据的删除操作。如果发现要删除的主表记录，正在被从表中某条记录的外键字段所引用，MySQL就会提示错误，从而确保了关联数据不会缺失。

外键约束可以在创建表的时候定义，也可以通过修改表来定义。语法结构如下：

[CONSTRAINT <外键约束名称>] FOREIGN KEY 字段名
REFERENCES <主表名> 字段名
1
2

我们可以在创建表的时候定义外键约束：

CREATE TABLE 从表名
(
  字段名 类型,
  ...
-- 定义外键约束，指出外键字段和参照的主表字段
CONSTRAINT 外键约束名
FOREIGN KEY (字段名) REFERENCES 主表名 (字段名)
)
1
2
3
4
5
6
7
8

当然，我们也可以通过修改表来定义外键约束：

ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY 字段名 REFERENCES 主表名 （字段名）;
1

一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计，比如添加新的字段，增加新的关联关系，但是没有预先定义外键约束，那么，就要用修改表的方式来补充定义。

下面，我们来看看怎么创建外键约束：

先创建主表demo.importhead:

create table demo.importhead
(
	listnumber int primary key,
  	supplierid int,
  	stocknumber int, 
  	importtype int,
  	importquantity decimal(10, 3),
  	importvalue decimal(10, 2),
  	recoder int,
  	recodingdate datetime
);
1
2
3
4
5
6
7
8
9
10
11

然后创建从表demo.importdetails，并且给它定义外键约束：

create table demo.importdetails
(
	listnumber int,
  	itemnumber int,
  	quantity decimal(10,3),
  	importprice decimal(10,2),
  	importvalue decimal(10,2),
  	-- 定义外键约束，指出外键字段和参照的主表字段
  	constraint fk_importdetails_importhead
  	foreign key (listnumber) references importhead(listnumber)
);
1
2
3
4
5
6
7
8
9
10
11

运行这个SQL语句，我们就在创建表的同时定义了一个名字叫"fk_importdetails_importhead"的外键约束。同时，我们声明，这个外键约束的字段"listnumber"引用的是表importhead里面的字段"listnumber"

我们可以通过MySQL自带的、用于存储系统信息的数据库：information_schema来查看外键约束的相关信息：

mysql> SELECT
    ->     constraint_name, -- 表示外键约束名称
    ->     table_name, -- 表示外键约束所属数据表的名称
    ->     column_name, -- 表示外键约束的字段名称
    ->     referenced_table_name, -- 表示外键约束所参照的数据表名称
    ->     referenced_column_name -- 表示外键约束所参照的字段名称
    -> FROM
    ->     information_schema.KEY_COLUMN_USAGE
    -> WHERE
    ->     constraint_name = 'fk_importdetails_importhead';
+-----------------------------+---------------+-------------+-----------------------+------------------------+
| CONSTRAINT_NAME             | TABLE_NAME    | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-----------------------------+---------------+-------------+-----------------------+------------------------+
| fk_importdetails_importhead | importdetails | listnumber  | importhead            | listnumber             |
+-----------------------------+---------------+-------------+-----------------------+------------------------+
1 row in set (0.05 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

通过查询，我们可以看到，外键约束所在的表是“importdetails”，外键字段是“listnumber”，参照的主表是“importhead”，参照的主表字段是“listnumber”。这样，通过定义外键约束，我们已经建立起了 2 个表之间的关联关系。

关联关系建立起来之后，如何才能获取我们需要的数据呢？这时，我们就需要用到连接查询了。

连接

在MySQL中，有2种类型的连接，分别是内连接（inner join）和外连接（outer join）。

• 内连接表示查询结果只返回符合连接条件的记录，这种连接方式比较常用；
• 外连接则不同，表示查询结果返回某一个表中的所有记录，以及另一个表中满足连接条件的记录。

下面我们来认识一下内连接：

在MySQL中，关键字join，inner join，cross join的含义是一样的，都表示内连接。我们通过join把两个表关联起来，来查询两个表中的数据。

超市的项目中有会员销售的需求，所以我们的流水表中的数据记录，既包括非会员的普通销售，又包括会员销售。它们的区别是会员销售的数据记录包括会员编号，而在非会员销售的数据记录中，会员编号为空。

下面是销售表demo.trans，实际的销售表比较复杂，为了方便理解，对表进行了简化，并且假设业务字段cardno是会员信息表的主键，简化以后的结构如下：

再看一下简化后的会员信息表：

这两个表之间存在关联关系，表demo.trans中的字段cardno是这个关联关系中的外键。

我们可以通过内连接，查询所有会员销售的流水记录：

select 
	a.transactionno,
	a.itemnumber,
	a.quantity,
	a.price,
	a.transdate,
	b.membername
from 
	demo.trans as a
join 
	demo.membermaster as b 
on (a.cardno = b.cardno);
1
2
3
4
5
6
7
8
9
10
11
12

我们通过公共字段cardno把两个表关联到一起，查询出了会员消费的数据。

在这里，关键字join和关键字on配对使用，意思是查询满足关联条件“demo.trans”表中cardno的值与demo.membermaster表中的cardio值相等的两个表中的所有记录。

上述内容讲解的是内连接，下面我们再说说外连接。

跟内连接只返回符合连接条件的数据记录不同的是，外连接还可以返回表中的所有记录，它包括两类，分别是左连接和右连接。

• 左连接：一般简写成left join，返回左表中的所有数据记录，以及右表中符合连接条件的记录；
• 右连接：一般简写成right join，返回右表中的所有数据记录，以及左表中符合连接条件的记录。

当我们需要查询全部流水信息的时候就会用到外连接，代码如下：

select 
	a.transactionno,
	a.itemnumber,
	a.quantity,
	a.price,
	a.transdate,
	b.membername
from demo.trans as a
left join demo.membermaster as b -- left join以demo.trans为主
on (a.cardno = b.cardno);
1
2
3
4
5
6
7
8
9
10

可以看到，我用到了 LEFT JOIN，意思是以表 demo.trans 中的数据记录为主，这个表中的数据记录要全部出现在结果集中，同时给出符合连接条件（a.cardno=b.cardno) 的表 demo.membermaster 中的字段 membername 的值。

我们也可以使用 RIGHT JOIN 实现同样的效果，代码如下：

select 
	a.transactionno.
	a.itemnumber,
	a.quantity,
	a.price,
	a.transdate,
	a.membername
from 
	demo.membermaster as b
right join 
	demo.joins as a -- right join, 顺序颠倒了，还是以demo.trans为主
on (a.cardno = b.cardno);
1
2
3
4
5
6
7
8
9
10
11
12

运行之后查看结果：

mysql> SELECT
    ->     a.transactionno,
    ->     a.itemnumber,
    ->     a.quantity,
    ->     a.price,
    ->     a.transdate,
    ->     b.membername
    -> FROM
    ->     demo.trans AS a
    ->         LEFT JOIN   -- 左连接
    ->     demo.membermaster AS b ON (a.cardno = b.cardno);
+---------------+------------+----------+-------+---------------------+------------+
| transactionno | itemnumber | quantity | price | transdate           | membername |
+---------------+------------+----------+-------+---------------------+------------+
|             1 |          1 |    1.000 | 89.00 | 2020-12-01 00:00:00 | 张三       |
|             2 |          2 |    1.000 | 12.00 | 2020-12-02 00:00:00 | NULL       |
+---------------+------------+----------+-------+---------------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT
    ->     a.transactionno,
    ->     a.itemnumber,
    ->     a.quantity,
    ->     a.price,
    ->     a.transdate,
    ->     b.membername
    -> FROM
    ->     demo.membermaster AS b
    ->         RIGHT JOIN   -- 右连接
    ->     demo.trans AS a
    ->     ON (a.cardno = b.cardno);
+---------------+------------+----------+-------+---------------------+------------+
| transactionno | itemnumber | quantity | price | transdate           | membername |
+---------------+------------+----------+-------+---------------------+------------+
|             1 |          1 |    1.000 | 89.00 | 2020-12-01 00:00:00 | 张三       |
|             2 |          2 |    1.000 | 12.00 | 2020-12-02 00:00:00 | NULL       |
+---------------+------------+----------+-------+---------------------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

关联查询中的误区

有了连接，我们就可以进行两个表的关联查询了。有个问题：

关联查询必须在外键约束的基础上，才可以吗？

其实，在MySQL中，外键约束不是关联查询的必要条件。很多人往往在设计表的时候，觉得只要连接查询就可以搞定一切了，外键约束太麻烦，没有必要，这样想的话，就进入了一个误区。

还是以超市进货的例子为例，假设一次进货数据是这样的：供货商编号是1，进货仓库编号是1。我们进货的商品编号是1234，进货数量是1，进货价格是10，进货金额是10.

insert into demo.importhead
(
listnumber,
supplierid,
stocknumber,
)
values
(
1234,
1,
1
);
1
2
3
4
5
6
7
8
9
10
11
12

运行SQL，查看表中内容：

mysql> SELECT *
    -> FROM demo.importhead;
+------------+------------+-------------+------------+----------+-------------+-------------+
| listnumber | supplierid | stocknumber | importtype | quantity | importprice | importvalue |
+------------+------------+-------------+------------+----------+-------------+-------------+
|       1234 |          1 |           1 |          1 |     NULL |        NULL |        NULL |
+------------+------------+-------------+------------+----------+-------------+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

可以看到，我们有了一个进货单头，单号是 1234，供货商是 1 号供货商，进货仓库是 1 号仓库。

接着，我们向进货单明细表中插入进货明细数据：

insert into demo.importdetails
(
listnumber,
itemnumber,
quantity,
importprice,
importvalue
)
values
(
1234,
1,
1,
10,
10
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

运行SQL，查看表中记录：

mysql> SELECT *
    -> FROM demo.importdetails;
+------------+------------+----------+-------------+-------------+
| listnumber | itemnumber | quantity | importprice | importvalue |
+------------+------------+----------+-------------+-------------+
|       1234 |          1 |    1.000 |       10.00 |       10.00 |
+------------+------------+----------+-------------+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

这样，我们就有了 1234 号进货单的明细数据：进货商品是 1 号商品，进货数量是 1 个，进货价格是 10 元，进货金额是 10 元。

这个时候，如果我删除进货单头表的数据，就会出现只有明细、没有单头的数据缺失情况。我们来看看会发生什么：

delete from demo.importhead
where listnumbere = 1234;
1
2

运行这条SQL语句，MySQL会提示错误，因为数据删除违反了外键约束。MySQL阻止了数据不一致的情况出现。

还有一个问题是，我插入数据的顺序，为什么我要先插入进货单头表的数据，再插入进货单明细表的数据呢？如果我先插入数据到从表（进货单明细表），会导致MySQL找不到参照的主表信息，会提示错误，因为添加数据违反了外键约束。

所以，虽然我们不用外键约束，也可以进行关联查询，但是有了它，MySQL系统才会保护我们的数据，避免出现误删的情况，从而提高系统整体的可靠性。

外键约束，可以帮助我们确定从表中的外键字段与主表中的主键字段之间的引用关系，还可以确保从表中数据所引用的主表数据不会被删除，从而保证了 2 个表中数据的一致性。

为什么在 MySQL 里，没有外键约束也可以进行关联查询呢？

原因是外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以，MySQL 允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

所以我们要尽量养成在关联表中定义外键约束的习惯。不过，如果业务场景因为高并发等原因，无法承担外键约束的成本，也可以不定义外键约束，但是一定要在应用层面实现外键约束的逻辑功能，这样才能确保系统的正确可靠。