MySQL系列(29): 根据id查出其所在分页的列表数据
根据给出的主键 id ,计算出该行所在的分页。SQL 语句的分页关键词 Limit
需要用的数据有 offset
,size
。而在应用端展示,会有页码(pageNo)和 每页条数(pageSize),总数(total) 三个数据。
分页算法
关键数据
- offset:偏移量,指从第几行开始。
- size:指查多少条数据。
- pageNo:当前页码。
- pageSize:每页显示条数,等同于 size。
- total:总条数。
- pages:总页数。
分页实现
limit 分页公式
1
limit(pageNo - 1)*pageSize, pageSize;
偏移量 offset 计算,即从第几行开始查询
1
$offset = (pageNo-1)*pageSize;
总条数计算 total。
1
$total = select count(1) from table_name;
总页数计算。总数除以每页条数,余数大于 0 则页数加 1,余数为最后一页的行数。
换总思路:行数从 0 开始,总数减 1;(总数 加 每页数量)/ 每页数量,商为 页数,余数为最后一页的行数。
1
$pages = (total + pageSize - 1)/pageSize;
分页实现
根据给定的 id 和 pageSize,计算出此 id 所在的分页,即计算出 id 所在的页码。
实现思路
需要查出总条数
1
$total = select count(1) from table_name;
计算出总页数
1
$pages = (total + pageSize - 1)/pageSize;
需要知道当前 id 所在的行
注意,即使 id 是自增的,id 号不等行号,这一步是关键。需要使用自增变量来得到所有计录的行号。
1
select @rownum := @rownum + 1 rownum,r.id from record r, (select @rownum := 0 ) n;
创建临时表存储生成的行号数据。
注意:
@rownum
变量是根据当前查出行数动态变化的,如果不存临时表直接用 id 条件匹配,rownum 始终为 1。1
CREATE TEMPORARY TABLE tmp_table select @rownum := @rownum + 1 rownum,r.id from record r, (select @rownum := 0 ) n;
从临时表查出当前 id 所在的行号。
1
2SELECT rownum FROM tmp_table WHERE id = 156;
-- rownum=192注意:临时表是与连接绑定的,只在当前连接可见,连接关闭后,临时表会被自动删除。但如果是外部程序创建临时表,用完后应及时删除临时表,否则可能产生脏数据。
1
2
3DROP TABLE tmp_table;
-- 或
DROP TEMPORARY TABLE tmp_table;计算当前 id 所在的页码
1
$pageNo = (rownum + pageSize - 1)/pageSize;
示例验证
例,假如总数有 196 条数据,每页10条,查出 id=58 所在的页。
先查下到 id <= 58 的共有 54 条数据,分页每页 10 条,共有 6 页,id=58 是第 6 页的第 4 条数据。
1 | mysql> SELECT count(1) FROM record r WHERE r.id <= 58 ORDER BY id ASC; |
使用 SQL 验证实现思路是否正确。
查询 id 号,并自动行成行数序列号
1 | mysql> select @rownum := @rownum + 1 rownum,r.id from record r, (select @rownum := 0 ) n ORDER BY r.id ASC; |
将行数序列号和 id 存到临时表
1 | mysql> CREATE TEMPORARY TABLE tmp_table select @rownum := @rownum + 1 rownum,r.id from record r, (select @rownum := 0 ) n ORDER BY r.id ASC; |
从临时表中查询 id=58 的行号
1 | mysql> SELECT rownum FROM tmp_table WHERE id = 58; |
计算 id=58 所在分页页码:pageNo = (54 + 10 - 1)/ 10 = 6.3,即 id = 54 的数据行在第 6 页的第 4 行(从 0 开始到 3)。
计算 id=58 所在分页的起始行数:(6-1)* 10 = 50,即 offset = 50。
查询 id=58 所在分页的列表数据: limit 50, 10
。
1 | mysql> SELECT * FROM record r ORDER BY id ASC limit 50, 10; |
删除临时表
1 | mysql> DROP TEMPORARY TABLE tmp_table; |
MySQL系列(29): 根据id查出其所在分页的列表数据
http://blog.gxitsky.com/2022/10/01/MySQL-29-Query-page-in-id/