你好,游客 登录 注册 搜索
背景:
阅读新闻

SQL分页排序的实现与分页数据重复问题——以Oracle rownum为例

[日期:2011-02-22] 来源:Linux社区  作者:huzgd [字体: ]
至此似乎分页SQL已经完成了,表面上看这个SQL挺正确,运行起来似乎也没问题。但经过我们实践检验,其实这个SQL是不安全的,在某些情况下会出错,原因在于它没有排序。在分页情况下,第一页和第二页的数据是来自两次相对独立的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都是一致的顺序了。

linux
相关资讯       Oracle教程 
本文评论   查看全部评论 (1)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款
第 1 楼
* 匿名 发表于 2013/11/6 16:43:34
我们也出现了这个问题