mysql中排序和分页出现重复数据

场景

我们使用分页查询时候经常会遇到需要对某个或者某几个字段进行排序，在排序字段有相同值的情况下有可能最后的排序结果不是我们预期的样子，我们来看下一个例子。

表里面的rec_time列的数据是有重复的，一般情况下我们分页的sql是这样的。
第1页

select * from ugc_keyword WHERE 1 order by rec_time desc LIMIT 0,10;

结果如下：

2610    teacher_general    warning    中国病毒    2021-04-24 17:46:54    yes    0
2609    general    warning    中国病毒    2021-04-24 17:46:44    yes    0
2608    teacher_general    warning    黄牛    2021-04-24 16:53:18    yes    0
2607    teacher_letter    warning    黄牛    2021-04-24 16:51:26    yes    0
2606    letter    warning    黄牛    2021-04-24 16:47:07    yes    0
2605    express    forbid    微信    2021-04-23 22:36:55    yes    0
2604    express    forbid    公众号    2021-04-23 22:30:00    yes    0
2603    express    forbid    app    2021-04-23 22:29:24    yes    0
2544    teacher_letter    warning    176    2021-04-23 18:27:42    yes    0
2542    teacher_letter    warning    185    2021-04-23 18:27:42    yes    0

第2页

select * from ugc_keyword WHERE 1 order by rec_time desc LIMIT 10,10;

结果如下：

2546    teacher_letter    warning    171    2021-04-23 18:27:42    yes    0
2554    teacher_letter    warning    177    2021-04-23 18:27:42    yes    0
2544    teacher_letter    warning    176    2021-04-23 18:27:42    yes    0
2545    teacher_letter    warning    1709    2021-04-23 18:27:42    yes    0
2552    teacher_letter    warning    189    2021-04-23 18:27:42    yes    0
2551    teacher_letter    warning    181    2021-04-23 18:27:42    yes    0
2540    teacher_letter    warning    155    2021-04-23 18:27:42    yes    0
2550    teacher_letter    warning    180    2021-04-23 18:27:42    yes    0
2553    teacher_letter    warning    1700    2021-04-23 18:27:42    yes    0
2547    teacher_letter    warning    166    2021-04-23 18:27:42    yes    0

可以发现，id为2544的这条数据出现在了第1页和第2页当中，这样就不是我们预期当中的结果。
那这是为什么呢？

分析

在我们以往的经验和理解当中，这里应该是先进行order by排好序，然后再取对应的分页数据。其实在mysql当中并不是这样的，通过查阅官方文档可以发现在mysql中会对limit优化，我们提取最重要的信息：

大概的意思就是 如果在ORDER BY列中有多个行具有相同的值，服务器可以自由地以任何顺序返回这些行，而且根据总体执行计划的不同，返回顺序也可能不同。换句话说，这些行对于非有序列的排序顺序是不确定的。影响执行计划的一个因素是LIMIT，因此带有或不带有LIMIT的ORDER BY查询可能会返回不同顺序的行。

通过以上信息我们就可以分析出，因为排序的字段是rec_time,这个字段的值是具有相同的值的，所以返回的行是随机的。所以不同的页面出现了重复数据

解决方式

官方的解决方式：

大概的意思就是 如果确保具有或不具有LIMIT的相同行顺序很重要，那么在order BY子句中包括额外的列，以使顺序具有确定性，例如id值是唯一的。

那么新增一个排序字段id，因为在表中id是有序唯一的，所以把查询sql修改为

select * from ugc_keyword WHERE 1 order by rec_time desc,id desc LIMIT 0,10;

作者：admin

PHP面试网 站点 QQ交谈