详解排序和分页(order by & limit)，及存在的坑

最新推荐文章于 2023-08-25 09:29:22 发布
最新推荐文章于 2023-08-25 09:29:22 发布
阅读量2.9k 收藏 3
点赞数
分类专栏： mysql
原文链接：https://blog.csdn.net/likun557/article/details/100789107
版权

limit介绍
limit用来限制select查询返回的行数，常用于分页等操作。

语法：

select 列 from 表 limit [offset,] count;

说明：

offset：表示偏移量，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过0行；范围：[0,+∞)。

count：跳过offset行之后开始取数据，取count行记录；范围：[0,+∞)。

limit中offset和count的值不能用表达式。

获取排名第n到m的记录

我们需要先跳过n-1条记录，然后取m-n+1条记录，如下：

select 列 from 表 limit n-1,m-n+1;

如：我们想获取订单金额最高的3到5名的记录，我们需要跳过2条，然后获取3条记录，如下：


mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            3 |       500.00 |
|            4 |       300.00 |
|            6 |       200.50 |
|            2 |       100.68 |
|            1 |        88.95 |
|            5 |        20.88 |
+--------------+--------------+
6 rows in set (0.00 sec)
 
mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc limit 2,3;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            6 |       200.50 |
|            2 |       100.68 |
|            1 |        88.95 |
+--------------+--------------+
3 rows in set (0.00 sec

分页查询

开发过程中，分页我们经常使用，分页一般有2个参数：

page：表示第几页，从1开始，范围[1,+∞)

pageSize：每页显示多少条记录，范围[1,+∞)

如：page = 2，pageSize = 10，表示获取第2页10条数据。

我们使用limit实现分页，语法如下：

select 列 from 表名 limit (page - 1) * pageSize,pageSize;

需求：我们按照订单金额降序，每页显示2条，依次获取所有订单数据、第1页、第2页、第3页数据，如下：

mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            3 |       500.00 |
|            4 |       300.00 |
|            6 |       200.50 |
|            2 |       100.68 |
|            1 |        88.95 |
|            5 |        20.88 |
+--------------+--------------+
6 rows in set (0.00 sec)
 
mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc limit 0,2;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            3 |       500.00 |
|            4 |       300.00 |
+--------------+--------------+
2 rows in set (0.00 sec)
 
mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc limit 2,2;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            6 |       200.50 |
|            2 |       100.68 |
+--------------+--------------+
2 rows in set (0.00 sec)
 
mysql> select a.id 订单编号,a.price 订单金额 from t_order a order by a.price desc limit 4,2;
+--------------+--------------+
| 订单编号     | 订单金额     |
+--------------+--------------+
|            1 |        88.95 |
|            5 |        20.88 |
+--------------+--------------+
2 rows in set (0.00 sec)

排序分页存在的坑

准备数据：

mysql> insert into test1 (b) values (1),(2),(3),(4),(2),(2),(2),(2);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0
 
mysql> select * from test1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
| 8 | 2 |
+---+---+
8 rows in set (0.00 sec)
 
mysql> select * from test1 order by b asc;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
| 8 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
8 rows in set (0.00 sec)

下面我们按照b升序，每页2条数据，来获取数据。

下面的sql依次为第1页、第2页、第3页、第4页、第5页的数据，如下：

mysql> select * from test1 order by b asc limit 0,2;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+
2 rows in set (0.00 sec)
 
mysql> select * from test1 order by b asc limit 2,2;
+---+---+
| a | b |
+---+---+
| 8 | 2 |
| 6 | 2 |
+---+---+
2 rows in set (0.00 sec)
 
mysql> select * from test1 order by b asc limit 4,2;
+---+---+
| a | b |
+---+---+
| 6 | 2 |
| 7 | 2 |
+---+---+
2 rows in set (0.00 sec)
 
mysql> select * from test1 order by b asc limit 6,2;
+---+---+
| a | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+
2 rows in set (0.00 sec)
 
mysql> select * from test1 order by b asc limit 7,2;
+---+---+
| a | b |
+---+---+
| 4 | 4 |
+---+---+
1 row in set (0.00 sec)

上面有2个问题：

问题1：看一下第2个sql和第3个sql，分别是第2页和第3页的数据，结果出现了相同的数据，是不是懵逼了。

问题2：整个表只有8条记录，怎么会出现第5页的数据呢，又懵逼了。

我们来分析一下上面的原因：主要是b字段存在相同的值，当排序过程中存在相同的值时，没有其他排序规则时，mysql懵逼了，不知道怎么排序了。

就像我们上学站队一样，按照身高排序，那身高一样的时候如何排序呢？身高一样的就乱排了。

建议：排序中存在相同的值时，需要再指定一个排序规则，通过这种排序规则不存在二义性，比如上面可以再加上a降序，如下：

mysql> select * from test1 order by b asc,a desc;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 8 |    2 |
| 7 |    2 |
| 6 |    2 |
| 5 |    2 |
| 2 |    2 |
| 3 |    3 |
| 4 |    4 |
+---+------+
8 rows in set (0.00 sec)

mysql> select * from test1 order by b asc,a desc limit 0,2;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 8 |    2 |
+---+------+
2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc,a desc limit 1,2;
+---+------+
| a | b    |
+---+------+
| 8 |    2 |
| 7 |    2 |
+---+------+
2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc,a desc limit 2,2;
+---+------+
| a | b    |
+---+------+
| 7 |    2 |
| 6 |    2 |
+---+------+
2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc,a desc limit 3,2;
+---+------+
| a | b    |
+---+------+
| 6 |    2 |
| 5 |    2 |
+---+------+
2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc,a desc limit 4,2;
+---+------+
| a | b    |
+---+------+
| 5 |    2 |
| 2 |    2 |
+---+------+
2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc,a desc limit 6,2;
+---+------+
| a | b    |
+---+------+
| 3 |    3 |
| 4 |    4 |
+---+------+
2 rows in set (0.00 sec)

mysql> select * from test1 order by b asc,a desc limit 7,2;
+---+------+
| a | b    |
+---+------+
| 4 |    4 |
+---+------+
1 row in set (0.00 sec)

mysql> select * from test1 order by b asc,a desc limit 8,2;
Empty set (0.00 sec)

总结
order by … [asc|desc]用于对查询结果排序，asc：升序，desc：降序，asc|desc可以省略，默认为asc

limit用来限制查询结果返回的行数，有2个参数（offset，count），offset：表示跳过多少行，count：表示跳过offset行之后取count行

limit中offset可以省略，默认值为0
limit中offset 和 count都必须大于等于0
limit中offset和count的值不能用表达式
分页排序时，排序不要有二义性，二义性情况下可能会导致分页结果乱序，可以在后面追加一个主键排序

参考：https://blog.csdn.net/likun557/article/details/100789107

hellenlee22
关注
专栏目录
MySQL排序、分组和分页
New_boy25的博客
12-16 1227
MySQL排序和分组使用的命令 排序 order by 一、 order by 单字段 ——默认从小到大排序，字段后面隐藏了asc条件，当条件为desc时，按从大到小排序。 示例1：查询年龄在18到34岁之间的男性，按照年龄从小到大排序： select * from student where (age between 18 and 34) and (gender="男") order by ag...
玩转Mysql系列 - 第8篇：详解排序和分页(order by & limit)，及存在的坑(1)
最新发布
Y1567409的博客
04-22 591
limit用来限制select查询返回的行数，常用于分页等操作。select 列 from 表 limit [offset,] count;说明：offset：表示偏移量，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过0行；范围：[0,+∞)。count：跳过offset行之后开始取数据，取count行记录；范围：[0,+∞)。limit中offset和count的值不能用表达式。下面我们列一些常用的示例来加深理解。获取前n行记录。
排序和分页(order by & limit)
Karin的博客
01-13 450
排序: asc升序 desc降序 order by 通过哪个字段排序 mysql> select a.id,a.name,subjectName,studentResult -> from student a -> inner join subject b -> on a.id =b.id -> inner join result c -> on b.id =c.id -> where subject...
MySQL学习之《排序与分页》
一个野生自学的运维狗
06-08 638
升序：ASC (ascend) 降序：DESC (descend)练习：按照salary从高到低的顺序显示员工信息 1.排序 1.1.基础用法 1.2.我们可以使用列的别名，进行排序 1.3.强调格式:WHERE 需要声明在FROM后，ORDER BY之前。1.4.二级排序 2.分页 2.1 mysql使用limit实现数据的分页显示 2.2 WHERE ... ORDER BY ...LIMIT声明顺序如下:2.3 MySQL8.0新特性 列的别名只能在 ORDER BY 中使用，不能在WHERE中使用。
玩转Mysql系列 - 第8篇：详解排序和分页(order by & limit)，及存在的坑
azhou的代码园
08-25 823
limit用来限制select查询返回的行数，常用于分页等操作。select 列 from 表 limit [offset,] count;说明：offset：表示偏移量，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过0行；范围：[0,+∞)。count：跳过offset行之后开始取数据，取count行记录；范围：[0,+∞)。limit中offset和count的值不能用表达式。下面我们列一些常用的示例来加深理解。
mysql 分页数据异常出现重复数据 order by limit 混合使用的坑
龚清林的博客
01-07 575
比如 表数据： 第一页，查询3条数据 SELECT * FROM test ORDER BY sort asc LIMIT 0,3 查询结果： 第二页，查询3条数据 SELECT * FROM test ORDER BY sort asc LIMIT 3,3 结果：老张重复出现了 结论：order by的列有相同的值时，mysql会随机选取这些行 解决方案：再加上主键id排序，确保顺序问题 SELECT * FROM test ORDER BY sort asc,id asc LIMIT 0,3
mysql排序后再分页_Mysql高手系列 - 第8篇：详解排序和分页(order by & limit)，及存在的坑...
weixin_30587341的博客
01-18 1649
这是Mysql系列第8篇。环境：mysql5.7.25，cmd命令中进行演示。代码中被[]包含的表示可选，|符号分开的表示可选其一。本章内容详解排序查询详解limitlimit存在的坑分页查询中的坑排序查询(order by)电商中：我们想查看今天所有成交的订单，按照交易额从高到低排序，此时我们可以使用数据库中的排序功能来完成。排序语法：select 字段名 from 表名 order by 字段...
MySQL Limit性能优化及分页数据性能优化详解
09-09
以下是对`LIMIT`性能优化及分页数据性能优化的详细解释： 首先，理解`LIMIT`的基本语法： ```sql SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset ``` 这里的`offset`是跳过的行数，`rows`是返回的...
详解MySQL的limit用法和分页查询语句的性能分析
09-09
例如，`SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10`将返回`category_id`为123的所有文章中，id排序后的第51到60条记录。 然而，随着数据量的增长，简单的`LIMIT`分页可能会变得效率...
MySQL数据库：排序及limit的使用
Globalizationa的博客
12-08 3621
排序 排序方式： 升序——asc（默认：从小到大） 降序——desc（由大到小） # 排序语法： order by 字段1 [asc]|desc[,字段2 [adc]|desc,……] limit # limit 和排序结合起来用，取前面的多少条数据 # 在limit后面只有一个数字时，表示在结果集中取前m条数据 # 在limit后面有两个数字时，表示在结果集中取m+1开始取n条数据 limit m[,n] select * form teachers order by tage desc li...
【MySql】二、排序与分页
weixin_41365204的博客
01-12 102
使用ORDER BY进行排序默认升序多级排序时：将先排序的写在前面，按照前一步的基础排序的写在后面就可以了。举例：在先部门升序的情况下，进行工资降序的排列。
04-排序和分页
qq_54591711的博客
07-22 151
排序和分页
postgresql中的order by、limit、offset偏移
10-18 4210
首先创建users表 create table users( id serial primary key, player varchar(255) not null, score real, team varchar(255) ) 插入记录并select insert into users(player,score,team) values ('库里',28.3,'勇士'), ('哈登',30.2,'火箭'), ('阿杜',25.6,'勇士'), ('阿詹',27.8,'骑士.
9.分页和排序
yuan_boss的博客
08-07 265
排序和分页,分组过滤 文章目录排序和分页,分组过滤排序分页分组过滤 排序 升序：ASC 降序：DESC SELECT s.studentNO,studentName,subjectName,studentResult FROM student s INNER JOIN result r ON s.studentNO = r.studentNO INNER JOIN subject sub ON r.subjectNO = sub.subjectNO where subjectName = '数据结构-1'
MySQL查询优化之order by 、 group by与分页查询优化
小小默：进无止境
09-07 3410
order by 子句尽量使用index方式排序(即using index)，避免使用filesort方式排序(即using filesort)。Index方式效率高，它指MySQL扫描索引本身完成排序，filesort则效率低。 **order by满足两种情况，会使用 index 方式排序：** * order by语句使用索引最左前列(最左匹配法则) * where子句和order by子句条件列组合满足最左匹配法则 下面给出几个实例来说明，如下所示我们创建表并为其创建组合索引(c1,c2,c3)
MySQL:关于排序order by limit值不稳定的说明（1）
老叶茶馆
12-26 6558
 导 读作者：高鹏（重庆八怪）原文地址：https://www.jianshu.com/p/8c2154872f83本系列文章将持续更新，欢迎关注~作者按：如果有误请谅解...
mysql同时使用order by和limit查询时的一个严重隐患 -- 丢失数据
热门推荐
肖武的专栏
04-11 3万+
我经常使用order by和limit来做数据分页显示并排序，一直也没发现过什么问题。但这两天缺遇到一个严重的问题，在按时间戳升序排列并用limit分批读取数据时，却发现在某些记录丢失了，表中明明有的记录确死活读取不到。研究了大半天终于发现了问题所在，记录一下以防忘记，也是给大家提个醒。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值