MySQL order by 排序使用的字段有重复值导致排序随机的情况

最新推荐文章于 2025-04-01 11:56:34 发布

原创最新推荐文章于 2025-04-01 11:56:34 发布 · 1.4k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #java #数据库 #sql

MySQL系列专栏收录该内容

6 篇文章

订阅专栏

当MySQL查询中ORDER BY的字段存在相同值时，返回结果的顺序可能是不确定的，这可能导致分页查询时数据不一致。解决方案是在ORDER BY子句中加入唯一或主键字段以确保排序的稳定性。例如，通过在SQL语句末尾添加`epc.id DESC`来确保排序的确定性。这样可以避免因相同值导致的数据查询遗漏问题。

生产环境一个列表页的查询sql:

select epc.id, epc.contractName, epc.contractType, epc.officerId,ee.employeeName officerName
        from er_product_contract epc left join er_employee ee on epc.officerId = ee.employeeId
        LEFT JOIN simu_product p ON epc.productCode = p.productCode order by p.productNum, epc.contractType desc

根据simu_product表中的productNum和er_product_contract表中的contractType字段排列，但是这两个字段的组合有很多数据是相同的，此时的查询结果是MySQL根据productNum和contractType随机返回的。(生产环境MySQL版本：5.6.26-log)

 查了一下MySQL官网，对这种情况做了介绍：

https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

“If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.”

翻译：如果在ORDER BY列中有多个行具有相同的值，则服务器可以自由以任何顺序返回这些行，并且根据整体执行计划的不同，返回值可能会有所不同。换句话说，这些行的排序顺序相对于无序列是不确定的。

基于这个，就知道为什么会出现这个问题了。这个SQL主要问题是会可能导致查询查不出一些记录，如果productNum和contractType字段相同的记录多于分页每页记录数，就会导致一些记录上一页出现了，下一页又查出了（MySQL随机返回导致的），而一些数据可能就查不出来了。怎么解决这个问题，想必大家页想到了，MySQL官网上也给出了一种方案：

“If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:”

在ORDER BY子句中包括其他列以使顺序确定，比如可以使用主键id等。上面的SQL可以修改下：

select epc.id, epc.contractName, epc.contractType, epc.officerId,ee.employeeName officerName
        from er_product_contract epc left join er_employee ee on epc.officerId = ee.employeeId
        LEFT JOIN simu_product p ON epc.productCode = p.productCode order by p.productNum, contractType desc,epc.id DESC

以上，问题得到解决。