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

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

分页算法

关键数据

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

分页实现

1. limit 分页公式

   limit(pageNo - 1)*pageSize, pageSize;

2. 偏移量 offset 计算，即从第几行开始查询

   $offset = (pageNo-1)*pageSize;

3. 总条数计算 total。

   $total = select count(1) from table_name;

4. 总页数计算。总数除以每页条数，余数大于 0 则页数加 1，余数为最后一页的行数。

   换总思路：行数从 0 开始，总数减 1；（总数 加 每页数量）/ 每页数量，商为 页数，余数为最后一页的行数。

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

分页实现

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

实现思路

1. 需要查出总条数

   $total = select count(1) from table_name;

2. 计算出总页数

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

3. 需要知道当前 id 所在的行

   注意，即使 id 是自增的，id 号不等行号，这一步是关键。需要使用自增变量来得到所有计录的行号。

   select @rownum := @rownum + 1 rownum,r.id from record r, (select @rownum := 0 ) n;

   创建临时表存储生成的行号数据。

   注意：@rownum 变量是根据当前查出行数动态变化的，如果不存临时表直接用 id 条件匹配，rownum 始终为 1。

   CREATE TEMPORARY TABLE tmp_table select @rownum := @rownum + 1 rownum,r.id from record r, (select @rownum := 0 ) n;

   从临时表查出当前 id 所在的行号。

   SELECT rownum FROM tmp_table WHERE id = 156;
   -- rownum=192

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

   DROP TABLE tmp_table;
   -- 或
   DROP TEMPORARY TABLE tmp_table;

4. 计算当前 id 所在的页码

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

示例验证

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

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

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 号，并自动行成行数序列号

mysql> select @rownum := @rownum + 1 rownum,r.id from record r, (select @rownum := 0 ) n ORDER BY r.id ASC;

将行数序列号和 id 存到临时表

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 的行号

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。

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)

删除临时表

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