Mysql—详解排序和分页（order by & limit）及存在的坑（八）

今天和大家聊聊数据查询中常使用到的两个查询，排序和分页查询。

| 声明：本文中被[]包含的表示可选项，|符号分开的表示可选其一。

一、详解排序查询（order by）

电商中：我们想查看今天所有成交的订单，按照交易额从高到低排序，此时我们可以使用数据库中的排序功能来完成。

排序语法：

select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc]...;

说明：

• 需要排序的字段跟在 order by 之后；
• asc | desc 表示排序的规则。asc：升序；desc：降序。默认为desc降序
• 支持多个字段进行排序，多字段之间用 逗号 隔开。

1、单字段排序


2、多字段排序

3、按别名排序

4、按函数排序
有个学生表（id：学号，birth：出生日期，name：姓名），如下：

需求：按照出生年份升序、编号升序，查询出编号、出生日期、出生年份、姓名，2种写法，如下：

说明：

• year()函数：输入日期函数，可以获取对应日期中的年份；
• 上面使用了两种方式排序，第一张是在order by后面使用了函数；第二种使用了别名排序

5、where之后进行排序

有订单数据如下：

需求：查询订单金额 >= 100的，按照订单金额降序排序，显示2列数据，列头：订单编号、订单金额，如下：

二、详解分页查询（limit）

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

limit语法：

select 列 from 表名 limit [offset,] count;

说明：

• offset：表示 偏移量 ，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过多少行；范围：[0，+无穷大)；
• count：跳过offset行之后开始取数据，取count行记录；范围：[0，+无穷大);
• limit中offset和count的值不能用表达式。

下面我们列一些常用的示例来加深理解。

1、获取前n行记录

select 列 from 表 limit 0,n;
或者
select 列 from 表 limit n;

示例：获取订单前两条记录

2、获取最大的一条记录

我们需要获取订单金额最大的一条记录，可以这么做：先按照金额降序，然后取第一条记录即可，如下：

3、获取排名第n到m的记录

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

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

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

4、分页查询

开发过程中，分页是业务一个基础功能。所以我们经常会使用的到，分页一般有两个参数：

• page ：表示第几页，从1开始，范围：[1,+无穷大);
• pageSize：每页显示多少条记录，范围：[1,+无穷大);

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

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

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

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

三、常见的坑

1、limit中不能使用表达式

结论：limit后面只能跟明确的数字。

2、limit后面的2个数字不能为负数

3、排序分页存储的坑

（1）准备数据

（2）按照b升序，每页2条数据

下面的SQL以此为第1页、第2页、第3页、第4页和第5页的数据，如下：

上面有两个问题：

• 问题1：看一下第2个SQL和第3个SQL，分别是第2页和第3页的数据，结果出现了相同的数据，是不是懵逼了！！
• 整个表只有8条数据，怎么会出现第5页的数据呢？是不是又懵逼了！？

分析：主要是b字段存在相同的值，当排序过程中存在相同的值时,没有其他排序规则时，这下换成Mysql懵逼了，不知道如何排序了。
就像我们上学站队一样，按照身高排序，那身高一样的如何排序呢？身高一样的就乱排序了。

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


看上面的结果，分页数据都正常了，第五页也没有数据了。

总结：

• order by … [asc | desc] 用于对查询结果排序，asc：升序；desc：降序，asc|desc可以省略，默认为desc;
• limit用来限制查询结果返回的行数，有2个参数（offset，count），offset：表示跳过多少行，count：表示跳过offset行后去count行数据；
• limit中offset可以省略，默认值为0；
• limit中offset和count都必须大于等于0，offset范围：[0, +无穷大]；count范围：[0, +无穷大]；
• limit中offset和count的值不能用表达式；
• 分页排序时，排序不要有二义性，二义性情况下会导致分页结果乱序，可以在后面追加一个主键排序。