mysql排序问题--某些记录重复出现在不同页中

Question

偶然发现的mysql排序问题 如有如下的这么一张表

select * from t;
+----+------+-------+---------------------+
| id | name | price | create_time         |
+----+------+-------+---------------------+
|  1 | a    |     1 | 2016-03-16 14:15:37 |
|  2 | b    |     1 | 2016-03-16 14:15:42 |
|  3 | c    |     1 | 2016-03-16 14:15:45 |
|  4 | d    |     1 | 2016-03-16 14:15:47 |
|  5 | e    |     1 | 2016-03-16 14:15:51 |
|  6 | f    |     1 | 2016-03-16 14:15:54 |
|  7 | g    |     1 | 2016-03-16 14:15:56 |
|  8 | h    |     1 | 2016-03-16 14:15:59 |
|  9 | i    |     1 | 2016-03-16 14:16:04 |
| 10 | j    |     1 | 2016-03-16 14:16:07 |
| 11 | k    |     1 | 2016-03-16 14:16:11 |
+----+------+-------+---------------------+
#第一页
select * from t order by price limit 0,5;
+----+------+-------+---------------------+
| id | name | price | create_time         |
+----+------+-------+---------------------+
| 11 | k    |     1 | 2016-03-16 14:16:11 |
|  2 | b    |     1 | 2016-03-16 14:15:42 |
|  3 | c    |     1 | 2016-03-16 14:15:45 |
|  4 | d    |     1 | 2016-03-16 14:15:47 |
|  5 | e    |     1 | 2016-03-16 14:15:51 |
+----+------+-------+---------------------+
#第二页 b、c、d、e 在第二页中重复出现了
select * from t order by price limit 5,5;
+----+------+-------+---------------------+
| id | name | price | create_time         |
+----+------+-------+---------------------+
|  6 | f    |     1 | 2016-03-16 14:15:54 |
|  5 | e    |     1 | 2016-03-16 14:15:51 |
|  4 | d    |     1 | 2016-03-16 14:15:47 |
|  3 | c    |     1 | 2016-03-16 14:15:45 |
|  2 | b    |     1 | 2016-03-16 14:15:42 |
+----+------+-------+---------------------+

似乎按照某一字段排序， 且许多记录存在相同的取值的话， mysql似乎会随机排序， 于是就会出现上述现象， 某些记录重复出现在不同页中。

Answer 1

是的，mysql对于有重复值的排序，不保证每次返回的结果是相同的，

如果需要每次返回结果相同，请order by 多列，如order by price,id

Answer 2

感谢楼主的分享，平常真还没注意到，这样的话只能order by多个条件试试了。