MySQL中实现分页操作的实战指南

当我们的数据量很大，而一次要将所有数据查询出来的话，就十分耗时，会严重影响用户体验。而通过在 MySQL 中实现分页操作，我们可以优化查询效率，提高用户体验。

实现分页的方式有很多种，但本文主要介绍通过 MySQL 的 limit 和 offset 语法实现分页操作。

什么是 limit 和 offset

limit 和 offset 是 MySQL 中用于分页操作的关键词。

• limit 用于限制查询结果的返回数量。
• offset 用于指定查询结果的偏移量，即从第几条数据开始查询结果。

如何使用 limit 和 offset

假设我们要查询数据库中所有用户的信息，并将其按照注册时间的先后顺序排列。同时，每页只显示 10 条用户信息，现在需要在第三页中查询用户信息。

通过使用 limit 和 offset，我们可以将查询语句写成如下格式：

SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

其中，LIMIT 10 表示页面只展示 10 条记录，OFFSET 20 表示从第 21 条记录开始查询。

可以发现，通过使用 limit 和 offset，我们不需要一次性查询出所有的结果，而是只查询需要的一部分结果，提高了查询效率。

MySQL 分页实战操作指南

假设我们的网站有一个用户评论功能，现在需要对用户的评论进行分页操作。

1. 根据评论时间降序查询所有评论

SELECT * FROM comments ORDER BY created_at DESC;

2. 分页查询

假设我们需要查询第 2 页的评论，每页展示 10 条记录：

SELECT * FROM comments ORDER BY created_at DESC LIMIT 10 OFFSET 10;

上述语句中，LIMIT 10 表示每页展示 10 条记录，OFFSET 10 表示从第 11 条记录开始查询，即上一页查询到第 10 条记录。

3. 分页查询中获取总记录数

如果需要在页面中展示总评论数和总页数，可以使用以下语句获取总记录数：

SELECT COUNT(*) FROM comments;

这里的 COUNT(*) 表示查询总记录数。

在实际应用中，我们可以使用该语句查询出总记录数后，通过总记录数和每页展示的记录数计算出总页数，即 totalPage = ceil(totalRecord / pageSize)，ceil 表示向上取整。

4. 将分页操作封装为函数

我们可以将分页操作封装成函数，提高代码的重用性和可维护性：

-- 定义函数名为 getCommentsPage
DELIMITER //
CREATE FUNCTION getCommentsPage(pageNum INT, pageSize INT)
RETURNS TEXT
BEGIN
  DECLARE offsetNum INT;
  DECLARE maxPage INT;
  DECLARE pageStr TEXT;
  SET offsetNum = (pageNum - 1) * pageSize;
  SET maxPage = CEIL((SELECT COUNT(*) FROM comments) / pageSize);
  SET pageStr = CONCAT('SELECT * FROM comments ORDER BY created_at DESC LIMIT ', pageSize, ' OFFSET ', offsetNum);
  SET pageStr = CONCAT(pageStr, ',{"totalPage":', maxPage, '}');
  RETURN pageStr;
END //
DELIMITER ;

在上述函数中，我们定义了两个参数 pageNum 和 pageSize，pageNum 代表当前页数，pageSize 代表每页展示的记录数。

• 我们首先定义了变量 offsetNum，用于计算查询结果的偏移量。
• 然后使用了 SELECT COUNT(*) 的方式查询总记录数，并通过 pageSize 计算出总页数。
• 接着构造了查询语句，并在语句中添加了关键字 {"totalPage":xxx}，用于在页面中显示总页数。

使用该函数进行分页查询：

SELECT CONCAT('[', getCommentsPage(2, 5), ']');

其中，将查询结果封装成了 JSON 数组并返回，方便前端展示。

示例说明

假如我们有一张评论表，其中包含以下字段：

CREATE TABLE `comments` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `content` TEXT COMMENT '评论内容',
  `created_at` DATETIME COMMENT '评论时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

插入评论数据：

INSERT INTO comments(content, created_at) VALUES 
('评论1', '2021-02-01 10:00:00'),
('评论2', '2021-02-02 10:00:00'),
('评论3', '2021-02-03 10:00:00'),
('评论4', '2021-02-04 10:00:00'),
('评论5', '2021-02-05 10:00:00'),
('评论6', '2021-02-06 10:00:00'),
('评论7', '2021-02-07 10:00:00'),
('评论8', '2021-02-08 10:00:00'),
('评论9', '2021-02-09 10:00:00'),
('评论10', '2021-02-10 10:00:00'),
('评论11', '2021-02-11 10:00:00'),
('评论12', '2021-02-12 10:00:00'),
('评论13', '2021-02-13 10:00:00'),
('评论14', '2021-02-14 10:00:00'),
('评论15', '2021-02-15 10:00:00');

如果我们需要查询第 2 页的评论，每页展示 5 条记录：

SELECT * FROM comments ORDER BY created_at DESC LIMIT 5 OFFSET 5;

查询结果：

id	content	created_at
10	评论10	2021-02-10 10:00:00
9	评论9	2021-02-09 10:00:00
8	评论8	2021-02-08 10:00:00
7	评论7	2021-02-07 10:00:00
6	评论6	2021-02-06 10:00:00

可以看出，上述查询结果返回了第 2 页的 5 条评论记录。

如果需要查询总评论数和总页数：

-- 查询总评论数
SELECT COUNT(*) FROM comments; -- 返回 15

-- 查询总页数
SELECT CEIL(COUNT(*) / 5) FROM comments; -- 返回 3

总评论数为 15，总页数为 3。

综上，使用 limit 和 offset 实现分页操作可以提高查询效率，通过封装函数可以提高代码的重用性和可维护性，在实际应用中非常实用。