【Mysql】Mysql排序的字段值重复时导致分页查询数据错乱的问题解决

猫巳

已于 2023-07-20 15:32:42 修改

阅读量2.9k

点赞数 6

CC 4.0 BY-SA版权

分类专栏： # MySQL 数据库文章标签： mysql 数据库

于 2023-07-20 15:31:51 首次发布

本文链接：https://blog.csdn.net/qq_31635851/article/details/131831108

数据库同时被 2 个专栏收录

11 篇文章

订阅专栏

MySQL

9 篇文章

订阅专栏

文章介绍了在MySQL中，当ORDERBY的字段存在重复值时，分页查询可能出现错乱的问题。原因是服务器对具有相同排序值的行返回顺序是不确定的。解决方案包括为排序字段添加索引或在ORDERBY子句中加入具有唯一性的列，如ID，以确保查询的稳定性。

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

文章目录

1. 问题描述
2. 原因
3. 处理方法
- 方法一：添加排序字段为索引字段
- 方法二：在ORDER BY子句中加入具有唯一性的列

1. 问题描述

今天发现一个系统Bug，原来页面中的分页查询错乱了，经过排查发现，是因为排序的字段存在重复值引起的。

例如：

id	name	age
1	张三	23
2	李四	23
3	小明	23
…	…	…
20	李华	23

当我们分别执行

# 第1到10行数据
select * from user_table order by age limit 0,10

# 第11到20行数据
select * from user_table order by age limit 10,10

我们会发现第一次查询的数据再第二次中出现（非必然，这里只是举例，我遇到的错乱问题描述）。

2. 原因

官网描述：https://dev.mysql.com/doc/refman/8.0/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 列中具有相同的值，则服务器可以自由地以任何顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回这些行。换句话说，这些行的排序顺序相对于无序列来说是不确定的。

3. 处理方法

方法一：添加排序字段为索引字段

create index idx_user_age
    on user_table (age);

方法二：在ORDER BY子句中加入具有唯一性的列

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this

如果必须确保有 LIMIT 和无 LIMIT 时的行顺序相同，可以在 ORDER BY 子句中加入额外的列，使顺序具有确定性。例如，如果 id 值是唯一的，则可以通过以下排序方式使给定类别值的记录按 id 排序

例如将

select * from user_table order by age limit 0,10

改为

select * from user_table order by age,id limit 0,10