这篇文章主要介绍了union和子查询中order by一起使用导致排序失效问题及解决方案，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教
目录
- 一、前言
- 二、问题列举
- 2.1 子查询中不能使用order by
- 2.2 子查询order by无效
- 2.3 排序条件不够严格导致分页数据重复
- 总结
一、前言
分页查询的需求如同家常便饭，多数情况下主要利用order by和limit即可实现，有些稍复杂一点的可能需要用到union操作去连接多个子查询结果集。
然而这三个操作是有一些需要留意的问题，下文将列举出3个可能碰到的情况。
MySQL版本：5.7.21
二、问题列举
2.1 子查询中不能使用order by
简单的union操作如下：
SELECT * FROM tb_artist WHERE id >= 10 UNION SELECT * FROM tb_artist WHERE id < 10
其结果为：
若子查询中包含order by，写法假设如下：
SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC UNION SELECT * FROM tb_artist WHERE id < 10
则语法报错：1221 - Incorrect usage of UNION and ORDER BY
应当将子查询括起来，正确写法如下：
(SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC) UNION (SELECT * FROM tb_artist WHERE id < 10)
其结果为：
语法没问题了，但是注意到子查询的order by却没有效果，故引出下面第二种问题。
2.2 子查询order by无效
解决方式是在子查询的order by后添加limit操作即可，具体可以limit一个不小于子查询结果集大小的数值，如下：
(SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC LIMIT 99999) UNION (SELECT * FROM tb_artist WHERE id < 10)
其结果为：
2.3 排序条件不够严格导致分页数据重复
该问题与union无直接联系，属于order by及limit本身的注意点。
即，如果SQL中的order by条件比较宽松不够严格，或者说是结果集中的每行记录存在并列或不唯一的次序的话，MySQL可能会随机给并列记录行进行排序，特别是排序又分页查询配合了limit操作，可能会导致上一页有的记录行又出现在了下一页的结果集之中。
对一些后台系统的分页表格中可能感觉不明显，但对于同样SQL查询逻辑的C端下拉列表的效果一目了然，特别是对带有图片的，很容易看到数据的重复。
解决方案：
所以在做分页查询时，要尽量保证每行记录都有唯一确定的次序，具体做法可以在原有排序条件后添加id或编号等这类唯一值的字段（索引字段也可以）。
例如，在艺术家artist创建时间降序排序的基础上，再对唯一code进行排序：
SELECT * FROM tb_artist WHERE logic_delete = 0 ORDER BY create_time DESC, code ASC LIMIT 0, 5
原因分析：
排序离不开算法，在关系型数据库中，往往会存在多种排序算法。通过MySQL的源码和官方文档介绍可以得知，它的排序规律可以总结如下：
- 当
order by不能使用索引进行排序时，将使用排序算法进行排序； - 若排序内容能全部放入内存，则仅在内存中使用快速排序；
- 若排序内容不能全部放入内存，则分批次将排好序的内容放入文件，然后将多个文件进行归并排序；
- 若排序中包含
limit语句，则使用堆排序（不稳定）优化排序过程。
所以解决排序分页数据重复问题有两种方式，第一种就是，在排序中加上唯一值，比如主键 id，这样由于 id 是唯一的，就能确保参与排序的 key 值不相同；第二种就是避免使用堆排序，让order by根据索引来排序。
说白了，就是order by后面的字段要有索引。
另外，使用JPA分页查询时，若order by的是非索引字段，通过查看JPA的sql发现，不会再去自动在order by后添加索引或id字段，需要注意。
MySQL5.7 相关文档：
https://dev.mysql.com/doc/refman/5.7/en/union.html
https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持北冥有鱼。
