By

Contents

通常来说，将联接操作看成是表之间的水平操作，因为该操作生成的虚拟表包含两个表中的列。而集合操作一般将这些操作视为垂直操作。MySQL数据库支持两种集合操作：UNION ALL和UNION DISTINCT。
与联接操作一样，集合操作也是对两个输入进行操作，并生成一个虚拟表。在联接操作中，一般把输入表称为左输入和右输入，或都第一个输入和第二个输入。集合操作的两个输入必须拥有相同的列数，若数据类型不同，MySQL数据库会自动进行隐式转化。同时结果列的名称由第一个输入决定。
在进一步阐述集合操作之前，先根据下列语句创建测试表x、y：

mysql> create table x(a char(1)); 
Query OK, 0 rows affected (0.01 sec)

mysql> create table y(a char(1)); 
Query OK, 0 rows affected (0.03 sec)

mysql> insert into x select 'a';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into x select 'b';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into x select 'c';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into y select 'a'; 
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into y select 'b';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>

接着来看对一个不同类型的数据进行集合操作：

mysql> select a as m from x
    -> union
    -> select 1 as n from dual
    -> union
    -> select 'abc' as o from dual
    -> union
    -> select now() as p from dual;
+---------------------+
| m                   |
+---------------------+
| a                   |
| b                   |
| c                   |
| 1                   |
| abc                 |
| 2016-03-18 20:44:33 |
+---------------------+
6 rows in set (0.00 sec)

mysql>

这里对各种不同的类型进行了联接，先从x表中取出类型为CHAR(1)的字符，第二个进行UNION操作的是整型数1，第三个是字符串abc，第四个是日期类型。虽然类型各不相同，但是MySQL数据库会自动对其进行判断，选出一种类型进行隐式转换。
另一方面，在这个例子中对每个选取操作都进行了别名定义，从最后的结果可以看出，MySQL选择了m这个别保，也就是集合操作中第一个SELECT输入的别名。
除了以下两点，集合操作中的SELECT语句和一般的SELECT查询并无不同：

  • 只有最后一个SELECT可以应用INOT OUTFILE，但是整个集合的操作将被输出到文件中。
  • 不能在SELECT语句中使用HIGH_PRIORITY关键字。

注意：在集合操作中INOT OUTFILE只能存在于最后一个SELECT语句中，否则MySQL数据库会提示语法错误，例如：

mysql> select a into outfile 'ret.txt' from x 
    -> union
    -> select a from y;
ERROR 1221 (HY000): Incorrect usage of UNION and INTO
mysql>

此外，虽然INTO OUTFILE只存在于最后一个SELECT语句中，但导出的结果是整个集合操作的结果，例如：

mysql> select a from x  union select a from y into outfile 'ret.txt';
Query OK, 3 rows affected (0.01 sec)

mysql> 
[mysql@mysql guo]$ cat ret.txt   #默认保存在data目录下的数据库目录
a
b
c
mysql> select a from x  union all select a from y into outfile '/home/mysql/ret.txt';       #指定保存目录
Query OK, 5 rows affected (0.00 sec)

mysql> system cat /home/mysql/ret.txt
a
b
c
a
b
mysql>

还有一点需要注意的是，若SELECT语句中包含LIMIT和ORDER BY子句，最好的做法是为参与集合操作的各SELECT语句添加括号，否则执行集合查询会得到错误提示，例如：
LIMIT 未添加括号

mysql> SELECT A FROM X ORDER BY A LIMIT 1
    -> UNION
    -> SELECT A FROM Y;
ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY
mysql>

只对第一个数据添加括号

    mysql> (SELECT A FROM X ORDER BY A LIMIT 1) UNION SELECT A FROM Y;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql>

这个集合操作的正确SQL语句应该为：

mysql> (SELECT A FROM X ORDER BY A LIMIT 1) UNION(SELECT A FROM Y);
+------+
| A    |
+------+
| a    |
| b    |
+------+
2 rows in set (0.00 sec)

mysql>

UNION DISTINCT 和UNION ALL
UNION DISTINCT组合两个输入，并应用DISTINCT过滤重复项。一般省略DISTINCT关键字，直接用UNION，例如：

mysql> SELECT A FROM X
    -> UNION
    -> SELECT A FROM y;
+------+
| A    |
+------+
| a    |
| b    |
| c    |
+------+
3 rows in set (0.00 sec)

mysql> SELECT A FROM X UNION ALL SELECT A FROM y;
+------+
| A    |
+------+
| a    |
| b    |
| c    |
| a    |
| b    |
+------+
5 rows in set (0.00 sec)

mysql>

MySQL数据库目前对于UNION DISTINCT的实现方式如下：

  • 创建一张临时表，即虚拟表。
  • 对这张临时表的列添加唯一索引（Unique Index）。
  • 将输入的数据插入临时表。
  • 返回虚拟表。

因为添加了唯一索引，所以可以过滤掉集合中重复的项。可以通过观察服务器状态变量Created_tmp_tables来确认UNION DISTICT的实现方式，例如：

mysql> show status like 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 10    |
+--------------------+-------+
1 row in set (0.00 sec)
mysql> SELECT A FROM X UNION SELECT A FROM y; 
+------+
| A    |
+------+
| a    |
| b    |
| c    |
+------+
3 rows in set (0.00 sec)

mysql> show status like 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 11    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql>

由于向临时表添加了唯一索引，插入的速度显然会因此而受到影响。如果确认进行UNION操作的两个集合中没有重复的选项，最有效的办法应该是使用UNION ALL。
UNION ALL 组合两个输入中所有的结果集，并包含重复的选项，例如：

mysql> SELECT A FROM X UNION ALL SELECT A FROM y;
+------+
| A    |
+------+
| a    |
| b    |
| c    |
| a    |
| b    |
+------+
5 rows in set (0.00 sec)

正如前面据说，如果确认两个输入没有重复项，应该选择UNION ALL。如果两个输入中有重复项，也可以在数据库端使用UNION ALL，在应用程序端进行DISTINCT的去重。

Contents