MySQL UNION使用与注意事项

union

最近工作中频繁接触到mysql的union写法。

通常我们都是合并相同的活字段一样的表查询的两个结果集。

用这个语法需要注意两件事情:

  1. 类型转换

  2. 字段的对应关系

需要特别关注的是,select的列并不是按字段名进行对应的,而是按select的顺序来匹配的。 如果忽略的这一点,可能会导致类型转换的问题。

附上手册的相关章节

语法

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

UNION用来将多个SELECT语句的结果合并到一个结果集。

第一个SELECT语句的列名用来作为最终返回结果集的列名。每个SELECT语句选择的列,对应的位置应该有相同的数据类型(例如,第一个语句的第一列应该与其他语句的第一列有相同的类型)。

如果对应的数据类型不匹配,那么UNION的结果需要顾及所有的SELECT语句的值(也即类型转换)。

例如:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+

这是正常的语句,但是有两个限制:

  • 只有最后一个SELECT语句可以使用INTO OUTFILE(但是,整个UNION的结果都会被写入到文件)。

  • HIGH_PRIORITY不能用在SELECT语句中。如果在第一个SELECT语句中指定它,没有效果。如果在后面的SELECT语句中使用,会报语法错误。

UNION的默认行为是移除重复的行,加上DISTINCT关键字也是一样的。如果使用ALL关键字,所有匹配的行都会被返回。

可以在SELECT语句中使用ORDER BY和LIMIT,需要放在括号内,例如:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

注:在MySQL5.7之后强制要求加括号。但是在测试的时候发现,不加也可以,例如:

SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10
UNION
SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10;

但是这两个sql含义是不一样的,上面的是在整个结果集中选出20条数据(不考虑重复);下面不加括号的版本其实值返回了第一个sql的10条数据,后面的limit限制的是整个结果集。

单独在SELECT语句中使用ORDER BY不会对结果集产生影响,因为UNION默认是不排序结果集的。因此,在此上下文中,ORDER BY通常和LIMIT一起使用,目的是确定子查询中的结果集,即使他不会对最终结果集产生任何影响。如果字句中只有ORDER BY,但是没有LIMIT,那么ORDER BY会被优化掉,因为他不会有任何影响。(亲测的确是这样,不要想当然的认为某个表会以排序的顺序在最终的结果集中)

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

这个语句不加括号效果一样。

这种ORDER BY不能使用包含表名的列引用(例如,table.col_name)。取而代之的是用第一个SELECT语句的列别名。

还有就是如果一个列有了别名,那么ORDER BY中必须使用别名,不能是列本身的名字。下面的两个查询,第一个正常,第二个会提示"Unknown column ‘a’ in ‘order clause’":

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

我们也可以指定额外的排序的列,例如,在每个SELECT语句中选择一个排序列:

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

也可以添加辅助排序的列,例如col1a:

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;

使用一个附加的列可以让你知道每个SELECT结果来自哪,例如添加上表名:

select id, name, 'table_1' from t1 
union
 select id, name, 'table_2' from t2;

UNION的语句如果在ORDER BY子句中包含一个聚合函数,会返回一个错误,编号和描述是:

Error: 3028 SQLSTATE: HY000 (ER_AGGREGATE_ORDER_FOR_UNION)

例如下面这个sql:

SELECT 1 AS foo UNION SELECT 2 ORDER BY MAX(1);

注:亲测在MySQL5.6.42,并没有报错。

总结

UNION其实用起来非常简单,文档上用很多篇幅介绍了ORDER BY,ORDER BY的这些限制,本质上是因为UNION采用临时表来实现

基于这一点,我们用UNION的时候筛选都尽量在每个子查询中写完整,而不是等UNION之后再用WHERE条件过滤。

例如下面两个相同功能的sql:

select id, name from t1 where name in ('x', 'y') 
union
select id, name from t2 where name in ('x', 'y');

-- 在特殊的需求下可能会这样处理
select * from (select id, name from t1 
union
select id, name from t2) tmp where name in ('x', 'y');

(完)