SQL 常见分页方式详解
在数据库查询中，分页是一个常见的需求，不同的数据库有不同的分页方式。下面介绍几种常见的 SQL 分页方法。
一、LIMIT 和 OFFSET 分页法
这是最常见和通用的分页方式，适用于大多数支持 SQL 标准的关系型数据库，如 MySQL、PostgreSQL、SQLite 等。
1. 基本语法
SELECT * FROM table_name
ORDER BY column_name
LIMIT offset, count;
其中，LIMIT用于限制返回的记录条数，offset表示偏移量，即从结果集的第几行开始返回数据，count是每页返回的行数。
2. 示例
例如，获取第 2 页，每页显示 10 条记录：
SELECT * FROM table_name
ORDER BY column_name
LIMIT 10 OFFSET 10;
在 MySQL 中，这种分页方式非常常见：
- 要获取第 2 页，每页 10 条记录，可以这样写：
SELECT * FROM table_name LIMIT 10 OFFSET 10;，表示从第 11 条记录开始，取 10 条记录。
二、OFFSET FETCH 分页法
SQL Server 2012 及以上版本和 PostgreSQL 9.5 及以上版本支持 OFFSET FETCH 语法。
1. 基本语法
SELECT * FROM table_name
ORDER BY column_name
OFFSET offset ROWS FETCH NEXT count ROWS ONLY;
OFFSET offset ROWS指定偏移量，FETCH NEXT count ROWS ONLY指定返回的行数。
2. 示例
例如，获取第 2 页，每页显示 10 条记录：
SELECT * FROM table_name
ORDER BY column_name
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
三、ROW_NUMBER() 分页法
使用 ROW_NUMBER() 窗口函数进行分页，通常性能较好，尤其在复杂查询中，适用于 SQL Server、PostgreSQL 等支持窗口函数的数据库。
1. 基本语法
SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY column_name) AS row_numFROM table_name
) AS numbered_rows
WHERE row_num BETWEEN offset + 1 AND offset + count;
ROW_NUMBER() OVER (ORDER BY column_name)为结果集中的每一行分配一个序号，WHERE row_num BETWEEN offset + 1 AND offset + count筛选出指定页的数据。
2. 示例
例如，获取第 2 页，每页显示 10 条记录：
SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY column_name) AS row_numFROM table_name
) AS numbered_rows
WHERE row_num BETWEEN 11 AND 20;
四、ROWNUM 分页法（适用于 Oracle）
在 Oracle 数据库中，可以使用 ROWNUM 进行分页。
1. 基本语法
SELECT *
FROM (SELECT *, ROWNUM AS row_numFROM table_nameWHERE ROWNUM <= offset + countORDER BY column_name
)
WHERE row_num > offset;
ROWNUM是 Oracle 中的伪列，表示返回的行号。
2. 示例
例如，获取第 2 页，每页显示 10 条记录：
SELECT *
FROM (SELECT *, ROWNUM AS row_numFROM table_nameWHERE ROWNUM <= 20ORDER BY column_name
)
WHERE row_num > 10;
五、MySQL 特定分页方式
1. 子查询分页
可以通过子查询先对数据进行排序，然后再使用LIMIT进行分页。
SELECT * FROM (SELECT * FROM table_name ORDER BY column_name) subquery LIMIT 10 OFFSET 10;
这里先对表中的数据按照指定列进行排序，然后从第 11 条记录开始取 10 条记录。
2. 存储过程分页
创建一个存储过程，接受页码和每页记录条数等参数，在存储过程中进行分页查询。
DELIMITER //CREATE PROCEDURE pagination(IN page INT, IN pageSize INT)
BEGINSET @offset = (page - 1) * pageSize;SELECT * FROM table_name LIMIT @offset, pageSize;
END //DELIMITER ;
调用存储过程：
CALL pagination(2, 10);
3. 变量分页
通过设置变量来实现分页。
SET @page = 2;
SET @pageSize = 10;
SET @offset = (@page - 1) * @pageSize;
SELECT * FROM table_name LIMIT @offset, @pageSize;
总结
SQL 分页方式的选择取决于所使用的数据库类型和版本，以及性能和可维护性的考量。不同的分页方式可能会在性能和适用性上有所差异，开发人员需要根据具体情况选择合适的方式来实现分页功能。