MySQL UNION语句中各SELECT子句如何实现不同的排序规则

问题根因 #

MySQL 优化器默认会忽略 UNION 子查询内部没有搭配 LIMIT 的 ORDER BY 语句，因为 UNION 默认会对合并后的结果集做去重和整体排序，子查询内的排序会被认为无意义直接优化。你去掉 WHERE 时刚好未触发该优化逻辑所以能正常运行，添加 WHERE 后优化规则生效就导致子句排序失效。

解决方法 #

方法1：子查询添加足够大的LIMIT #

给每个子查询添加一个远大于表数据量的LIMIT，即可让优化器保留子查询内的排序逻辑：

(SELECT
  id,
  name
FROM
  table1
WHERE
  active=1
ORDER BY name
LIMIT 1000000)
UNION
(SELECT
   id,
   name
FROM
   table2
WHERE
   active=1
ORDER BY id
LIMIT 1000000);

注意：如果不需要对两个结果集去重，把UNION替换为UNION ALL可以大幅提升查询效率。

方法2：新增标识字段统一排序（更稳定） #

该方法不受MySQL优化规则影响，排序逻辑更可控，推荐生产环境使用：

SELECT id, name 
FROM (
    -- 标记第一个查询来源，排序字段取name
    SELECT 
        id, 
        name, 
        1 AS source,
        name AS sort_col
    FROM table1
    WHERE active=1
    UNION ALL
    -- 标记第二个查询来源，排序字段取id（类型要和第一个sort_col保持一致）
    SELECT 
        id, 
        name, 
        2 AS source,
        CAST(id AS CHAR) AS sort_col
    FROM table2
    WHERE active=1
) AS union_result
ORDER BY 
    source ASC, -- 保证第一个查询结果全部排在前面
    sort_col ASC; -- 按各自的排序字段排序

以上两种方法运行后都可以得到你预期的合并结果。

内容的提问来源于stack exchange，提问作者Dex