实习期间遇到的一个问题,在使用Mysql ORDER BY进行排序并分页的时候,很奇怪的发现会有重复的数据,经过排查后,发现是由于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,11;
查找结果为:
在第二页查询的时候发现出现了顺序错乱的问题。原因是ORDRY BY字段的值不是唯一的,会导致排序错乱,遇到这种情况我们可以再加上一个字段去排序,这里我使用的是主键id
查询第一页:
select * from test order by order1,order2,order3 desc,id limit 10 OFFSET 0
结果:
查询第二页
select * from test order by order1,order2,order3 desc,id limit 10 OFFSET 10
结果: