MySQL、Oracle和SQL Server分页查询实例解析

什么是分页查询

分页查询是在一张数据表中检索指定数量的数据，并以固定的分页大小显示在前端界面上。它通常被运用在数据量较大、无法一次性全部显示的情况下，将数据划分成若干页进行显示，有利于用户的数据浏览和页面渲染的负载均衡。

分页查询的基本语法

针对不同的数据库，分页查询的语法和方法会有所不同。然而，总体而言，我们可以将MySQL、Oracle和SQL Server 的分页查询分为两种方法，其基本语法如下：

第一种：使用 LIMIT 进行分页查询

MySQL、SQLite 和 MariaDB 数据库的分页查询，可以使用 SELECT 语句联合 LIMIT 语句进行分页，基本语法如下：

SELECT column FROM table LIMIT offset, limit;

其中，column 为需要检索的数据列，table 为数据源表，offset 指定了当前页的起始位置，即查询结果集的一段子集的开始行数，从0开始计数，limit 指定了当前页的数据量。

例如，想要查询数据源表A的前20条数据：

SELECT * FROM A LIMIT 0, 20;

如果想查询20到40条数据：

SELECT * FROM A LIMIT 20, 20;

第二种：使用 ROW_NUMBER 进行分页查询

Oracle 和 SQL Server 数据库的分页查询，可以使用 ROW_NUMBER () OVER() 函数和子查询实现，基本语法如下：

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (order by id asc) rownum, * FROM A) as t 
WHERE t.rownum BETWEEN offset and offset + limit -1;

其中，order by 子句用于指定排序方式，asc 表示升序，desc 表示降序；ROW_NUMBER() OVER () 子句用于生成行号，子查询中的 as t 则为子查询命名，方便后续使用。

以下为示例分别使用两种方法在 MySQL、Oracle 和 SQL Server 中进行分页查询的实例。

示例一：使用 LIMIT 进行分页查询

1. MySQL 示例

test 表结构如下：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

向 test 表中插入测试数据：

INSERT INTO `test` (`id`, `name`, `age`) VALUES
(1, 'xiaoming1', 10),
(2, 'xiaoming2', 20),
(3, 'xiaoming3', 30),
(4, 'xiaoming4', 40),
(5, 'xiaoming5', 50),
(6, 'xiaoming6', 60),
(7, 'xiaoming7', 70),
(8, 'xiaoming8', 80),
(9, 'xiaoming9', 90),
(10, 'xiaoming10', 100),
(11, 'xiaoming11', 110);

使用以下 SQL 语句，查询 test 表中前3条数据：

SELECT * FROM test LIMIT 0,3;

结果如下：

+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | xiaoming1 |  10 |
|  2 | xiaoming2 |  20 |
|  3 | xiaoming3 |  30 |
+----+-----------+-----+

使用以下 SQL 语句，查询 test 表中第 4 条至第 6 条（共 3 条）数据：

SELECT * FROM test LIMIT 3,3;

结果如下：

+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  4 | xiaoming4 |  40 |
|  5 | xiaoming5 |  50 |
|  6 | xiaoming6 |  60 |
+----+-----------+-----+

2. Oracle 示例

创建表 test，插入数据同 MySQL 示例。

使用以下 SQL 语句，查询 test 表中前 3 条数据：

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (order by id asc) rownum, * FROM test) as t 
WHERE t.rownum BETWEEN 1 AND 3;

结果如下：

   ROWNUM         ID NAME          AGE
---------- ---------- ------------ ----------
         1          1 xiaoming1          10
         2          2 xiaoming2          20
         3          3 xiaoming3          30

使用以下 SQL 语句，查询 test 表中第 4 条至第 6 条（共 3 条）数据：

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (order by id asc) rownum, * FROM test) as t 
WHERE t.rownum BETWEEN 4 AND 6;

结果如下：

   ROWNUM         ID NAME          AGE
---------- ---------- ------------ ----------
         4          4 xiaoming4          40
         5          5 xiaoming5          50
         6          6 xiaoming6          60

3. SQL Server 示例

创建表 test，插入数据同 MySQL 示例。

使用以下 SQL 语句，查询 test 表中前 3 条数据：

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (order by id asc) rownum, * FROM test) as t 
WHERE t.rownum BETWEEN 1 AND 3;

结果如下：

 rownum id name       age
------- -- ---------- -----------
      1  1 xiaoming1  10
      2  2 xiaoming2  20
      3  3 xiaoming3  30

使用以下 SQL 语句，查询 test 表中第 4 条至第 6 条（共 3 条）数据：

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) rownum, * FROM test) as t 
WHERE t.rownum BETWEEN 4 AND 6;

结果如下：

 rownum id name       age
------- -- ---------- -----------
      4  4 xiaoming4  40
      5  5 xiaoming5  50
      6  6 xiaoming6  60

示例二：使用 ROW_NUMBER 进行分页查询

1. MySQL 示例

MySQL 不支持 ROW_NUMBER 函数，因此可以通过使用变量和子查询来实现分页查询。先创建表 test，插入数据同示例一。

使用以下 SQL 语句，在 test 表中查询第 4 条到第 6 条数据：

SELECT t.* FROM 
(SELECT @rownum:=@rownum+1 as rownum, t1.* FROM 
  (SELECT @rownum:=0) r, test as t1 ORDER BY t1.id ASC) as t 
WHERE t.rownum BETWEEN 4 AND 6;

注意，这里的 @rownum 是一个 MySQL 自定义变量，用来模拟 ROW_NUMBER() 函数实现对查询结果集的行号计算，其初始化语句为 SELECT @rownum:=0。

结果如下：

 rownum id name       age
------- -- ---------- -----------
      4  4 xiaoming4  40
      5  5 xiaoming5  50
      6  6 xiaoming6  60

2. Oracle 示例

创建表 test，插入数据同示例一。

使用以下 SQL 语句，在 test 表中查询第 4 条到第 6 条数据：

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (order by id asc) rownum, * FROM test) as t 
WHERE t.rownum BETWEEN 4 AND 6;

结果如下：

   ROWNUM         ID NAME          AGE
---------- ---------- ------------ ----------
         4          4 xiaoming4          40
         5          5 xiaoming5          50
         6          6 xiaoming6          60

3. SQL Server 示例

创建表 test，插入数据同示例一。

使用以下 SQL 语句，查询 test 表中前 3 条数据：

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (order by id asc) rownum, * FROM test) as t 
WHERE t.rownum BETWEEN 1 AND 3;

结果如下：

 rownum id name       age
------- -- ---------- -----------
      1  1 xiaoming1  10
      2  2 xiaoming2  20
      3  3 xiaoming3  30

使用以下 SQL 语句，查询 test 表中第 4 条至第 6 条（共 3 条）数据：

SELECT * FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY Id ASC) rownum, * FROM test) as t 
WHERE t.rownum BETWEEN 4 AND 6;

结果如下：

 rownum id name       age
------- -- ---------- -----------
      4  4 xiaoming4  40
      5  5 xiaoming5  50
      6  6 xiaoming6  60

结束语

以上便是 MySQL、Oracle 和 SQL Server 分页查询的实例解析。对于较小规模数据的查询，我们建议使用 LIMIT 方法；而对于更加庞大的数据量或需要更加自由的排序机制的数据查询，我们建议使用 ROW_NUMBER() OVER() 方法。