Mysql 排序分页混乱

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.

现象一

id=25这条数据，出现在第一页、第二页。

数据特征： 排序字段，重复率极高，仅有2个值。

数据库版本：

现象二

相同数据，在以下数据库版本，均排序分页正常。

• 8.0.11
• 5.6.46

第一页：

SELECT * FROM city order by FIELD(state,'ENABLE','DISABLE') LIMIT 0,10;

第二页：

SELECT * FROM city order by FIELD(state,'ENABLE','DISABLE') LIMIT 10,10;

解决方案

关于现象一，官网给出以下解释：

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.

当有多个重复值的时候，mysql server返回的是任意顺序。具体的顺序依赖执行计划。

关于现象二，暂没合理的解释。

• 相同的数据库版本，类似的重复数据，分页排序就是正常的。
• 相同的数据，不同的数据库版本，分页排序也是正常的。

参考

8.2.1.17 LIMIT Query Optimization