MYSQL LEFT JOIN 的怪异行为

理论 MYSQL的LEFT JOIN 有时候会变的不一样

如果LFET JOIN B WHERE 条件部分有B的条件,那么它跟主表不再是LEFT

JOIN ,而是变成了 INNER JOIN!

我们创建两个父子表

mysql> show create table children;+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table    | Create Table                                                                                                                                                                                                                                                                                                                           |+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| children | CREATE TABLE `children` (  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,  `NAME` varchar(45) DEFAULT NULL,  `SEX` char(1) DEFAULT NULL,  `AGE` tinyint DEFAULT NULL,  `FATHER_ID` bigint DEFAULT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='孩子'   |+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
mysql> show create table FATHER;+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                                                                                                                                                                                                                              |+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| FATHER | CREATE TABLE `FATHER` (  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,  `NAME` varchar(45) DEFAULT NULL,  `MONY` decimal(10,0) DEFAULT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='父亲'   |+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

生成必要的数据

mysql> select * from FATHER;+----+--------+------+| ID | NAME   | MONY |+----+--------+------+|  1 | 王五   | 3000 ||  2 | 张三   | 3500 ||  3 | 李四   | 5000 ||  4 | 刘二   | 8000 ||  5 | 陈七   | 5500 ||  6 | 杨八   | 7500 |+----+--------+------+6 rows in set (0.00 sec)

mysql> select * from children;+----+-----------+------+------+-----------+| ID | NAME      | SEX  | AGE  | FATHER_ID |+----+-----------+------+------+-----------+|  1 | 王八      | 男   |   38 |         1 ||  2 | 王高      | 男   |   28 |         1 ||  3 | 王艳      | 女   |   18 |         1 ||  4 | 张雪      | 女   |   33 |         2 ||  5 | 张亮      | 男   |   25 |         2 ||  6 | 李贵      | 男   |   27 |         3 ||  7 | 李丽      | 女   |   25 |         3 ||  8 | 杨莉      | 女   |   23 |         6 ||  9 | 杨雪      | 女   |   22 |         6 || 10 | 杨七郎    | 男   |   35 |         6 |+----+-----------+------+------+-----------+10 rows in set (0.00 sec)

实验一   没有条件的时候

mysql> SELECT A.NAME,A.MONY,B.NAME,B.AGE,B.SEX    -> FROM books.FATHER A    -> LEFT JOIN books.children B ON A.ID=B.FATHER_ID    -> ORDER BY A.ID ASC;+--------+------+-----------+------+------+| NAME   | MONY | NAME      | AGE  | SEX  |+--------+------+-----------+------+------+| 王五   | 3000 | 王艳      |   18 | 女   || 王五   | 3000 | 王高      |   28 | 男   || 王五   | 3000 | 王八      |   38 | 男   || 张三   | 3500 | 张亮      |   25 | 男   || 张三   | 3500 | 张雪      |   33 | 女   || 李四   | 5000 | 李丽      |   25 | 女   || 李四   | 5000 | 李贵      |   27 | 男   || 刘二   | 8000 | NULL      | NULL | NULL || 陈七   | 5500 | NULL      | NULL | NULL || 杨八   | 7500 | 杨七郎    |   35 | 男   || 杨八   | 7500 | 杨雪      |   22 | 女   || 杨八   | 7500 | 杨莉      |   23 | 女   |+--------+------+-----------+------+------+12 rows in set (0.00 sec)

认为非常正常,没有孩子的要展现出来的!

执行计划也很正常

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+|  1 | SIMPLE      | A     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary; Using filesort            ||  1 | SIMPLE      | B     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using where; Using join buffer (hash join) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

实验二 带条件的

mysql> SELECT A.NAME,A.MONY,B.NAME,B.AGE,B.SEX    -> FROM books.FATHER A    -> LEFT JOIN books.children B ON A.ID=B.FATHER_ID    -> WHERE B.SEX='男'    -> ORDER BY A.ID ASC;+--------+------+-----------+------+------+| NAME   | MONY | NAME      | AGE  | SEX  |+--------+------+-----------+------+------+| 王五   | 3000 | 王八      |   38 | 男   || 王五   | 3000 | 王高      |   28 | 男   || 张三   | 3500 | 张亮      |   25 | 男   || 李四   | 5000 | 李贵      |   27 | 男   || 杨八   | 7500 | 杨七郎    |   35 | 男   |+--------+------+-----------+------+------+

主表的被屏蔽掉了,而执行计划是B表为主表,驱动表

详情见阅读原文