MySQL中的limit及其灵活使用

原创巩飞 2020-02-14

660

在实际业务中，常常有需要返回特定部分数据的场景，比如

找出工资最高的前两名同事
找出公司第二名雇佣的员工
…

Oracle通过子句order by配合rownum实现，SQL Server有top，MySQL就是通过limit了。我们先来看看limit的语法：

SELECT ... [LIMIT {[offset,] row_count | row_count OFFSET offset}]

其中offset表示记录的起始偏移量，row_count表示显示的行数。默认情况下，起始偏移量为0，只需要写记录行数就行，这时，实际显示的就是前n条记录。

例如，找出工资最高的前两名同事

root@database-one 21:21:  [gftest]> select * from emp;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
+--------+------+---------+------------+--------+
5 rows in set (0.01 sec)

root@database-one 21:21:  [gftest]> select * from emp order by sal desc limit 2;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
+--------+------+---------+------------+--------+
2 rows in set (0.00 sec)

找出公司第二名雇佣的员工

root@database-one 21:21:  [gftest]> select * from emp order by hiredate;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
+--------+------+---------+------------+--------+
5 rows in set (0.00 sec)

root@database-one 21:22:  [gftest]> select * from emp order by hiredate limit 1,1;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
+--------+------+---------+------------+--------+
1 row in set (0.00 sec)

在软件开发领域，limit还有个非常多的使用场景，就是和order by配合起来进行分页。
比如一个表中有很多数据，软件页面显示时只显示10条数据，当用户点击下一页时再显示接下来的10条数据，跳转到特定的页只显示那个页对应的数据。

我们先构造一张新表emp_copy，放些数据

root@database-one 22:00:  [gftest]> 
root@database-one 22:00:  [gftest]> CREATE TABLE `emp_copy` (
    ->  `eno` int auto_increment not null,
    ->  `ename` varchar(10) DEFAULT NULL,
    ->  `age` int(11) DEFAULT NULL,
    ->  `sal` decimal(10,2) DEFAULT NULL,
    ->  `hiredate` date DEFAULT NULL,
    ->  `deptno` int(2) DEFAULT NULL,
    ->  primary key(eno)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    ->  ;
Query OK, 0 rows affected (3.01 sec)

root@database-one 22:00:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select * from emp;
Query OK, 5 rows affected (1.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@database-one 22:00:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 5 rows affected (0.23 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@database-one 22:01:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 10 rows affected (0.20 sec)
Records: 10  Duplicates: 0  Warnings: 0

root@database-one 22:01:  [gftest]> insert into emp_copy(ename,age,sal,hiredate,deptno) select ename,age,sal,hiredate,deptno from emp_copy;
Query OK, 20 rows affected (0.03 sec)
Records: 20  Duplicates: 0  Warnings: 0

root@database-one 22:01:  [gftest]> commit;
Query OK, 0 rows affected (0.00 sec)

root@database-one 22:01:  [gftest]> select * from emp_copy;
+-----+--------+------+---------+------------+--------+
| eno | ename  | age  | sal     | hiredate   | deptno |
+-----+--------+------+---------+------------+--------+
|   1 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|   2 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|   3 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|   4 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|   5 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|   8 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|   9 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  10 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  11 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  12 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  15 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|  16 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  17 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  18 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  19 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  20 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|  21 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  22 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  23 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  24 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  30 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|  31 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  32 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  33 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  34 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  35 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|  36 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  37 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  38 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  39 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  40 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|  41 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  42 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  43 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  44 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  45 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|  46 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  47 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  48 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  49 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
+-----+--------+------+---------+------------+--------+
40 rows in set (0.02 sec)

root@database-one 22:01:  [gftest]>

细心的同学可能注意到自增列的序号有跳号，这个跟MySQL的原理有关，是因为批量插入数据造成的，后面再抽时间详细说明。

按eno降序分页显示数据

select * from emp_copy order by eno desc limit (N-1)*M,M;
N代表页数，M代表每页显示的记录数

假设10行为一页，显示第1页

root@database-one 22:06:  [gftest]> select * from emp_copy order by eno desc limit 0,10;
+-----+--------+------+---------+------------+--------+
| eno | ename  | age  | sal     | hiredate   | deptno |
+-----+--------+------+---------+------------+--------+
|  49 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  48 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  47 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  46 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  45 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|  44 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  43 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  42 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  41 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  40 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
+-----+--------+------+---------+------------+--------+
10 rows in set (0.01 sec)

显示第2页

root@database-one 22:12:  [gftest]> select * from emp_copy order by eno desc limit 10,10;
+-----+--------+------+---------+------------+--------+
| eno | ename  | age  | sal     | hiredate   | deptno |
+-----+--------+------+---------+------------+--------+
|  39 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  38 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  37 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  36 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  35 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|  34 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  33 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  32 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|  31 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|  30 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
+-----+--------+------+---------+------------+--------+
10 rows in set (0.02 sec)

显示第4页

root@database-one 22:12:  [gftest]> select * from emp_copy order by eno desc limit 30,10;
+-----+--------+------+---------+------------+--------+
| eno | ename  | age  | sal     | hiredate   | deptno |
+-----+--------+------+---------+------------+--------+
|  12 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|  11 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|  10 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|   9 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|   8 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
|   5 | 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
|   4 | 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
|   3 | 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
|   2 | 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
|   1 | 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
+-----+--------+------+---------+------------+--------+
10 rows in set (0.01 sec)

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

MySQL循序渐进（共42篇）

从搬砖到开车。

MySQL中的limit及其灵活使用

文章被以下合辑收录

评论