- 背景 * 全并查询结果是将多个 select 语句的查询结果合并到一起。 * 参与合并的结果集需要字段统一。 * 字段可以用空字符串''代替。
- 合并查询结果实战 [ users1 and users2 ] * 查看 users1 表和 users2 表结构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表中有相同字段 tommysql> 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合并并去重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)
lisea 2017/7/4 星期二 上午 10:45:53
* union all只全并不去重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)
- union与union all总结 * union相对于union all多了一步去重操作，此操作会创建临时表，降低性能。 * 当两边结果集数据相对都确定了唯一性，推荐使用union all。
- 总结以需求驱动技术，技术本身没有优略之分，只有业务之分。
