MySql Order by 字段出现重复导致 limit 分页后的数据错乱

骑猪看日落 2023-10-12 09:19 54阅读 0赞

*  问题描述

select \* from standard\_process order by event\_time desc 此条sql查询的结果如下：

![在这里插入图片描述][d8db04348b2749febc58e0764ade9fd2.png]

共有28条数据，确实是按照event\_time排序的。

但是加了limit以后就出现问题了，原本以为是截取的前10条，结果出现了数据错乱的情况。

select \* from standard\_process order by event\_time desc limit 10

select \* from standard\_process order by event\_time desc limit 10,10

select \* from standard\_process order by event\_time desc limit 20,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.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders. Consider this query, which is sorted by the category column but nondeterministic with respect to the id and rating columns:

说明当order by 列中有重复值的时候，mysql server 会看自己心情随机处理返回结果。

当使用mysql 进行分页查询出现数据不一致问题时候 可以看看order by 后面列数据是否有重复值。

所以此时的解决办法有2个，一个是order by 后面根据一个唯一的列去排序，如果还是优先想根据重复的列去排序，则可以使用多列排序。例如下面两种sql

select * from standard_process order by create_time desc
    
    select * from standard_process order by create_time desc limit 10
    
    select * from standard_process order by create_time desc limit 10,10
    
    select * from standard_process order by create_time desc limit 20,10
    
    
    select * from standard_process order by event_time desc,id desc 
    
    select * from standard_process order by event_time desc ,id desc limit 10
    
    select * from standard_process order by event_time desc,id desc  limit 10,10
    
    select * from standard_process order by event_time desc ,id desc limit 20,10

[d8db04348b2749febc58e0764ade9fd2.png]: https://img-blog.csdnimg.cn/d8db04348b2749febc58e0764ade9fd2.png