摘要
本文介绍MySQL中使用union多个子集导致排序失效的场景如何解决。
失效场景复现
目的
想先让A表按id升序，再让B表按id降序，然后拼在一起，最终想保证顺序一致
(SELECT id, name FROM A ORDER BY id ASC)
UNION
(SELECT id, name FROM B ORDER BY id DESC);
结果
A或B内单独查询顺序无误，但UNION后排序反而乱了...
原因
mysql8.0官方给的解释是：UNION分支里的ORDER BY会被优化器直接扔掉，想让单个分支先排好序，必须在该分支里再加一个LIMIT任意大数，并且最后把整个UNION再包一层SELECT，在最外层写ORDER BY。
正确写法
- 多子集排序字段一致：
SELECT * FROM (
SELECT id, name, 1 AS seq -- 用常量列保住先后顺序
FROM A
ORDER BY id ASC
LIMIT 99999999 -- 必须加LIMIT，否则ORDER BY会被优化掉
) a
UNION
SELECT * FROM (
SELECT id, name, 2 AS seq
FROM B
ORDER BY id DESC
LIMIT 99999999
) b
ORDER BY seq, id; -- 外层再按需要整体排序
- 多子集排序字段不一致：
SELECT * FROM (
SELECT id, create_time, 1 AS seq -- 用常量列保住先后顺序
FROM table_A
ORDER BY create_time ASC
LIMIT 99999999 -- 必须加LIMIT，否则ORDER BY会被优化掉
) a
UNION
(
SELECT id, create_time, 2 AS seq
FROM table_B
ORDER BY id DESC
LIMIT 99999999
) b
ORDER BY seq; -- 仅保证先A后B，不再对内部二次排序
总结
以上我们了解了mysql中union多个子集时防止子集内的排序失效的做法，子集写limit，外层再包含，最终再order。
关注公众号：咖啡Beans
在这里，我们专注于软件技术的交流与成长，分享开发心得与笔记，涵盖编程、AI、资讯、面试等多个领域。无论是前沿科技的探索，还是实用技巧的总结，我们都致力于为大家呈现有价值的内容。期待与你共同进步，开启技术之旅。
