MySQL order by + limit 分页隐藏陷阱

最新推荐文章于 2024-04-17 15:28:40 发布

张晓鱼

最新推荐文章于 2024-04-17 15:28:40 发布

阅读量720

点赞数 2

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/jwyspnys1314/article/details/88243311

版权

在使用MySQL的order by + limit进行分页时，遇到加limit前后结果顺序不一致及分页数据重叠的问题。原因是当排序字段有重复值时，MySQL的limit在排序未完成时可能提前结束，导致不同结果。解决方案是增加一个唯一的排序字段，如id，以确保分页的准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL通过order by + limit实现分页的时候遇到了两个问题：

SQL语句加limit和不加limit返回的顺序不一致
SQL语句第一页和第二页的数据出现重叠（一共就两页数据）

完整的表结构和数据见文章末尾

不带limit的SQL语句如下：

select * from album_play_stat_app_type_daily order by active_user_num desc

1、加limit和不加limit结果集顺序不一致

当不加limit的时候返回的结果集为：
在这里插入图片描述
当加了limit之后SQL语句为：

select * from album_play_stat_app_type_daily order by active_user_num desc limit 0, 10

返回结果为：
在这里插入图片描述
比较两次的SQL语句，从执行逻辑上来看，第二条SQL语句应该按照第一条SQL的结果集取前10条返回才对，但实际上却并非如此！

2、第一页和第二页的数据出现重叠

修改SQL语句查询第二页数据，SQL语句为：

select * from album_play_stat_app_type_daily order by active_user_num desc limit 10, 10;

返回值：
在这里插入图片描述
比较第一页和第二页返回的结果集，可以看到当中有三条记录出现了重叠。

3、原因

3.1、limit加与不加返回顺序不一样

当order by后面的排序字段出现重复值的时候，比如问题中的active_user_num字段，存在很多的重复值，MySQL排序的时候返回他们的顺序是不确定的；
而limit语句在执行的时候并不会等到所有的结果都排序完成之后再进行截取，而是当有序集的大小已经满足要求的时候便会结束后续操作，返回有序集；

3.2、第一页和第二页数据重叠

原因同问题1；

4、解决方案

再添加一个排序字段，比如id，保证联合排序字段唯一，修改后SQL如下：

select * from album_play_stat_app_type_daily
order by active_user_num desc, id desc

参考：MySQL 官方手册-limit查询优化

完整的表结构和数据

CREATE TABLE `album_play_stat_app_type_daily` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `app_type` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '应用类型',
  `album_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '专辑ID',
  `album_title` varchar(255) CHARACTER SET utf8 NOT

最低0.47元/天解锁文章