MySQL多表连接探究笔记

引言：在项目中遇到了需要合并MySQL数据库表的需求，合并的过程中要跳过已有的记录，因为表的设计初期没有设置主键，最后还是通过MySQL的not exists语句实现了需求，顺便对MySQL里的多表连接进行了一下探索，顺手记录如下。

注：仅为个人备忘查询笔记，参考请慎重……

1. 创建测试用例

创建玩家表（player）和工会表（guild），并往里面插入数据。因为不考察和索引相关的问题，我们不设置主键和索引。

use test;
drop table if exists guild;
create table guild (id int, guild_name varchar(20) );
insert into guild (id, guild_name) values (1001,"g1"),(1002,"g2"),(1004,"g4");
drop table if exists player;
create table player (id int, player_name varchar(20), guild_id int);
insert into player (id, player_name, guild_id) values (1000001,"p1",1001),(1000002,"p2",1001),(1000003,"p3",1002),(1000004,"p4",1003);

表数据如下：

mysql> select * from player;
+---------+-------------+----------+
| id      | player_name | guild_id |
+---------+-------------+----------+
| 1000001 | p1          |     1001 |
| 1000002 | p2          |     1001 |
| 1000003 | p3          |     1002 |
| 1000004 | p4          |     1003 |
+---------+-------------+----------+
4 rows in set (0.00 sec)

mysql> select * from guild;
+------+------------+
| id   | guild_name |
+------+------------+
| 1001 | g1         |
| 1002 | g2         |
| 1004 | g4         |
+------+------------+
3 rows in set (0.00 sec)

工会表我们故意错开了1003，以备下面查询用。

2. MySQL多表查询的多种形式：

表连接，使用join关键字：inner join, left join, right join。

子查询，在where后跟查询条件：带in, any, exists, not exists的where子句。

3. 考察join（Block Nest Join和hash join）

如果我们需要查询玩家所在的工会名称，会用到表连接，将player表和guild表连接起来：

select player.id, player.player_name, guild.id, guild.guild_name 
from player 
left join guild on guild.id=player.guild_id;

当前MySQL版本为8.0.23 MySQL，我们使用explain查看一下执行计划：

mysql> explain select player.id, player.player_name, guild.id, guild.guild_name from player left join guild on guild.id=player.guild_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | player | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                       |
|  1 | SIMPLE      | guild  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------