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

2023-07-15 20:18:03 183

排序查询（orderby）

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

排序语法：

select字段名from表名orderby字段1[asc|desc],字段2[asc|desc];

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

单字段排序

mysql>createtabletest2(aint,bvarchar(10));
QueryOK,0rowsaffected(0.01sec)
mysql>insertintotest2values(10,'jack'),(8,'tom'),(5,'ready'),(100,'javacode');
QueryOK,4rowsaffected(0.00sec)
Records:4Duplicates:0Warnings:0
mysql>select*fromtest2;
+------+----------+
|a|b|
+------+----------+
|10|jack|
|8|tom|
|5|ready|
|100|javacode|
+------+----------+
4rowsinset(0.00sec)
mysql>select*fromtest2orderbyaasc;
+------+----------+
|a|b|
+------+----------+
|5|ready|
|8|tom|
|10|jack|
|100|javacode|
+------+----------+
4rowsinset(0.00sec)
mysql>select*fromtest2orderbyadesc;
+------+----------+
|a|b|
+------+----------+
|100|javacode|
|10|jack|
|8|tom|
|5|ready|
+------+----------+
4rowsinset(0.00sec)
mysql>select*fromtest2orderbya;
+------+----------+
|a|b|
+------+----------+
|5|ready|
|8|tom|
|10|jack|
|100|javacode|
+------+----------+
4rowsinset(0.00sec)

多字段排序

比如学生表，先按学生年龄降序，年龄相同时，再按学号升序，如下：

mysql>createtablestu(idintnotnullcomment'学号'primarykey,agetinyintnotnullcomment'年龄',namevarchar(16)comment'姓名');
QueryOK,0rowsaffected(0.01sec)
mysql>insertintostu(id,age,name)values(1001,18,'路人甲Java'),(1005,20,'刘德华'),(1003,18,'张学友'),(1004,20,'张国荣'),(1010,19,'梁朝伟');
QueryOK,5rowsaffected(0.00sec)
Records:5Duplicates:0Warnings:0
mysql>select*fromstu;
+------+-----+---------------+
|id|age|name|
+------+-----+---------------+
|1001|18|路人甲Java|
|1003|18|张学友|
|1004|20|张国荣|
|1005|20|刘德华|
|1010|19|梁朝伟|
+------+-----+---------------+
5rowsinset(0.00sec)
mysql>select*fromstuorderbyagedesc,idasc;
+------+-----+---------------+
|id|age|name|
+------+-----+---------------+
|1004|20|张国荣|
|1005|20|刘德华|
|1010|19|梁朝伟|
|1001|18|路人甲Java|
|1003|18|张学友|
+------+-----+---------------+
5rowsinset(0.00sec)

按别名排序

mysql>select*fromstu;
+------+-----+---------------+
|id|age|name|
+------+-----+---------------+
|1001|18|路人甲Java|
|1003|18|张学友|
|1004|20|张国荣|
|1005|20|刘德华|
|1010|19|梁朝伟|
+------+-----+---------------+
5rowsinset(0.00sec)
mysql>selectage'年龄',idas'学号'fromstuorderby年龄asc,学号desc;
+--------+--------+
|年龄|学号|
+--------+--------+
|18|1003|
|18|1001|
|19|1010|
|20|1005|
|20|1004|
+--------+--------+

按函数排序

有学生表（id：编号，birth：出生日期，name：姓名），如下：

mysql>droptableifexistsstudent;
QueryOK,0rowsaffected(0.01sec)
mysql>CREATETABLEstudent(
->idint(11)NOTNULLCOMMENT'学号',
->birthdateNOTNULLCOMMENT'出生日期',
->namevarchar(16)DEFAULTNULLCOMMENT'姓名',
->PRIMARYKEY(id)
->);
QueryOK,0rowsaffected(0.01sec)
mysql>insertintostudent(id,birth,name)values(1001,'1990-10-10','路人甲Java'),(1005,'1960-03-01','刘德华'),(1003,'1960-08-16','张学友'),(1004,'1968-07-01','张国荣'),(1010,'1962-05-16','梁朝伟');
QueryOK,5rowsaffected(0.00sec)
Records:5Duplicates:0Warnings:0
mysql>
mysql>SELECT*FROMstudent;
+------+------------+---------------+
|id|birth|name|
+------+------------+---------------+
|1001|1990-10-10|路人甲Java|
|1003|1960-08-16|张学友|
|1004|1968-07-01|张国荣|
|1005|1960-03-01|刘德华|
|1010|1962-05-16|梁朝伟|
+------+------------+---------------+
5rowsinset(0.00sec)

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

