1. 背景   * 全并查询结果是将多个 select 语句的查询结果合并到一起。   * 参与合并的结果集需要字段统一。   * 字段可以用空字符串''代替。

2. 合并查询结果实战 [ 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)


3. union与union all总结   * union相对于union all多了一步去重操作，此操作会创建临时表，降低性能。   * 当两边结果集数据相对都确定了唯一性，推荐使用union all。

4. 总结以需求驱动技术，技术本身没有优略之分，只有业务之分。