Oracle_12 分页查询问题

之前说到那个MybatisPlus的分页查询效率问题,然后改成了Oracle_12新特性进行分页查询,然后分页失效了

1
2
3
4
5
6
select u.user_id, u.username, u.updated_at, ulc.level_name, ul.level_id, ul.level_sort 
from users u
left join user_level ul on u.user_id = ul.user_id
left join user_level_config ulc on ul.level_id = ulc.level_id
order by ul.updated_at desc
offset 0 rows fetch next 10 rows only

这是查询首页,如果再下一页,就会是一样的数据

1
2
3
4
5
6
select u.user_id, u.username, u.updated_at, ulc.level_name, ul.level_id, ul.level_sort 
from users u
left join user_level ul on u.user_id = ul.user_id
left join user_level_config ulc on ul.level_id = ulc.level_id
order by ul.updated_at desc
offset 10 rows fetch next 10 rows only

数据根本没有变化,所以这样分页是失效的。经过Google和排查,发现问题出在排序order by ul.updated_at desc上,因为关联表的关系,部分useruser_level中是没有数据的,所以按照ul.updated_at排序是会失效的,所以换成order by ul.updated_at,u.updated_at desc这样就会正常分页

1
2
3
4
5
6
select u.user_id, u.username, u.updated_at, ulc.level_name, ul.level_id, ul.level_sort 
from users u
left join user_level ul on u.user_id = ul.user_id
left join user_level_config ulc on ul.level_id = ulc.level_id
order by ul.updated_at,u.updated_at desc
offset 10 rows fetch next 10 rows only

再尝试查询一次

1
2
3
4
5
6
select u.user_id, u.username, u.updated_at, ulc.level_name, ul.level_id, ul.level_sort 
from users u
left join user_level ul on u.user_id = ul.user_id
left join user_level_config ulc on ul.level_id = ulc.level_id
order by ul.updated_at,u.updated_at desc
offset 20 rows fetch next 10 rows only

所以这个offset ? rows fetch next ? rows only Oracle_12新特性语法,在分页上失效跟排序有很大的关系。一般业务上的分页请求都是xxx.com/findList/{pageSize}/{page},所以写的通用点就是:

1
SELECT * FROM [TABLE_NAME] ORDER BY [FIELD] DESC OFFSET  ((#{page} - 1) * #{pageSize})  ROWS FETCH NEXT #{pageSize} ROWS ONLY

本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!