mysql>SELECTid编号,birth出生日期,year(birth)出生年份,name姓名fromstudentORDERBYyear(birth)asc,idasc;
+--------+--------------+--------------+---------------+
|编号|出生日期|出生年份|姓名|
+--------+--------------+--------------+---------------+
|1003|1960-08-16|1960|张学友|
|1005|1960-03-01|1960|刘德华|
|1010|1962-05-16|1962|梁朝伟|
|1004|1968-07-01|1968|张国荣|
|1001|1990-10-10|1990|路人甲Java|
+--------+--------------+--------------+---------------+
5rowsinset(0.00sec)
mysql>SELECTid编号,birth出生日期,year(birth)出生年份,name姓名fromstudentORDERBY出生年份asc,idasc;
+--------+--------------+--------------+---------------+
|编号|出生日期|出生年份|姓名|
+--------+--------------+--------------+---------------+
|1003|1960-08-16|1960|张学友|
|1005|1960-03-01|1960|刘德华|
|1010|1962-05-16|1962|梁朝伟|
|1004|1968-07-01|1968|张国荣|
|1001|1990-10-10|1990|路人甲Java|
+--------+--------------+--------------+---------------+
5rowsinset(0.00sec)

说明：
year函数：属于日期函数，可以获取对应日期中的年份。
上面使用了2种方式排序，第一种是在orderby中使用了函数，第二种是使用了别名排序。

where之后进行排序

有订单数据如下：

mysql>droptableifexistst_order;
QueryOK,0rowsaffected,1warning(0.00sec)
mysql>createtablet_order(
->idintnotnullauto_incrementcomment'订单编号',
->pricedecimal(10,2)notnulldefault0comment'订单金额',
->primarykey(id)
->)comment'订单表';
QueryOK,0rowsaffected(0.01sec)
mysql>insertintot_order(price)values(88.95),(100.68),(500),(300),(20.88),(200.5);
QueryOK,6rowsaffected(0.00sec)
Records:6Duplicates:0Warnings:0
mysql>select*fromt_order;
+----+--------+
|id|price|
+----+--------+
|1|88.95|
|2|100.68|
|3|500.00|
|4|300.00|
|5|20.88|
|6|200.50|
+----+--------+
6rowsinset(0.00sec)

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

mysql>selecta.id订单编号,a.price订单金额fromt_orderawherea.price>=100orderbya.pricedesc;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|3|500.00|
|4|300.00|
|6|200.50|
|2|100.68|
+--------------+--------------+
4rowsinset(0.00sec)

limit介绍

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

语法：

select列from表limit[offset,]count;

说明：

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

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

获取前n行记录

select列from表limit0,n;
或者
select列from表limitn;

示例，获取订单的前2条记录，如下：

mysql>createtablet_order(
->idintnotnullauto_incrementcomment'订单编号',
->pricedecimal(10,2)notnulldefault0comment'订单金额',
->primarykey(id)
->)comment'订单表';
QueryOK,0rowsaffected(0.01sec)
mysql>insertintot_order(price)values(88.95),(100.68),(500),(300),(20.88),(200.5);
QueryOK,6rowsaffected(0.01sec)
Records:6Duplicates:0Warnings:0
mysql>select*fromt_order;
+----+--------+
|id|price|
+----+--------+
|1|88.95|
|2|100.68|
|3|500.00|
|4|300.00|
|5|20.88|
|6|200.50|
+----+--------+
6rowsinset(0.00sec)
mysql>selecta.id订单编号,a.price订单金额fromt_orderalimit2;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|1|88.95|
|2|100.68|
+--------------+--------------+
2rowsinset(0.00sec)
mysql>selecta.id订单编号,a.price订单金额fromt_orderalimit0,2;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|1|88.95|
|2|100.68|
+--------------+--------------+
2rowsinset(0.00sec)

获取最大的一条记录

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

mysql>selecta.id订单编号,a.price订单金额fromt_orderaorderbya.pricedesc;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|3|500.00|
|4|300.00|
|6|200.50|
|2|100.68|
|1|88.95|
|5|20.88|
+--------------+--------------+
6rowsinset(0.00sec)
mysql>selecta.id订单编号,a.price订单金额fromt_orderaorderbya.pricedesclimit1;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|3|500.00|
+--------------+--------------+
1rowinset(0.00sec)
mysql>selecta.id订单编号,a.price订单金额fromt_orderaorderbya.pricedesclimit0,1;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|3|500.00|
+--------------+--------------+
1rowinset(0.00sec)

获取排名第n到m的记录

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

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

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

mysql>selecta.id订单编号,a.price订单金额fromt_orderaorderbya.pricedesc;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|3|500.00|
|4|300.00|
|6|200.50|
|2|100.68|
|1|88.95|
|5|20.88|
+--------------+--------------+
6rowsinset(0.00sec)
mysql>selecta.id订单编号,a.price订单金额fromt_orderaorderbya.pricedesclimit2,3;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|6|200.50|
|2|100.68|
|1|88.95|
+--------------+--------------+
3rowsinset(0.00sec)

