mysql分页-避免丢失或重复数据到客户端

Question

我在mySql中有一个这样的表

CREATE TABLE `usermst` (
  `userid` smallint(5) unsigned NOT NULL,
  `username` varchar(45) NOT NULL,
  `insdate` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

有如下数据

insert into usermst(userid, username)
values (1, "user 1"),
       (2, "user 2"),
       (3, "user 3"),
       (4, "user 4"),
       (5, "user 5"),
       (6, "user 6"),
       (7, "user 7"),
       (8, "user 8"),
       (9, "user 9"),
       (10, "user 10");

如果我查询有4条记录的第一页（分页），它的工作就可以了。

select * from usermst order by insdate desc, userid desc limit 0,4;

输出：

userid  username insdate
10      user 10  2016-03-08 12:32:04.239335
9       user 9   2016-03-08 12:32:04.239335
8       user 8   2016-03-08 12:32:04.239335
7       user 7   2016-03-08 12:32:04.239335

请求第二页，查询如下

select * from usermst order by insdate desc, userid desc limit 4,4;

输出：

userid  username insdate
6       user 6   2016-03-08 12:32:04.239335
5       user 5   2016-03-08 12:32:04.239335
4       user 4   2016-03-08 12:32:04.239335
3       user 3   2016-03-08 12:32:04.239335

但是，如果以某种方式删除了第1页的记录，客户端将丢失数据。或插入第1页的记录（根据我的查询排序），客户端将获取重复数据。如何防止此错误？

Answer 1

我将使用的方法是将分页基于排序列值，但仅在遍历页面时（下一页/上一页按钮）。当希望导航到特定页码时，仍然需要使用您现有的方法，但我认为您仅描述的问题实际上是使用下一页/上一页按钮导航时的问题。

对于每个“下一页”，获取“insdate”小于上一页读取中最后一个“insdate”的所有行。

第一页：

SELECT * FROM usermst ORDER BY insdate DESC, userid DESC LIMIT 0,4;
SELECT @prevInsDate := insdate, @prevUserId := userid FROM usermst 
    ORDER BY insdate DESC, userid DESC LIMIT 3,1;

下一页（第二页）：

SELECT * FROM usermst 
WHERE insdate <= @prevInsDate AND userid < @prevUserId
ORDER BY insdate DESC, userid DESC LIMIT 0,4;

SELECT @prevInsDate := insdate, @prevUserId := userid FROM usermst 
    ORDER BY insdate DESC, userid DESC LIMIT 7,1;

其中@prevInsDate和@prevUserId已声明为变量。