Mysql分页order by数据错乱重复

        作久项目代码优化，公司用的是Mybatis，发现分页和排序时直接传递参数占位符用的都是 $，由于$有SQL注入风险，要改为#，但是封装page类又麻烦，所以直接使用了 pageHelper 插件了，方便快捷，但是测试时发现数据有问题：

//第二页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC  
LIMIT    5 , 5;

//第三页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC  
LIMIT    10 , 5

//第四页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC  
LIMIT    15 , 5

分页数量正常，但这3条SQL的结果集是一样的，第二第三第四页的数据，一模一样，我一脸懵逼，后来查了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.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders.

 大概意思是 ：一旦 order by 的 colunm 有多个相同的值的话，结果集是非常不稳定

那怎么解决呢，其实很简单，就是order by 加上唯一不重复的列即可，即在后面加上一个唯一索引就可以了，

ORDER BY idnumber DESC , id DESC

 

//第二页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC ,
id DESC
LIMIT    5 , 5;

//第三页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC ,
id DESC
LIMIT    10 , 5

//第四页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC  ,
id DESC
LIMIT    15 , 5

完美解决问题了。