MySQL系列(29): 根据id查出其所在分页的列表数据

根据给出的主键 id ,计算出该行所在的分页。SQL 语句的分页关键词 Limit 需要用的数据有 offsetsize。而在应用端展示,会有页码(pageNo)和 每页条数(pageSize),总数(total) 三个数据。

分页算法

关键数据

  • offset:偏移量,指从第几行开始。
  • size:指查多少条数据。
  • pageNo:当前页码。
  • pageSize:每页显示条数,等同于 size。
  • total:总条数。
  • pages:总页数。

分页实现

  1. limit 分页公式

    1
    limit(pageNo - 1)*pageSize, pageSize;
  2. 偏移量 offset 计算,即从第几行开始查询

    1
    $offset = (pageNo-1)*pageSize;
  3. 总条数计算 total。

    1
    $total = select count(1) from table_name;
  4. 总页数计算。总数除以每页条数,余数大于 0 则页数加 1,余数为最后一页的行数。

    换总思路:行数从 0 开始,总数减 1;(总数 加 每页数量)/ 每页数量,商为 页数,余数为最后一页的行数。

    1
    $pages = (total + pageSize - 1)/pageSize;

分页实现

根据给定的 id 和 pageSize,计算出此 id 所在的分页,即计算出 id 所在的页码。

实现思路

  1. 需要查出总条数

    1
    $total = select count(1) from table_name;
  2. 计算出总页数

    1
    $pages = (total + pageSize - 1)/pageSize;
  3. 需要知道当前 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
    2
    SELECT rownum FROM tmp_table WHERE id = 156;
    -- rownum=192

    注意:临时表是与连接绑定的,只在当前连接可见,连接关闭后,临时表会被自动删除。但如果是外部程序创建临时表,用完后应及时删除临时表,否则可能产生脏数据。

    1
    2
    3
    DROP TABLE tmp_table;
    -- 或
    DROP TEMPORARY TABLE tmp_table;
  4. 计算当前 id 所在的页码

    1
    $pageNo = (rownum + pageSize - 1)/pageSize;

示例验证

例,假如总数有 196 条数据,每页10条,查出 id=58 所在的页。

先查下到 id <= 58 的共有 54 条数据,分页每页 10 条,共有 6 页,id=58 是第 6 页的第 4 条数据。

1
2
3
4
5
6
7
mysql> SELECT count(1) FROM record r WHERE r.id <= 58 ORDER BY id ASC;
+----------+
| count(1) |
+----------+
| 54 |
+----------+
1 row in set (0.03 sec)

使用 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
2
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;
Query OK, 196 rows affected (0.00 sec)

从临时表中查询 id=58 的行号

1
2
3
4
5
6
7
mysql> SELECT rownum FROM tmp_table WHERE id = 58;
+--------+
| rownum |
+--------+
| 54 |
+--------+
1 row in set (0.02 sec)

计算 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT * FROM record r ORDER BY id ASC limit 50, 10;
+----+------------+-----------+---------------------+
| id | first_name | last_name | last_update |
+----+------------+-----------+---------------------+
| 55 | FAY | KILMER | 2006-02-15 04:34:33 |
| 56 | DAN | HARRIS | 2006-02-15 04:34:33 |
| 57 | JUDE | CRUISE | 2006-02-15 04:34:33 |
| 58 | CHRISTIAN | AKROYD | 2006-02-15 04:34:33 |
| 59 | DUSTIN | TAUTOU | 2006-02-15 04:34:33 |
| 60 | HENRY | BERRY | 2006-02-15 04:34:33 |
| 61 | CHRISTIAN | NEESON | 2006-02-15 04:34:33 |
| 62 | JAYNE | NEESON | 2006-02-15 04:34:33 |
| 63 | CAMERON | WRAY | 2006-02-15 04:34:33 |
| 64 | RAY | JOHANSSON | 2006-02-15 04:34:33 |
+----+------------+-----------+---------------------+
10 rows in set (0.05 sec)

删除临时表

1
2
3
4
mysql> DROP TEMPORARY TABLE tmp_table;
Query OK, 0 rows affected (0.00 sec)
-- 或
mysql> DROP TABLE tmp_table;

MySQL系列(29): 根据id查出其所在分页的列表数据

http://blog.gxitsky.com/2022/10/01/MySQL-29-Query-page-in-id/

作者

光星

发布于

2022-10-01

更新于

2023-03-06

许可协议

评论