MySQL 5.6 排序数据丢失的诡异现象

[stevenkang]

按照第一条的 SQL 查询 4 条数据，ID 分别为 3、2、4、1

然后按照第 2、3 条 SQL 进行同样的排序条件以及分页查询，预期的结果应该是 3、2 和 4、1

但是执行结果如下

ID 为 2 的数据竟然不见了，目前暂时通过 date desc, id desc 两个组合排序解决了此问题，但是又带来了新的问题，这样排序会导致索引实效。

有其他小伙伴遇到此类情况了吗？欢迎分享解决方案。

[GTim]

楼主这是什么软件，语法高亮好有感觉

我猜呢，就是字段类型的问题，请注意 2 4 条数据的 date1 是一样，查询不保证返回顺序

[GTim]

解决办法吗？如果你不想用数据库多字段排序，反正每页也才几十条数据而已，先读出来再排一次序

好像，不知道，谁说的，有一条数据库铁律，就是任何非必要计算，都在应用层解决，而非数据库层解决

[thread2]

// LIMIT clause results in duplicate data across pages
https://bugs.mysql.com/bug.php?id=69732

[Troevil]

因为不稳定， 唯一排序字段 date1 出现一样的值，order by 不能确定就会出现顺序错乱 不同的机器，不同时间去执行都有可能出现不同的结果，解决方法就是让 order by 能够确定顺序 比如：order by date1，id

[mingyun]

貌似没什么办法，之前在阿里云论坛看到这个问题

[yangqi]

mysql select 是随机的。你这个 date1 相同，排序再加 Limit 就随机选择了。需要再加上 id 排序即可解决

[stevenkang]

@GTim 软件是 navicat for mysql。数据量少的情况下确实可以用应用层来解决，这里数据比较多。

@Troevil asc 排序没有这个问题。如果 order by 两个字段的话，会导致索引失效，这里比较麻烦。
@thread2 看来这个 BUG 一直在呀，只有用别的办法解决了

@yangqi 现在解决方案就是 date + id 一起排序，唯一缺点是会导致索引失效，真是蛋疼啊。

[aaronly]

不只是 mysql， 排序算法本身存在不稳定性, 提高稳定性的方法就是增加比较关键字
参考： https://en.wikipedia.org/wiki/Sorting_algorithm 章节：stability

[yangqi]

@stevenkang 这个很简答，再加一个索引不就行了，keyname (date, id) 或者 kename (date desc, id asc)

[mooncakejs]

索引可以复合解决

[ryuzaki113]

order by id

[Ravenddd]

limit 是根据行的序号去操作的, 当 order by 的字段存在重复的时候, 行数就会随机排序, 这时候用 limit 就会根据随机的行序号拿, 分页自然也会出现少了数据的假象, 加上主键排序这个问题就可以解决, 要保证排序的组合必定唯一
索引的话楼上的说了, 复合索引可以的