今天写项目遇到一个关于limit offset的问题记录一下

  # limit 50
  select * from tb order by created_at desc limit 50 offset 0;
  select * from tb order by created_at desc limit 50 offset 50;
  select * from tb order by created_at desc limit 50 offset 100;
  select * from tb order by created_at desc limit 50 offset 150;
  select * from tb order by created_at desc limit 50 offset 200;
  # limit 100
  select * from tb order by created_at desc limit 100 offset 100;
  select * from tb order by created_at desc limit 200 offset 200;

这里遇到一个问题limit 50 和limit 100 取出的数据不一致、而且limit 50 offset xx 会取出原来重复的数据

解决办法

在order by created_at desc 后面加一个带顺序的索引例如id

  select * from tb order by created_at desc , id desc limit 50 offset 0;

具体原因【待证实】：
目前猜测是mysql 取出对应limit的数据后进行排序，由于created_at是秒单位，所以在同一时刻会有很多数据，mysql每次进行排序的时候就可能会出现重复的状况