使用 MySQL 进行分页

拥有一个大型数据集并且只需要获取特定数量的行，这就是 LIMIT子句的存在原因。它允许限制 SQL 查询语句返回的结果中的行数。

分页是指将大型数据集划分为较小部分的过程。

通过一次获取小块数据来更快地向用户发送数据的能力是使用分页的好处之一。

工作原理

分页的工作原理是定义每个请求的结果中的最大行数以及所请求的页面。

下表表示名为users的表上的项目，该表将用作示例。

+----+----------+
| id | Name     |
+----+----------+
| 1  | John     |
| 2  | Jane     |
| 3  | Peter    |
| 4  | Joseph   |
| 5  | Mary     |
| 6  | Jack     |
| 7  | Ann      |
| 8  | Bill     |
| 9  | Sam      |
| 10 | Rose     |
| 11 | Juan     |
+----+----------+

对于此示例，最大行数将是2，这意味着在每个请求中，我们最多将获得 2 行。

该表有 11 行，我们将每个请求的结果限制为 2 行，导致 6 页 2 个项目。页数的确定方法是将行数 （11） 除以每页的行数 （2），并确保结果四舍五入到下一个整数。

Total pages = CEIL(Total number of rows / Limit number of rows)

MySQL没有PAGE子句，但它有OFFSET子句，它允许将位置从开始计数的位置移动到LIMIT数字。​​​​​​​​​​​​​​

OFFSET的值是通过将LIMIT子句值乘以您要查找的页码减去 1 来完成的。​​​​​​​

OFFSET = LIMIT * (PAGE - 1)

在上表中有 11 个用户，为了获取前 2 个用户，我们使用以下查询：

PAGE = 1
LIMIT = 2
OFFSET = (PAGE-1) * LIMIT
OFFSET = (1-1) * 2
OFFSET = 0 * 2
OFFSET = 0

偏移量初始值是0，而不是​​​​​​​1，这就是我们从页码中减去 1 的原因。

SELECT `id`, `name`
FROM `users`
LIMIT 2
OFFSET 0

前面的查询将生成以下结果，表示分页的第 1 页：

+----+----------+
| id | Name     |
+----+----------+
| 1  | John     |
| 2  | Jane     |
+----+----------+

MySQL有不同的方法来使用偏移量，而不使用OFFSET子句。

SELECT `id`, `name`
FROM `users`
LIMIT 0,2

第一个参数是偏移量，第二个参数是行计数。

要获得第二页，或者换句话说，接下来的两行，我们必须再次计算理论上增加一个OFFSET值。

PAGE = 2
LIMIT = 2
OFFSET = (PAGE-1) * LIMIT
OFFSET = (2-1) * 2
OFFSET = 1 * 2
OFFSET = 2

SELECT `id`, `name`
FROM `users`
LIMIT 2
OFFSET 2

下面可以看到上一个查询的结果：

+----+----------+
| id | Name     |
+----+----------+
| 3  | Peter    |
| 4  | Joseph   |
+----+----------+

查询将转换为跳过前 2 项并获取接下来的 2 行。

因此，在第三页中，我们使用以下 OFFSET 4 项来跳过前 4 项。

PAGE = 3
LIMIT = 2
OFFSET = (PAGE-1) * LIMIT
OFFSET = (3-1) * 2
OFFSET = 2 * 2
OFFSET = 4

SELECT `id`, `name`
FROM `users`
LIMIT 2 OFFSET 4

+----+----------+
| id | Name     |
+----+----------+
| 5  | Mary     |
| 6  | Jack     |
+----+----------+

偏移和排序方式

有时同时使用OFFSET和ORDER BY可以使分页不起作用，以随机顺序返回行，并在每个页面上返回意外行。​​​​​​​

如果多行在 ORDER BY 列中具有相同的值，则服务器可以自由地以任意顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回这些行。换句话说，这些行的排序顺序对于无序列是不确定的。MySQL 文档

最常见的情况是，如果您按没有索引的列排序，MySQL Server无法确定行的正确顺序。

解决此问题的一种方法是向一个或多个列添加索引。尽管如果您不想或不需要仅为此目的向多个列添加索引，这可能不是最佳选择。

如果确保具有和不带 LIMIT 的行顺序相同很重要，请在 ORDER BY 子句中包含其他列以使顺序具有确定性。MySQL 文档

这意味着还有另一种解决此问题的方法是在ORDER BY子句中添加唯一列，例如主键列。

SELECT `id`, `name`
FROM `users`
LIMIT 2
OFFSET 2
ORDER BY `name`, `id`

而不是：

SELECT `id`, `name`
FROM `users`
LIMIT 2
OFFSET 2
ORDER BY `name`

这样，您可以确保MySQL在查找LIMIT行数之前按唯一列对行进行排序。