mysql使用技巧分页limit

• Z时代
• 2024-01-10
• 分类：综合

mysql 分页使用 limit关键字，limit x,y （x代表从哪条数据开始，y代表页面大小。mysql第一条数据在limit计算时索引为0）

limit 10
　　前10条
limit 0,10
　　从第1条开始的10条
limit 10,10
　　从第 11 条开始的 10 条
limit 100,10
 　从第101条开始的10条
 　数据量大时(>千万)，效率低
oracal 分页，使用 oracle的特殊列 rownum
select*from
(select*,rownum R from
(select*from a)

实例：查找入职员工时间排名倒数第三的员工所有信息。limit 2,1代表选择从第3条数据开始的1条数据，即第3页数据，页面大小为1

select*from employees orderby hire_date desc limit 2,1

前n条数据，即从索引0开始计算:limit n 或者 limit 0,n

mysql>select*from employees orderby hire_date desc limit 5;
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
|173| Sundita    | Kumar     | SKUMAR  |011.44.1343.329268|2000-04-21| SA_REP   |6100.00|0.10|148|80|
|167| Amit       | Banda     | ABANDA  |011.44.1346.729268|2000-04-21| SA_REP   |6200.00|0.10|147|80|
|166| Sundar     | Ande      | SANDE   |011.44.1346.629268|2000-03-24| SA_REP   |6400.00|0.10|147|80|
|128| Steven     | Markle    | SMARKLE |650.124.1434|2000-03-08| ST_CLERK |2200.00|NULL|120|50|
|165| David      | Lee       | DLEE    |011.44.1346.529268|2000-02-23| SA_REP   |6800.00|0.10|147|80|
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
5 rows inset (0.00 sec)
mysql>select*from employees orderby hire_date desc limit 0,5;
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
|173| Sundita    | Kumar     | SKUMAR  |011.44.1343.329268|2000-04-21| SA_REP   |6100.00|0.10|148|80|
|167| Amit       | Banda     | ABANDA  |011.44.1346.729268|2000-04-21| SA_REP   |6200.00|0.10|147|80|
|166| Sundar     | Ande      | SANDE   |011.44.1346.629268|2000-03-24| SA_REP   |6400.00|0.10|147|80|
|128| Steven     | Markle    | SMARKLE |650.124.1434|2000-03-08| ST_CLERK |2200.00|NULL|120|50|
|165| David      | Lee       | DLEE    |011.44.1346.529268|2000-02-23| SA_REP   |6800.00|0.10|147|80|
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
5 rows inset (0.00 sec)

更多例子 

limit 5     前5条数据

limit 2,1   第3页数据，页面大小为1

limit 2,2   第2页数据，页面大小为2

limit 2,3   第3条数据起，共3条数据，此数据不符合分页数据显示格式
分页显示格式：limit startIndex pageSize
startIndex = (需要查询的页码数 - 1) * pageSize

mysql>select*from employees orderby hire_date desc limit 5;
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
|173| Sundita    | Kumar     | SKUMAR  |011.44.1343.329268|2000-04-21| SA_REP   |6100.00|0.10|148|80|
|167| Amit       | Banda     | ABANDA  |011.44.1346.729268|2000-04-21| SA_REP   |6200.00|0.10|147|80|
|166| Sundar     | Ande      | SANDE   |011.44.1346.629268|2000-03-24| SA_REP   |6400.00|0.10|147|80|
|128| Steven     | Markle    | SMARKLE |650.124.1434|2000-03-08| ST_CLERK |2200.00|NULL|120|50|
|165| David      | Lee       | DLEE    |011.44.1346.529268|2000-02-23| SA_REP   |6800.00|0.10|147|80|
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
5 rows inset (0.00 sec)
mysql>select*from employees orderby hire_date desc limit 2,1;
+-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email | phone_number       | hire_date  | job_id | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+
|166| Sundar     | Ande      | SANDE |011.44.1346.629268|2000-03-24| SA_REP |6400.00|0.10|147|80|
+-------------+------------+-----------+-------+--------------------+------------+--------+---------+----------------+------------+---------------+
1 row inset (0.00 sec)
mysql>select*from employees orderby hire_date desc limit 2,2;
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
|166| Sundar     | Ande      | SANDE   |011.44.1346.629268|2000-03-24| SA_REP   |6400.00|0.10|147|80|
|128| Steven     | Markle    | SMARKLE |650.124.1434|2000-03-08| ST_CLERK |2200.00|NULL|120|50|
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
2 rows inset (0.00 sec)
mysql>select*from employees orderby hire_date desc limit 2,3;
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
| employee_id | first_name | last_name | email   | phone_number       | hire_date  | job_id   | salary  | commission_pct | manager_id | department_id |
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
|166| Sundar     | Ande      | SANDE   |011.44.1346.629268|2000-03-24| SA_REP   |6400.00|0.10|147|80|
|128| Steven     | Markle    | SMARKLE |650.124.1434|2000-03-08| ST_CLERK |2200.00|NULL|120|50|
|165| David      | Lee       | DLEE    |011.44.1346.529268|2000-02-23| SA_REP   |6800.00|0.10|147|80|
+-------------+------------+-----------+---------+--------------------+------------+----------+---------+----------------+------------+---------------+
3 rows inset (0.00 sec)

以上是 mysql使用技巧分页limit 的全部内容， 来源链接： utcz.com/z/533190.html