记录mysql排序字段有重复值,分页数据错乱问题,下面2个sql 除了分页limit外,其他都一样,

但是第二页的结果却包含部分第一页的数据。

 

select * from t_jfsc_studentrankdetail where sfsc =0 order by integralnum desc limit 0,10;
select * from t_jfsc_studentrankdetail where sfsc =0  order by integralnum desc limit 10,10;

查了一下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列中有多个行具有相同的值,则服务器可以自由以任何顺序返回这些行,并且根据整体执行计划的不同,返回值可能会有所不同。 换句话说,这些行的排序顺序相对于无序列是不确定的。

 解决方案:排序字段增加一个不可能重复的字段,如 order by integralnum ,id desc即可解决

 

参考文档:https://blog.csdn.net/qq_22076345/article/details/102975191