分页查询

开发过程中，分页我们经常使用，分页一般有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>selecta.id订单编号,a.price订单金额fromt_orderaorderbya.pricedesc;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|3|500.00|
|4|300.00|
|6|200.50|
|2|100.68|
|1|88.95|
|5|20.88|
+--------------+--------------+
6rowsinset(0.00sec)
mysql>selecta.id订单编号,a.price订单金额fromt_orderaorderbya.pricedesclimit0,2;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|3|500.00|
|4|300.00|
+--------------+--------------+
2rowsinset(0.00sec)
mysql>selecta.id订单编号,a.price订单金额fromt_orderaorderbya.pricedesclimit2,2;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|6|200.50|
|2|100.68|
+--------------+--------------+
2rowsinset(0.00sec)
mysql>selecta.id订单编号,a.price订单金额fromt_orderaorderbya.pricedesclimit4,2;
+--------------+--------------+
|订单编号|订单金额|
+--------------+--------------+
|1|88.95|
|5|20.88|
+--------------+--------------+
2rowsinset(0.00sec)

避免踩坑

limit中不能使用表达式

mysql>select*fromt_orderwherelimit1,4+1;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'limit1,4+1'atline1
mysql>select*fromt_orderwherelimit1+0;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'limit1+0'atline1
mysql>

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

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

mysql>select*fromt_orderwherelimit-1;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'limit-1'atline1
mysql>select*fromt_orderwherelimit0,-1;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'limit0,-1'atline1
mysql>select*fromt_orderwherelimit-1,-1;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'limit-1,-1'atline1

排序分页存在的坑

准备数据：

mysql>insertintotest1(b)values(1),(2),(3),(4),(2),(2),(2),(2);
QueryOK,8rowsaffected(0.01sec)
Records:8Duplicates:0Warnings:0
mysql>select*fromtest1;
+---+---+
|a|b|
+---+---+
|1|1|
|2|2|
|3|3|
|4|4|
|5|2|
|6|2|
|7|2|
|8|2|
+---+---+
8rowsinset(0.00sec)
mysql>select*fromtest1orderbybasc;
+---+---+
|a|b|
+---+---+
|1|1|
|2|2|
|5|2|
|6|2|
|7|2|
|8|2|
|3|3|
|4|4|
+---+---+
8rowsinset(0.00sec)

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

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

mysql>select*fromtest1orderbybasclimit0,2;
+---+---+
|a|b|
+---+---+
|1|1|
|2|2|
+---+---+
2rowsinset(0.00sec)
mysql>select*fromtest1orderbybasclimit2,2;
+---+---+
|a|b|
+---+---+
|8|2|
|6|2|
+---+---+
2rowsinset(0.00sec)
mysql>select*fromtest1orderbybasclimit4,2;
+---+---+
|a|b|
+---+---+
|6|2|
|7|2|
+---+---+
2rowsinset(0.00sec)
mysql>select*fromtest1orderbybasclimit6,2;
+---+---+
|a|b|
+---+---+
|3|3|
|4|4|
+---+---+
2rowsinset(0.00sec)
mysql>select*fromtest1orderbybasclimit7,2;
+---+---+
|a|b|
+---+---+
|4|4|
+---+---+
1rowinset(0.00sec)

上面有2个问题：

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

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

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

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

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

mysql>select*fromtest1orderbybasc,adesc;
+---+---+
|a|b|
+---+---+
|1|1|
|8|2|
|7|2|
|6|2|
|5|2|
|2|2|
|3|3|
|4|4|
+---+---+
8rowsinset(0.00sec)
mysql>select*fromtest1orderbybasc,adesclimit0,2;
+---+---+
|a|b|
+---+---+
|1|1|
|8|2|
+---+---+
2rowsinset(0.00sec)
mysql>select*fromtest1orderbybasc,adesclimit2,2;
+---+---+
|a|b|
+---+---+
|7|2|
|6|2|
+---+---+
2rowsinset(0.00sec)
mysql>select*fromtest1orderbybasc,adesclimit4,2;
+---+---+
|a|b|
+---+---+
|5|2|
|2|2|
+---+---+
2rowsinset(0.00sec)
mysql>select*fromtest1orderbybasc,adesclimit6,2;
+---+---+
|a|b|
+---+---+
|3|3|
|4|4|
+---+---+
2rowsinset(0.00sec)
mysql>select*fromtest1orderbybasc,adesclimit8,2;
Emptyset(0.00sec)

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

总结

orderby…[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的值不能用表达式
分页排序时，排序不要有二义性，二义性情况下可能会导致分页结果乱序，可以在后面追加一个主键排序

到此这篇关于Mysql排序和分页(orderby&limit)及存在的坑的文章就介绍到这了,更多相关Mysql排序和分页内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

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

热门推荐

随机推荐