MySQL 分页

在处理数据库查询结果时，经常会遇到需要分页展示数据的情况。本文将详细介绍 MySQL 中的分页功能，包括使用 LIMIT 关键字实现基本分页、优化 LIMIT 效率、结果集偏移量等相关内容。

1. 基本分页

首先，我们通常会使用 SELECT 语句来获取数据，然后使用 LIMIT 关键字来实现基本的分页功能。

LIMIT 关键字可以限制查询结果的返回行数，并且可以指定起始行。

SELECT * FROM table_name
LIMIT offset, row_count;

其中，offset 表示偏移量，即从结果集的第几行开始返回，row_count 表示返回的行数。

例如，我们有一个名为 student 的表，包含 id、name 和 score 字段。

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    score INT
);

我们可以使用如下方式进行分页查询：

SELECT *
FROM student
ORDER BY id
LIMIT 10, 5;

上述语句将返回 student 表中按照 id 排序的第 11~15 行数据。

2. 优化 LIMIT 效率

在面对大量数据的查询时，如果直接使用 LIMIT 关键字来分页，可能会导致效率较低。这是因为 MySQL 在执行查询时，需要扫描所有满足条件的行，然后再返回指定的行数。

为了优化查询效率，可以使用游标。游标的工作原理是，先根据条件查询出所有满足条件的数据，然后根据指定的偏移量和行数，返回需要的数据。

2.1 使用游标

MySQL 8.0 以上版本支持使用游标实现分页查询。可以通过以下方式获得一个游标并设置起始行：

DECLARE cur CURSOR FOR SELECT * FROM table_name;
SET @row_number = offset;

然后，在循环中获取游标数据，并根据偏移量和行数确定是否输出。

OPEN cur;
FETCH NEXT FROM cur INTO @id, @name, @score;
WHILE @@FETCH_STATUS = 0 AND @row_number < (offset + row_count) DO
    IF @row_number >= offset THEN
        -- 输出数据
    END IF;
    SET @row_number = @row_number + 1;
    FETCH NEXT FROM cur INTO @id, @name, @score;
END WHILE;
CLOSE cur;

2.2 使用服务器变量

如果使用的是 MySQL 5.6 及以下版本，则无法使用游标来优化 LIMIT 执行效率。此时，可以使用服务器变量来代替。

首先，我们需要声明两个服务器变量 @row_number 和 @total 来保存查询的总行数和当前行数。

SET @total = 0; -- 总行数
SET @row_number = 0; -- 当前行数

然后，在查询语句中，使用子查询来计算总行数，并将结果保存到 @total 变量中。

SELECT COUNT(*) INTO @total FROM table_name;

最后，使用两层嵌套查询来实现分页查询，并将当前行数保存到 @row_number 变量中。

SELECT t.*, @row_number := @row_number + 1 AS row_number
FROM (
    SELECT * FROM table_name
    ORDER BY id
    LIMIT offset, row_count
) AS t;

通过上述方式，我们可以提高查询的效率，并且能够正确地获取分页结果。

3. 结果集偏移量

在实际应用中，可能会遇到需要获取上一页或下一页数据的场景。MySQL 提供了 OFFSET 子句来实现结果集偏移量。

SELECT * FROM table_name
LIMIT row_count
OFFSET offset;

其中，offset 表示从结果集的第几行开始返回，row_count 表示返回的行数。

例如，我们需要获取上一页数据，可以通过以下方式实现：

SET @offset = (currentPage - 1) * pageSize; -- 当前页偏移量
SET @row_count = pageSize; -- 每页行数

SELECT *
FROM table_name
ORDER BY id
LIMIT @row_count
OFFSET @offset;