MySQL中数据结果集分页功能的实现方法;数据库查询返回特定结果即分页查询

    xiaoxiao2021-03-25  54

    因为欣赏所以转载 原文地址  http://blog.csdn.net/andkylee/article/details/5637638  http://www.2cto.com/database/201306/218771.html

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

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

     

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

     

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

     

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

    在MySQL5.0.15-nt-max下进行测试。

     

    1.新建一张临时表(MyISAM或者InnoDB类型的都可以),本测试表没有使用自增列。

    [c-sharp]  view plain  copy 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条数据,

    [c-sharp]  view plain  copy 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

     

    测试数据情况为:

    [c-sharp]  view plain  copy 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中关键字是不区分大小写的,Limit,limit,LIMIT对于数据库引擎来说都是一样的。

    后面是两个参数,row_position表示起始行,更确切的说应该是偏移行,自然row_position是从0开始的。也就是说第一行的偏移行数为0.

    要是想返回从第二行开始的记录,就将row_position指定为1.

    第二个参数:row_count表示返回的行数。 这个很简单,不赘述。

     

    因此,要返回第100至300行数据,需要指定偏移行号为99,并且行数为201.

    语句为:

    [c-sharp]  view plain  copy select *from andkylee LimiT 99,201;  

     

     

    返回结果为:

    [c-sharp]  view plain  copy 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 |  

     

    再展示一个带where条件的语句的limit使用情况。

    返回name为'andkylee'的所有记录的第100至300行数据。

    [c-sharp]  view plain  copy select *from andkylee where name='andkylee' LimiT 99,201;  

     

    结果为:

    [c-sharp]  view plain  copy 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 |   | 210 | andkylee | F   |   33 | bbbbbbbbbb |   | 212 | andkylee | F   |   33 | bbbbbbbbbb |   | 214 | andkylee | F   |   33 | bbbbbbbbbb |   | 216 | andkylee | F   |   33 | bbbbbbbbbb |   | (0.09 sec)   mysql>   

     

     

    4. 功能完善。

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

     

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

    ---------------------------------------------------------------------------------------------------------------------------------

    1 几种不同数据库的不同的分页写法:

    a  mysql   1 a) 查询前n条记录 2 select * from table_name limit 0,n 3 b) 查询第n条到第m条 4 select * from table_name limit n,m b oracle    1 a)查询前n条记录 2 select * from table_name where rownum 3 b)查询第m条到第n条记录: 4 select * from (select a.*,a.rownum rn from table_name where rownum<n) where rn>m c sqlserver 1 a)查询前n条记录: 2 select top n * from table_name; 3 b)查询第n条到第m条记录: 4 select top n * from (select top m * from table_name order by column_name) a order by column_name desc 2 oracle rownum的用法 对于rownum来说它是oracle 系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,而且rownum不能以任何表的名称作为前缀。   (1) rownum 对于等于某值的查询条件 如果希望找到学生表中第一条学生的信息,可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息,使用rownum=2结果查不到数据。因为rownum都是从1开始,但是1以上的自然数在rownum做等于判断是时认为都是false条件,所以无法查到rownum = n(n>1的自然数)。 SQL> select rownum,id,name from student where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)   (2)rownum对于大于某值的查询条件    如果想找到从第二行记录以后的记录,当使用rownum>2是查不出记录的,原因是由于rownum是一个总是从1开始的伪列, Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立,所以查不到记录。       那如何才能找到第二行以后的记录呀。可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名,否则还是不会查出记录来,这是因为rownum不是某个表的列,如果不起别名的话,无法知道rownum是子查询的列还是主查询的列。 SQL>select * from(select rownum no ,id,name from student) where no>2;   (3)rownum对于小于某值的查询条件     如果想找到第三条记录以前的记录,当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n((n>1的自然数)的条件认为是成立的,所以可以找到记录。 SQL> select rownum,id,name from student where rownum <3;       综上几种情况,可能有时候需要查询rownum在某区间的数据,那怎么办呀从上可以看出rownum对小于某值的查询条件是人为true的,rownum对于大于某值的查询条件直接认为是false的,但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据,包括第二行和第三行数据,那么我们只能写以下语句,先让它返回小于等于三的记录行,然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。 SQL> select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;   (4)rownum和排序     Oracle中的rownum的是在取数据的时候产生的序号,所以想对指定排序的数据去指定的rowmun行数据就必须注意了。 SQL> select rownum ,id,name from student order by name;   可以看出,rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号,rowid也是顺序分配的。为了解决这个问题,必须使用子查询 SQL> select rownum ,id,name from (select * from student order by name);   3 mysql中的limit用法 具体的语法为:     1 SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offset     LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。    1 mysql> SELECT * FROM table LIMIT 5,10;  // 检索记录行 6-15 2   3 //为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1: 4 mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last. 5   6 //如果只给定一个参数,它表示返回最大的记录行数目: 7 mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行 8   9 //换句话说,LIMIT n 等价于 LIMIT 0,n。   4 mysql的高效分页写法 1 Select a.* from ( 2   select id from table b force index(ind_group_type_time) 3   where b.id=1111 order by b.update_time desc limit  xx, xx 4 ) b, table a where a.id=b.id; MySQL的limit工作原理就是先读取n条记录,然后抛弃前n条,读m条想要的,所以n越大,性能会越差。    优化前SQL: SELECT * FROM member ORDER BY last_active LIMIT 50,5    优化后SQL: SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)    分别在于,优化前的SQL需要更多I/O浪费,因为先读索引,再读数据,然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了,然后通过member_id读取需要的列。   5 分页写法的页数计算 总页数=(总记录数-1)/每页显示的记录数 +1   总结一下:数据库中mysql和oracle的分页写法都不一致,各个数据库有各自的特点。另外要注意下相关sql的性能优化,特别是针对大数据的翻页查询。

    转载请注明原文地址: https://ju.6miu.com/read-50025.html

    最新回复(0)