1. 背景
* 全并查询结果是将多个 select 语句的查询结果合并到一起。
* 参与合并的结果集需要字段统一。
* 字段可以用空字符串''代替。
2. 合并查询结果实战 [ users1 and users2 ]
* 查看 users1 表和 users2 表结构
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql>
desc
users1;
+
-------+---------------+------+-----+---------+----------------+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
-------+---------------+------+-----+---------+----------------+
| id |
bigint
(20) |
NO
| PRI |
NULL
| auto_increment |
|
name
|
varchar
(64) |
NO
| |
NULL
| |
| sex | enum(
'M'
,
'F'
) |
NO
| |
NULL
| |
| age |
int
(11) |
NO
| |
NULL
| |
+
-------+---------------+------+-----+---------+----------------+
4
rows
in
set
(0.00 sec)
mysql>
desc
users2;
+
-------+---------------+------+-----+---------+----------------+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
-------+---------------+------+-----+---------+----------------+
| id |
bigint
(20) |
NO
| PRI |
NULL
| auto_increment |
|
name
|
varchar
(64) |
NO
| |
NULL
| |
| sex | enum(
'M'
,
'F'
) |
NO
| |
NULL
| |
| age |
int
(11) |
NO
| |
NULL
| |
+
-------+---------------+------+-----+---------+----------------+
4
rows
in
set
(0.01 sec)
|
* 查看 users1 表和 users2 表数据
users1和users2表中有相同字段 tom
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql>
select
*
from
users1;
+
----+------+-----+-----+
| id |
name
| sex | age |
+
----+------+-----+-----+
| 1 | tom | M | 25 |
| 2 | jak | F | 42 |
+
----+------+-----+-----+
2
rows
in
set
(0.00 sec)
mysql>
select
*
from
users2;
+
----+-------+-----+-----+
| id |
name
| sex | age |
+
----+-------+-----+-----+
| 1 | tom | M | 25 |
| 2 | lisea | M | 42 |
+
----+-------+-----+-----+
2
rows
in
set
(0.00 sec)
|
* union 合并并去重
|
1
2
3
4
5
6
7
8
9
|
mysql> (
select
*
from
users1)
union
(
select
*
from
users2);
+
----+-------+-----+-----+
| id |
name
| sex | age |
+
----+-------+-----+-----+
| 1 | tom | M | 25 |
| 2 | jak | F | 42 |
| 2 | lisea | M | 42 |
+
----+-------+-----+-----+
3
rows
in
set
(0.00 sec)
|
* union all 只全并不去重
|
1
2
3
4
5
6
7
8
9
10
|
mysql> (
select
*
from
users1)
union
all
(
select
*
from
users2);
+
----+-------+-----+-----+
| id |
name
| sex | age |
+
----+-------+-----+-----+
| 1 | tom | M | 25 |
| 2 | jak | F | 42 |
| 1 | tom | M | 25 |
| 2 | lisea | M | 42 |
+
----+-------+-----+-----+
4
rows
in
set
(0.01 sec)
|
* 查看union 性能分析
[ 使用了临时表 ]
mysql> explain (select * from users1) union (select * from users2); +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | users1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 2 | UNION | users2 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.01 sec)
* 查看union all 性能分析
[ 未使用临时表 ]
mysql> explain (select * from users1) union all (select * from users2); +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | users1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 2 | UNION | users2 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 1 warning (0.01 sec)
3. union 与 union all 总结
* union 相对于 union all多了一步去重操作，此操作会创建临时表，降低性能。
* 当两边结果集数据相对都确定了唯一性，推荐使用union all。
4. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1943768，如需转载请自行联系原作者
