Mysql分页order by数据错乱重复

本文介绍了一个关于MyBatis中分页查询遇到的问题及解决方案,详细讲述了如何通过调整ORDER BY子句来避免非确定性的排序结果,确保每一页数据的准确性。

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

        作久项目代码优化,公司用的是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

完美解决问题了。

<think>嗯,用户问的是在使用SQL的ORDER BY之后,某些列为什么会丢失,以及如何解决。我需要根据提供的引用资料来分析这个问题。首先,我得回忆一下引用中的内容。引用1提到子查询中ORDER BY失效,导致分页正确。引用2指出分页查询时数据重复或丢失,可能和排序失效有关。引用3讨论GROUP BYORDER BY一起使用导致排序失效,分页数据异常。引用4是关于MyBatis中动态排序参数的处理问题。 可能的原因有几个方向:首先是子查询中的ORDER BY没有被正确应用,导致外层分页时顺序混乱。比如在子查询里排序,但外层查询没有正确继承排序,导致分页错误。第二个可能是当GROUP BYORDER BY同时使用时,GROUP BY可能会影响排序的结果,特别是如果排序字段在GROUP BY子句或聚合函数中,导致排序失效。第三个可能是动态拼接ORDER BY时参数传递正确,比如使用#{condition.field}的方式可能被转义,导致语句错误,应该用${}来直接替换变量。 另外,分页机制本身的问题,比如使用LIMIT和OFFSET时,如果排序稳定,相同的值可能导致分页数据重复出现。比如,如果排序字段有重复值,同页可能包含相同记录,进而导致数据丢失或重复。 解决方法可能包括:确保ORDER BY在正确的查询层级使用,避免在子查询中排序后外层又进行分页而未保留排序。使用稳定的排序字段,比如添加唯一字段作为第二排序条件。在动态SQL中正确使用${}而是#{}来避免引号问题。同时,检查GROUP BYORDER BY的字段是否冲突,可能需要调整分组或排序字段,或者在子查询中先排序再分组。 还需要考虑数据库的执行顺序,GROUP BY会在ORDER BY之前执行,所以如果在同一查询中同时使用,可能需要子查询先排序再分组,或者使用窗口函数来处理。此外,分页时的数据重复可能是因为数据变化导致OFFSET准,可以考虑使用游标分页是传统的LIMIT/OFFSET。 总之,需要综合引用中的案例,分析同情况下的原因,并给出对应的解决方案。可能需要具体问题具体分析,比如检查SQL语句的结构,确认排序字段是否正确应用,动态参数是否正确处理,以及分页机制是否稳定。</think>### SQL ORDER BY 后部分列丢失的原因及解决方法 #### 一、原因分析 1. **子查询中 ORDER BY 失效导致分页异常** 当在子查询中使用 `ORDER BY` 后,若外层查询未正确继承排序规则,可能导致分页数据错乱。例如: ```sql SELECT * FROM ( SELECT * FROM table ORDER BY create_time ) AS t LIMIT 0,10 ``` 某些数据库(如 MySQL)可能忽略子查询中的 `ORDER BY`,导致外层分页逻辑错误[^1]。 2. **GROUP BYORDER BY 冲突** 当 `GROUP BY` 和 `ORDER BY` 同时使用时,分组操作会覆盖排序规则。例如: ```sql SELECT department, AVG(salary) FROM employees GROUP BY department ORDER BY salary DESC ``` 此处 `GROUP BY department` 会导致 `ORDER BY salary` 失效,因为 `salary` 未被聚合或包含在分组字段中[^3]。 3. **动态拼接 ORDER BY 参数错误** 在 MyBatis 等框架中,若使用 `#{field}` 动态拼接排序字段,会导致字段被引号包裹(如 `ORDER BY 'create_time' DESC`),进而引发语法错误或排序失效[^4]。 4. **分页数据重复或丢失** 当排序字段存在重复值时,使用 `LIMIT/OFFSET` 分页可能导致数据重复或丢失。例如: ```sql SELECT * FROM table ORDER BY score LIMIT 10 OFFSET 10 ``` 若 `score` 有相同值,同页可能包含相同记录[^2]。 --- #### 二、解决方法 1. **确保排序在正确层级生效** - 将 `ORDER BY` 放在外层查询,避免子查询排序被忽略: ```sql SELECT * FROM ( SELECT * FROM table ) AS t ORDER BY create_time LIMIT 0,10 ``` 2. **处理 GROUP BYORDER BY 冲突** - 在子查询中先排序再分组: ```sql SELECT * FROM ( SELECT * FROM employees ORDER BY salary DESC ) AS t GROUP BY department ``` - 使用窗口函数(如 MySQL 8.0+ 的 `ROW_NUMBER()`): ```sql SELECT department, AVG(salary) FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees ) AS t GROUP BY department ``` 3. **正确拼接动态排序参数** - 在 MyBatis 中使用 `${field}` 避免引号包裹: ```xml ORDER BY ${condition.field} ${condition.sortRule} ``` - 添加白名单校验字段合法性,防止 SQL 注入。 4. **稳定分页机制** - 添加唯一字段作为第二排序条件(如主键): ```sql SELECT * FROM table ORDER BY score, id LIMIT 10 OFFSET 10 ``` - 改用游标分页(如基于最后一条记录的排序值): ```sql SELECT * FROM table WHERE score > {last_score} ORDER BY score LIMIT 10 ``` --- #### 三、总结对比 | 场景 | 现象 | 解决方案 | |--------------------|------------------|-----------------------| | 子查询 ORDER BY 失效 | 分页数据错乱 | 外层显式添加 ORDER BY | | GROUP BYORDER BY 冲突 | 排序未生效 | 子查询先排序或使用窗口函数 | | 动态参数拼接错误 | 字段被引号包裹导致语法错误 | 使用 `${}` 并校验字段合法性 | | 分页数据重复/丢失 | 相同排序值导致边界重叠 | 添加唯一字段排序或改用游标分页 | ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值