SQL分页排序的实现与分页数据重复问题

╰半夏微凉° 2022-04-14 06:20 303阅读 0赞

转自：http://www.linuxidc.com/Linux/2011-02/32593p2.htm  
  
在oracle分页查询中，我们采用类似以下所示的公认的比较高效的数据库分页查询语句（Effective Oracle by Design中有描述、众多oracle使用者也做过测试）。  
  
写道  
SELECT \* FROM  
(  
SELECT A.\*, ROWNUM RN  
FROM (SELECT \* FROM TABLE\_NAME) A  
WHERE ROWNUM <= n)  
WHERE RN > m  
  
基于Rownum进行分页，表面上看运行起来似乎也没问题。但经过我们实践检验，其实实际上有时是不安全的，在某些情况下会出错，原因在于它没有排序。在分页情况下，第一页和第二页的数据是来自两次相对独立的SQL，如果没有排序，则SQL第一次和第二次执行时返回的结果是不一致的。  
不一致是什么意思？假设有一个无排序的SQL，我们把SQL执行两次：  
  
第一次执行后会返回有1、2、3、4、5共5条记录  
第二次执行后还是会返回有1、2、3、4、5共5条记录  
大部分情况下，这两次返回结果的顺序是完全一样的。但不幸的是，也许数据库有问题了，也许有人改了数据，反正有时候它会不一样，比如第二次执行时第2条和第4条对调了，返回的是1、4、3、2、5共5条记录，如下：  
  
第一次：1、2、3、4、5  
第二次：1、4、3、2、5  
假设我们对这个SQL进行分页，每页3条记录，共两页，正常情况下结果是这样的：  
  
拉第一页时，执行第一次SQL，按1、2、3、4、5排序，返回1、2、3三条记录  
拉第二页时，执行第一次SQL，按1、2、3、4、5排序，返回4、5两条记录  
但如果发生排序混乱的问题，结果会这样：  
  
拉第一页时，执行第一次SQL，按1、2、3、4、5排序，返回1、2、3三条记录  
拉第二页时，执行第二次SQL，按1、4、3、2、5排序，返回2、5两条记录  
结果我们会发现，分页结果很不正常，2这条记录出现了两次，4则消失了。正常来说，我们不会注意到有数据丢失，但我们会注意到分页的数据有重复。  
  
怎么办呢？那我们就加一个排序吧，排序子句要加在最里层的SQL里，这样分页出来的结果才会是排序后的结果。比如按名称、类别或作者排序的order by子句：  
  
select xxx.\*  
from (  
select rownum as recordno  
from (  
select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS from TAB001 where ATYPE='SOME\_TYPE'  
order by NAME,ATYPE,CREATOR  
) xx  
) xxx  
where recordno >= :开始记录号  
and recordno <= :结束记录号  
  
这样是不是可以了呢？答案还是不行，因为这些字段的值不是唯一的。可考虑一个极端情况，就是这个表里500万条记录的名称、类别和作者都完全一样，会有什么结果呢？结果仍然是无序。  
  
最终解决这个问题的办法，就是一定要用ID主键排序。不管前面有多少个order by字段，最后面一定要加上ID主键：  
  
  
select xxx.\*  
from (  
select rownum as recordno  
from (  
select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS from TAB001 where ATYPE='SOME\_TYPE'  
order by NAME,ATYPE,CREATOR,ID  
) xx  
) xxx  
where recordno >= :开始记录号  
and recordno <= :结束记录号  
  
由于主键ID是唯一的，所以只要ID不变，按ID排序就能保证每次执行分页SQL都是一致的顺序了。