Mysql order by 乱序导致分页取值问题

背景：生产巡检发现第一页和第二页商品重复

分析后发现是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;

如图，排序和分页已正常。

---