分页查询的重复数据问题

一、前言

相信在平常数据库的使用过程中,大家都有使用过分页查询语来获取数据.不知道有没有使用过类似下面(1)(2)的查询语句。

(1)select * from user limit 0, 4 ;

(2)select * from user limit 4, 4;

以上查询语句等价于调用DSF的分页查询方法时不传入排序参数。如果使用过,那可就要小心了,这里面可能会有一个隐藏的问题。

二、问题阐述

2.1分页造成数据缺失

假设现在有一张user表,有以下数据。

Id(主键)

User_name

Createtime

9

A

2017-04-26 09:36:20

8

B

2017-04-26 09:36:19

7

C

2017-04-26 09:36:18

6

D

2017-04-26 09:36:17

5

E

2017-04-26 09:36:17

4

F

2017-04-26 09:36:17

3

G

2017-04-26 09:36:16

2

H

2017-04-26 09:36:15

1

I

2017-04-26 09:36:14

仍然执行上面两条sql,假设先执行(1)再执行(2)。

我们期待数据库按id有序输出。在执行完(1)后,返回id值为1,2,3,4的数据,执行完(2)后返回id值为5,6,7,8的数据。

在mysql中,确实会有MyISAM 表按插入顺序作为默认的排序方式输出结果,InnoDB 表按id默认排序输出结果。但是,这种默认排序并不是一定会严格执行的。数据的增删改,以及数据的缓存,都可能会对mysql的结果输出顺序造成影响。所以,极有可能执行完(1)后返回1,2,3,4,执行完(2)后返回4,5,6,7的情况,造成部分数据重复查询,部分数据漏查的情况(ORACLE默认排序也会有同样的问题)。要解决这一问题,其实很简单,只需要对结果排序,不再使用mysql不靠谱的默认排序 ,就可以解决这一问题。

(3)select * from user  order by id limit 0, 4 ;

(4)select * from user  o rder by id limit 4, 4; 

这样在执行语句(3)(4)时,一定会严格按照id的顺序返回正确的结果。

2.2排序列必须唯一

         在执行(3),(4)时,按id排序可以得到正确结果。可是,我们的查询语句,很多时候是如下形式:

(5)select * from user   orderby createtime limit 0, 4;

(6)select * from user   order by createtime limit 4, 4;

         按照createtime排序能解决这个问题吗?答案是不能。查看user表的数据可以发现,id值为4,5,6的数据其createtime是一样的。这就意味着,mysql对4,5,6进行排序时,其排序结果是不确定的。也就是说,执行语句(5)时,有可能返回的是1,2,3,4,有可能返回的是1,2,3,5。因为createtime一致,部分数据的排序位置并不固定,所以真正要解决重复数据问题,不仅要排序,排序的列还要严格唯一,如语句(7)(8),增加唯一的主键作为排序依据,可以解决这个问题。

(7)select * from user  orderby createtime,id limit 0, 4;

(8)select * from user  order by createtime,id limit 4, 4;

以上查询语句可以消除相同的createtime值对排序结果的影响。

2.3插入和删除带来新问题

      上面的示例中,我们并未考虑数据插入或者删除的情况。如果在查询时,数据存在插入或者删除,语句(3)(4)、(7)(8)能得到正确的结果吗?答案依旧是——不能。同样,依旧使用user表进行举例。首先执行语句(3),返回结果1、2、3、4。假设此时,执行了删除语句,删除了id为1的数据。接下来,再执行语句(4),返回的结果会变为6,7,8,9,出现漏查数据5的情况。这是因为,删除id为1的数据后,id为5的数据排序提升一个位置,提升到第4位,故不再出现在语句(4)的结果集中。在查询过程中,插入数据同样会导致数据顺序发生变化,进而导致结果集出现重复或者遗漏的情况。如何解决这个问题呢,分页查询需要换成如下写法:

(9)select * from userwhere id>0 order by id limit 0,4;

(10)select * from userwhere id>4 order by id limit 0,4;

这种写法,分页的页码不再变化,一直为0,同时新增查询条件id,id比较值为上一次分页查询结果集中id的最大值。例如,在执行完语句(9)之后,返回结果为1,2,3,4。最大的id值为4,故语句(10)中增加id>4的查询条件。当执行完语句(10)后,返回结果为5,6,7,8.如果要查询第三页的数据,则查询语句写为:

(11)select * from userwhere id>8 order by id limit 0,4;

这种查询方式,每次都是查询的第一页,但是通过已查询数据的id最大值对已经查询出来的数据进行过滤。

(三)总结

     综上,分页查询时,采取哪种sql语句的形式,主要看对分页查询数据出现缺失或者重复时的容忍程度。

(1)如果完全不在意数据是否有缺失或者重复,无需进行任何排序。

(2)如果不考虑数据有删除、插入对查询结果集造成影响的情况,则采用排序的方法可以有效避免分页查询结果重复或者缺失的情况,最好采用唯一列进行排序。

(3)如果要避免有删除、插入对查询结果集造成影响,则需要利用上一次分页查询结果作为下一次查询的条件,不断对查询结果进行过滤,来确保得到正确的查询结果。

评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值