MySQL中数据结果集分页功能的实现方法

关键字： MySQL 分页 limit

目前B/S架构的软件大行其道。通过浏览器如何向终端用户只展示指定页面内容而不是全部的？
毕竟返回全部结果集，时间和空间开销都很大。

如此情况下，对SQL返回结果集进行分页是个很不错的解决方案。

本文主要介绍MySQL数据库中的分页实现方式。我以一个数据库管理员的角度进行说明，至于具体的实现还需要开发人员详细编写过程。

总的说来，在MySQL中实现分页功能是很简单的事情。MySQL数据库的开发人员替广大的使用者着想，提供了一个关键字limit来实现限定指定范围的功能。

下面简单介绍分页的思路。

在MySQL5.0.15-nt-max下进行测试。
1.新建一张临时表(MyISAM或者InnoDB类型的都可以)，本测试表没有使用自增列。

CREATE TABLE `andkylee` (  
  `id` int(11) NOT NULL,  
  `name` varchar(30) default NULL,  
  `sex` char(1) NOT NULL,  
  `age` smallint(6) default NULL,  
  `note` varchar(100) default NULL  
) ENGINE=MyISAM DEFAULT CHARSET=utf8;  

2.插入测试数据。 在这里尤其介绍一种插入大量测试数据的方法。
先插入2条数据，

insert into andkylee  
values(1,'hanyuliu','F',25,'aaaaaaaaaa'),(2,'andkylee','F',33,'bbbbbbbbbb');  

批量插入数据
在没有自增列的情况下，用下面的语句来实现插入“重复”数据。

insert into andkylee
select id+(select max(id) from andkylee),name,sex,age,note from andkylee;

本语句能够复制表已有的所有记录，并且能够实现“主键列”自增。

可能有点缺陷的是：需要再select列表中逐个写出各个列名。但是，好像含有自增列的表为了实现插入重复数据，也必须把非自增列都写出来吧。 如果我说的不对，请告知。

循环插入重复数据，使得表数据行数以指数方式增长。 再把已有的数据插入到表内，行数变为以前的2倍。

insert into andkylee
select id+(select max(id) from andkylee),name,sex,age,note from andkylee;

mysql> select count(*) from andkylee;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.05 sec)

重复上面的语句，执行10次之后，表内的数据行数为：2048
测试数据情况为：

mysql> select *from andkylee;  
+------+----------+-----+------+------------+  
| id   | name     | sex | age  | note       |  
+------+----------+-----+------+------------+  
|    1 | hanyuliu | F   |   25 | aaaaaaaaaa |  
|    2 | andkylee | F   |   33 | bbbbbbbbbb |  
|    3 | hanyuliu | F   |   25 | aaaaaaaaaa |  
|    4 | andkylee | F   |   33 | bbbbbbbbbb |  
.....  
| 2043 | hanyuliu | F   |   25 | aaaaaaaaaa |  
| 2044 | andkylee | F   |   33 | bbbbbbbbbb |  
| 2045 | hanyuliu | F   |   25 | aaaaaaaaaa |  
| 2046 | andkylee | F   |   33 | bbbbbbbbbb |  
| 2047 | hanyuliu | F   |   25 | aaaaaaaaaa |  
| 2048 | andkylee | F   |   33 | bbbbbbbbbb |  
+------+----------+-----+------+------------+  
2048 rows in set (0.08 sec)  

3.选择第100至300行数据
这里不得不介绍limit这个关键字了。
limit关键字的使用方式为： (SQL statement) limit row_position,row_count
其中：(SQL statement) 为完整的SQL语句，就是普通的sql语句，以前的语句该怎么写的还是怎么写。
limit 是个关键字，在MySQL中关键字是不区分大小写的。
后面是两个参数，row_position表示起始行，更确切的说应该是偏移行，自然row_position是从0开始的。也就是说第一行的偏移行数为0.
要是想返回从第二行开始的记录，就将row_position指定为1.
第二个参数：row_count表示返回的行数。 这个很简单，不赘述。
因此，要返回第100至300行数据，需要指定偏移行号为99，并且行数为201.

