Mysql根据指定数据确定分页

场景

某业务，只知道每页要展示数据大小和指定数据，然后确定指定数据在第几页。

测试表user
id name age update_time create_time
1 hou 32 2017-11-01 12:00:00 2017-11-01 12:00:00
2 hou1 22 2017-11-01 12:00:00 2017-11-11 12:00:00
3 hou2 29 2017-09-01 12:00:00 2017-11-01 12:00:00
4 hou3 24 2017-05-01 12:00:00 2017-11-02 12:00:00
5 hou4 25 2017-12-01 12:00:00 2017-08-01 12:00:00
6 hou5 26 2017-11-01 04:02:00 2017-03-01 12:00:00
7 hou6 12 2017-11-01 12:23:00 2017-10-01 12:00:00
8 hou7 28 2017-10-01 12:00:00 2017-11-01 12:00:00
需求

现在只知道每页要显示3条数据，即pageSize = 3。确认 id = 8 的数据在第几页。

分析

上面的需求，有两个情况。情况一是根据ID从小到大升序查询；情况二是根据表中某些字段进行排序。

情况一解决方案

  1. 查询 id <= 8 的记录总数 count
  2. 取模 mod = count % pageSize
  3. 取整 multiple = count / pageSize
  4. 计算id = 8 的数据在第几页：pageNo = mod == 0 ? multiple : multiple + 1

情况二解决方案

因为在查询的时候进行了重排序，所以记录的ID可能会出现错乱现象，因为不能使用 情况一的解决方案 来解决问题。此时需要考虑其他解决方案。

我们知道，不能使用情况一的解决方案的根源是因为唯一且顺序排序的ID发生了错乱，因为我们的解决方案也是想着能否使用SQL语句使查询出的结果根据某个字段进行顺序排序。由此想到了查询结果的行数，因此可以得到相应解决方案。

查询结果记录对应的行数sql

1
2
3
4
5
6
7
8
9
10
SELECT
	@rownum :=@rownum + 1 AS rownum,
	user.id AS userId,
	name, age,
	update_time
FROM
	user,
	(SELECT @rownum := 0) t
ORDER BY
	update_time DESC

查询结果为：

rownum userId name age update_time
1 5 hou4 25 2017-12-01 12:00:00
2 7 hou6 12 2017-11-01 12:23:00
3 1 hou 32 2017-11-01 12:00:00
4 2 hou1 22 2017-11-01 12:00:00
5 6 hou5 26 2017-11-01 04:02:00
6 8 hou7 28 2017-10-01 12:00:00
7 3 hou2 29 2017-09-01 12:00:00
8 4 hou3 24 2017-05-01 12:00:00

有查询的结果表可知，user表中的 id = 8 的数据，在新表中的第六行。即id = 8 的数据前面有5条数据。根据 情况一的解决方案 即可算出分页。

  1. 获取 id = 8 的记录位于结果集中第几行，即rownum。SQL为：
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT rownum form 
(
    SELECT
    	@rownum :=@rownum + 1 AS rownum,
    	user.id AS userId,
    	name, age,
    	update_time
    FROM
    	user,
    	(SELECT @rownum := 0) t
    ORDER BY
    	update_time DESC
) A where userId = 8
  1. 取模 mod = rownum % pageSize
  2. 取整 multiple = rownum / pageSize
  3. 计算id = 8 的数据在第几页：pageNo = mod == 0 ? multiple : multiple + 1
Fork me on GitHub