mysql limit 分页数据丢失问题测试

背景

前几天看到有说mysql使用 limit 0,10 这种方式分页会丢失数据，有人质疑说不会，动手验证一下。

操作步骤

表结构如下：

create table `test`.`t_model`( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键', `uid` bigint COMMENT '业务主键', `modelid` varchar(50) COMMENT '字符主键', `modelname` varchar(50) COMMENT '名称', `desc` varchar(50) COMMENT '描述', primary key (`id`), UNIQUE index `uid_unique` (`uid`), key `modelid_index` (`modelid`) USING BTREE ) ENGINE=InnoDB charset=utf8 collate=utf8_bin;

构造测试数据语法如下：

drop procedure IF EXISTS u_head_and_low_pro; delimiter $$ create procedure u_head_and_low_pro() begin DECLARE n int DEFAULT 1; set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values '; set @exedata = ''; WHILE n < 10001 DO set @exedata = concat(@exedata,"(",n,",","'id20170831",n,"','","name",n,"','","desc'",")"); if n % 1000 = 0 then set @exesql = concat(@exesql,@exedata,";"); prepare stmt from @exesql; execute stmt; DEALLOCATE prepare stmt; commit; set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values '; set @exedata = ""; else set @exedata = concat(@exedata,','); end if; set n = n + 1; END WHILE; end;$$ delimiter ;

查询语法如下：

-- 第一页 select * from t_model limit 0, 10; -- 第二页 select * from t_model limit 10, 10; -- 第三页 select * from t_model limit 20, 10; -- 第四页 select * from t_model limit 30, 10; -- 第五页 select * from t_model limit 40, 10;

在查询时，第一页是1~10，第二页是11~20，第四页时，查询结果为： id uid modelid modelname desc

---

31 31 id2017083131 name31 desc 32 32 id2017083132 name32 desc 33 33 id2017083133 name33 desc 34 34 id2017083134 name34 desc 35 35 id2017083135 name35 desc 36 36 id2017083136 name36 desc 37 37 id2017083137 name37 desc 38 38 id2017083138 name38 desc 39 39 id2017083139 name39 desc 40 40 id2017083140 name40 desc

此时，查询第5页应该是 41~50。如果某人A在界面上查询数据，现在查询到第4页，在显示完第4页数据后，另外一个人B（或程序）将第11~20的数据删除掉了，A再翻页到第5页时，此时数据为： id uid modelid modelname desc

---

51 51 id2017083151 name51 desc 52 52 id2017083152 name52 desc 53 53 id2017083153 name53 desc 54 54 id2017083154 name54 desc 55 55 id2017083155 name55 desc 56 56 id2017083156 name56 desc 57 57 id2017083157 name57 desc 58 58 id2017083158 name58 desc 59 59 id2017083159 name59 desc 60 60 id2017083160 name60 desc

也就是说，A在本次操作过程中，如果一直往下翻页到最后一页的话，41~50 这页的数据就无法看到，数据丢失了。 当然，如果刷新一下界面，重新翻页就没有问题了。

结论

如果分页查询的表的数据插入和删除等操作频繁的话，建议还是使用 limit 10 这种语法方式分页查询，可以提升性能，也更准确。