今天使用手写了的分页查询,结果出现了重复数据。
问题相关sql及分析
SELECT * FROM (
SELECT tt.*, ROWNUM AS rowno
FROM (SELECT * FROM XXXXX ORDER BY NAME) tt
WHERE ROWNUM <= ${page.endIndex}) table_alias
WHERE table_alias.rowno >= ${page.startIndex}
这个手写sql与mybatis-plus自动生成的sql一致,好像没有发现什么问题。
1.拆分sql
SELECT tt.*, ROWNUM AS rowno
FROM (SELECT * FROM XXXXX ORDER BY NAME) tt
SELECT tt.*, ROWNUM AS rowno
FROM (SELECT * FROM XXXXX ORDER BY NAME) tt
WHERE ROWNUM <= ${page.endIndex}
对比上面两个sql的结果,可以发现,加上where条件之后的前endIndex+1个数据中的rownum与没有加上条件的rownum不一致,且前endIndex+1个数据并不是没有加上条件中的前endIndex+1个数据。因此问题出现在ROWNUM <= ${page.endIndex}上面。
解决方案
1.数据库order by排序不唯一,会导致最终的数据排序是不是稳定的,表现出来的结果,就是分页时会重复显示,可以排序后加上唯一值,比如id,key等(有时候好像不起作用)。
SELECT * FROM (
SELECT tt.*, ROWNUM AS rowno
FROM (SELECT * FROM XXXXX ORDER BY NAME,id) tt
WHERE ROWNUM <= ${page.endIndex}) table_alias
WHERE table_alias.rowno >= ${page.startIndex}
- 把分页条件放置到获取rownum的更外层
SELECT * FROM (
SELECT tt.*, ROWNUM AS rowno
FROM (SELECT * FROM XXXXX ORDER BY NAME) tt) table_alias
WHERE table_alias.rowno >= ${page.startIndex}
and table_alias.rowno <= ${page.endIndex}