pages:总页数。
limit(pageNo - 1)*pageSize, pageSize;2.偏移量 offset 计算,即从第几行开始查询
$offset = (pageNo-1)*pageSize;3.总条数计算 total。
$total = select count(1) from table_name;4.总页数计算。总数除以每页条数,余数大于 0 则页数加 1,余数为最后一页的行数。
$pages = (total + pageSize - 1)/pageSize;
$total = select count(1) from table_name;2.计算出总页数
$pages = (total + pageSize - 1)/pageSize;3.需要知道当前 id 所在的行
select @rownum := @rownum + 1 rownum,r.id from record r, (select @rownum := 0 ) n;创建临时表存储生成的行号数据。
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;
# 堆代码 duidaima.com 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 验证实现思路是否正确。
mysql> select @rownum := @rownum + 1 rownum,r.id from record r, (select @rownum := 0 ) n ORDER BY r.id ASC;3.将行数序列号和 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)4.从临时表中查询 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)。
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)删除临时表
# 堆代码 duidaima.com mysql> DROP TEMPORARY TABLE tmp_table; Query OK, 0 rows affected (0.00 sec) -- 或 mysql> DROP TABLE tmp_table;