背景
前不久在写一个分页接口的时候, 在测试阶段出现了排序结果紊乱且数据不正确的问题, 那个接口是按照 create_time 进行排序的, 但是对应的表中有很多相同 create_time 的数据, 最后发现是因为 order by 排序的时候, 如果排序字段中有多行相同的列值, 则排序结果是不确定的.
复现
创建一个简单表, 并插入一些数据
- MySQL> desc people;
- +-------------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +-------------+-------------+------+-----+---------+----------------+
- | id | bigint(20) | NO | PRI | NULL | auto_increment |
- | name | varchar(20) | NO | | NULL | |
- | create_time | bigint(20) | NO | | NULL | |
- +-------------+-------------+------+-----+---------+----------------+
3 行于数据集 (0.02 秒)
- MySQL> select * from people;
- +----+--------+-------------+
- | id | name | create_time |
- +----+--------+-------------+
| 1 | 张三 | 1 |
| 2 | 李四 | 2 |
| 3 | 王五 | 3 |
| 4 | 赵六 | 4 |
| 5 | 孙七 | 2 |
| 6 | 赵八 | 2 |
| 7 | 吴九 | 2 |
| 8 | 郑十 | 2 |
+----+--------+-------------+
8 行于数据集 (0.02 秒)
分页的写法
分页一般有 2 个参数: page: 表示第几页, 从 1 开始, 范围 [1,+∞)pageSize: 每页显示多少条记录, 范围 [1,+∞)
limit 分页公式
(1)limit 分页公式: curPage 是当前第几页; pageSize 是一页多少条记录
limit (curPage-1)*pageSize,pageSize(2) 用的地方: sql 语句中
select 列 from 表名 limit(curPage-1)*pageSize,pageSize;
查询复现
- MySQL> select * from people order by create_time asc limit 0,2;
- +----+--------+-------------+
- | id | name | create_time |
- +----+--------+-------------+
| 1 | 张三 | 1 |
| 2 | 李四 | 2 |
+----+--------+-------------+
2 行于数据集 (0.06 秒)
- MySQL> select * from people order by create_time asc limit 2,2;
- +----+--------+-------------+
- | id | name | create_time |
- +----+--------+-------------+
| 8 | 郑十 | 2 |
| 6 | 赵八 | 2 |
+----+--------+-------------+
2 行于数据集 (0.09 秒)
- MySQL> select * from people order by create_time asc limit 4,2;
- +----+--------+-------------+
- | id | name | create_time |
- +----+--------+-------------+
| 6 | 赵八 | 2 |
| 7 | 吴九 | 2 |
+----+--------+-------------+
2 行于数据集 (0.04 秒)
- MySQL> select * from people order by create_time asc limit 6,2;
- +----+--------+-------------+
- | id | name | create_time |
- +----+--------+-------------+
| 3 | 王五 | 3 |
| 4 | 赵六 | 4 |
+----+--------+-------------+
2 行于数据集 (0.05 秒)
排序字段出现重复数据, 这时可以加入第二个排序字段, 提高排序的唯一性,
- MySQL> select * from people order by create_time asc,id asc limit 0,2;
- +----+--------+-------------+
- | id | name | create_time |
- +----+--------+-------------+
| 1 | 张三 | 1 |
| 2 | 李四 | 2 |
+----+--------+-------------+
2 行于数据集 (0.05 秒)
- MySQL> select * from people order by create_time asc,id asc limit 2,2;
- +----+--------+-------------+
- | id | name | create_time |
- +----+--------+-------------+
| 5 | 孙七 | 2 |
| 6 | 赵八 | 2 |
+----+--------+-------------+
2 行于数据集 (0.10 秒)
- MySQL> select * from people order by create_time asc,id asc limit 4,2;
- +----+--------+-------------+
- | id | name | create_time |
- +----+--------+-------------+
| 7 | 吴九 | 2 |
| 8 | 郑十 | 2 |
+----+--------+-------------+
2 行于数据集 (0.05 秒)
- MySQL> select * from people order by create_time asc,id asc limit 6,2;
- +----+--------+-------------+
- | id | name | create_time |
- +----+--------+-------------+
| 3 | 王五 | 3 |
| 4 | 赵六 | 4 |
+----+--------+-------------+
2 行于数据集 (0.03 秒)
我们可以观察到第一次的查询中, 缺少了'孙七'的数据行, 当我们加上了第二个排序字段时分页数据变得正常了.
总结
MySQL 使用 limit 进行分页时, 可能会出现重复数据, 通过加入 order by 子句可以解决, 但是需要注意的是, 如果排序字段有相同值的情况下, 由于排序字段数据重复, 可能会导致每次查询排序后结果顺序不同, 分页还是会出现重复数据, 这时可以加入第二个排序字段, 提高排序的唯一性, 最好保证排序的字段在表中的值是唯一的, 这样就可以少写一个排序字段, 增加查询效率, 因为 order by 后面有多个排序字段时, 无法用到索引.
来源: http://database.51cto.com/art/202201/698523.htm