分页查询

原文连接：分页查询 - 廖雪峰的官方网站

分页

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

要实现分页功能，实际上就是从结果集中显示第1~100条记录作为第1页，显示第101~200条记录作为第2页，以此类推。

因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <N-M> OFFSET <M>子句实现。我们先把所有学生按照成绩从高到低进行排序：

-- 按score从高到低
SELECT id, name, gender, score FROM students ORDER BY score DESC;

 结果：

id	name	gender	score
2	小红	F	95
8	小新	F	91
1	小明	M	90
9	小王	M	89
3	小军	M	88
10	小丽	F	88
7	小林	M	85
5	小白	F	81
4	小米	F	73
6	小兵	M	55

现在，我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：

-- 查询第1页
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

 结果：

id	name	gender	score
2	小红	F	95
8	小新	F	91
1	小明	M	90

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：

-- 查询第2页
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

  结果：

id	name	gender	score
9	小王	M	89
3	小军	M	88
10	小丽	F	88

 类似的，查询第3页的时候，OFFSET应该设定为6:

-- 查询第3页
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 6;

结果：

id	name	gender	score
7	小林	M	85
5	小白	F	81
4	小米	F	73

 查询第4页的时候，OFFSET应该设定为9:

-- 查询第4页
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 9;

 结果：

id	name	gender	score
6	小兵	M	55

 

由于第4页只有1条记录，因此最终结果集按实际数量1显示。LIMIT 3表示的意思是“最多3条记录”。

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

• LIMIT总是设定为pageSize；
• OFFSET计算公式为pageSize * (pageIndex - 1)。

这样就能正确查询出第N页的记录集。

如果原本记录集一共就10条记录，但我们把OFFSET设置为20，会得到什么结果呢？

-- OFFSET设定为20
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 20;

结果：Empty result set

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

注意

OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。

在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

小结

使用LIMIT <M> OFFSET <N>可以对结果集进行分页，每次查询返回结果集的一部分；

分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值。