语句为：

select * from andkylee LimiT 99,201;  

返回结果为：

mysql> select *from andkylee LimiT 99,201;  
+-----+----------+-----+------+------------+  
| id  | name     | sex | age  | note       |  
+-----+----------+-----+------+------------+  
| 100 | andkylee | F   |   33 | bbbbbbbbbb |  
| 101 | hanyuliu | F   |   25 | aaaaaaaaaa |  
| 102 | andkylee | F   |   33 | bbbbbbbbbb |  
| 103 | hanyuliu | F   |   25 | aaaaaaaaaa |  
   
            ………………………………………………………………
             ………………………………………………………………

| 297 | hanyuliu | F   |   25 | aaaaaaaaaa |  
| 298 | andkylee | F   |   33 | bbbbbbbbbb |  
| 299 | hanyuliu | F   |   25 | aaaaaaaaaa |  
| 300 | andkylee | F   |   33 | bbbbbbbbbb |  
+-----+----------+-----+------+------------+  
201 rows in set (0.00 sec)  
mysql>  
 

再展示一个带where条件的语句的limit使用情况。
返回name为’andkylee’的所有记录的第100至300行数据。

select * from andkylee where name='andkylee' LimiT 99,201;  

结果为：

mysql> select *from andkylee where name='andkylee' LimiT 99,201;  
+-----+----------+-----+------+------------+  
| id  | name     | sex | age  | note       |  
+-----+----------+-----+------+------------+  
| 200 | andkylee | F   |   33 | bbbbbbbbbb |  
| 202 | andkylee | F   |   33 | bbbbbbbbbb |  
| 204 | andkylee | F   |   33 | bbbbbbbbbb |  
| 206 | andkylee | F   |   33 | bbbbbbbbbb |  
| 208 | andkylee | F   |   33 | bbbbbbbbbb |  
          ………………………………………………………………
           ………………………………………………………………
| 586 | andkylee | F   |   33 | bbbbbbbbbb |  
| 588 | andkylee | F   |   33 | bbbbbbbbbb |  
| 590 | andkylee | F   |   33 | bbbbbbbbbb |  
| 592 | andkylee | F   |   33 | bbbbbbbbbb |  
| 594 | andkylee | F   |   33 | bbbbbbbbbb |  
| 596 | andkylee | F   |   33 | bbbbbbbbbb |  
| 598 | andkylee | F   |   33 | bbbbbbbbbb |  
| 600 | andkylee | F   |   33 | bbbbbbbbbb |  
+-----+----------+-----+------+------------+  
201 rows in set (0.09 sec)  
mysql>  

4. 功能完善。
通过第3步骤中的两个结果集进行对比，我们可以发现，MySQL中的limit关键字类似于一个伪列的功能。比Oracle中的rownum要强大，在oracle中要通过一次rownum再加一次虚拟列才能实现类似MySQL中的limit功能。因此limit功能存在优势的地方在于：不产生虚拟列。也就是说你不需要花费额外的功夫来处理列名列表了。最简单的就是直接用星号*,开发人员在编写存错过程的时候比较省事了。

最后，个人感觉：通过比较mssqlserver，oracle，sybase中的分页是实现方式，我感觉MySQL的limit是最简单最方便的。

--------------------- ******************* 分 割 线 *********************---------------------
　Oracle分页查询格式：
　　以下是代码片段：

　　SELECT * FROM (　　
    SELECT A.*, ROWNUM  FROM (  SELECT * FROM TABLE_NAME )   WHERE ROWNUM <= 40
　　)
　　WHERE RN >= 21

mysql

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 ,注意，10为偏移量

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

//如果只给定一个参数，它表示返回最大的记录行数目：

mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行 

//也就是说，LIMIT n 等价于 LIMIT 0,n。