背景:生产巡检发现第一页和第二页商品重复
分析后发现是sql语句中使用order by子句,但是排序字段的值都是一样的,导致排序后分页取值错乱。
复现:
建表语句:
create table test( id int, order1 int, order2 int, order3 int );
insert into test value (1,10,null,100);
insert into test value (2,10,null,100);
insert into test value (3,10,null,100);
insert into test value (4,10,null,100);
insert into test value (5,10,null,100);
insert into test value (6,10,null,100);
insert into test value (7,10,null,100);
insert into test value (8,10,null,100);
insert into test value (9,10,null,100);
insert into test value (10,10,null,100);
insert into test value (11,10,null,100);
insert into test value (12,10,null,100);
insert into test value (13,10,null,100);
insert into test value (14,10,null,100);
insert into test value (15,10,null,100);
insert into test value (16,10,null,100);
insert into test value (17,10,null,100);
insert into test value (18,10,null,100);
insert into test value (19,10,null,100);
insert into test value (20,10,null,100);
第一页数据: select * from test order by order1,order2,order3 desc limit 0,10;
第二页数据:
select * from test order by order1,order2,order3 desc limit 10,20;
由于排序字段值相同,此处排序后取分页数据已经发生错乱。
所以在使用排序取分页时,一定要注意确保有一个排序字段能唯一确定一条记录。
此处可以加上id进行排序,放在最后作为兜底的排序字段,结果如下:
select * from test order by order1,order2,order3 desc,id limit 0,10;
select * from tt order by order1,order2,order3 desc,id limit 10,20;
如图,排序和分页已正常。