mysql 分页存储过程实例

时间：2015-01-23 来源：西部数据作者：西部数据

本文章收集了四款mysql 分页存储过程实例代码,有高效的分页存储过程以及入门级的和通用的存储过程分页代码,如果你正在学mysql分页存储过程就进来看看吧.

mysql测试版本：5.0.41-community-nt,mysql分页存储过程

drop procedure if exists pr_pager;

create procedure pr_pager(

    in     p_table_name        varchar(1024),        /*表名*/

    in     p_fields            varchar(1024),        /*查询字段*/

    in     p_page_size            int,                /*每页记录数*/

    in     p_page_now            int,                /*当前页*/

    in     p_order_string        varchar(128),        /*排序条件(包含order关键字,可为空)*/

    in     p_where_string        varchar(1024),        /*where条件(包含where关键字,可为空)*/

     out     p_out_rows            int                    /*输出记录总数*/



)

    not deterministic

     sql security definer

     comment '分页存储过程'



begin

    /*定义变量*/

    declare m_begin_row int default 0;

    declare m_limit_string char(64);

    /*构造语句*/

    set m_begin_row = (p_page_now - 1) * p_page_size;

    set m_limit_string = concat(' limit ', m_begin_row, ', ', p_page_size);



    set @count_string = concat('select count(*) into @rows_total from ', p_table_name, ' ', p_where_string);

    set @main_string = concat('select ', p_fields, ' from ', p_table_name, ' ', p_where_string, ' ', p_order_string, m_limit_string);

    /*预处理*/

    prepare count_stmt from @count_string;

    execute count_stmt;

    deallocate prepare count_stmt;

    set p_out_rows = @rows_total;

    prepare main_stmt from @main_string;

    execute main_stmt;

    deallocate prepare main_stmt;



end

一款高效的存储过程分页代码,存储过程分页的基本原理:我们先对查找到的记录集(支持输入查找条件_whereclause和排列条件_orderby)的key字段临时存放到临时表,然后构建真正的记录集输出.

create   procedure   `mysqltestuser_select_pageable`(

        _whereclause   varchar(2000),     --   查找条件

        _orderby   varchar(2000),     --   排序条件

        _pagesize     int   ,       --   每页记录数

        _pageindex   int   ,     --   当前页码

        _docount       bit       --   标志:统计数据/输出数据

)

        not   deterministic

        sql   security   definer

        comment   ' '

begin

  --   定义key字段临时表

  drop   table   if   exists   _temptable_keyid;     --   删除临时表,如果存在

  create   temporary     table     _temptable_keyid

  (

userid   int

  )type=heap;

  --   构建动态的sql，输出关键字key的id集合

  --   查找条件

  set   @sql   =   'select     userid   from   mysqltestuser ';

  if   (_whereclause   is   not   null)     and   (_whereclause   <>   ' ')   then

  set   @sql=   concat(@sql,   '   where   '   ,_whereclause);

  end   if;

  if   (_orderby   is   not   null)     and     (_orderby   <> ' ')   then

  set   @sql=   concat(   @sql   ,   '   order   by   '   ,   _orderby);

  end   if;

  --   准备id记录插入到临时表

  set   @sql=concat( 'insert   into   _temptable_keyid(userid) ',   @sql);

  prepare   stmt   from   @sql;

  execute   stmt   ;

  deallocate   prepare   stmt;

--   key的id集合     [end]

--   下面是输出

if   (_docount=1)   then     --   统计

          begin

                    select   count(*)   as   recordcount   from   _temptable_keyid;

          end;

else                                   --   输出记录集

          begin

                  --   计算记录的起点位置

  set   @startpoint   =   ifnull((_pageindex-1)*_pagesize,0);

                  set   @sql= '                 select           a.*

      from       mysqltestuser   a

      inner   join   _temptable_keyid   b

      on     a.userid   =b.userid     ';

  set   @sql=concat(@sql, "     limit     ",@startpoint, "   , ",_pagesize);

                  prepare   stmt   from   @sql;

  execute   stmt   ;

  deallocate   prepare   stmt;

          end;

end   if;

  drop   table   _temptable_keyid;

end;

下面是mysqltestuser表的ddl:

create   table   `mysqltestuser`   (

    `userid`   int(11)   not   null   auto_increment,

    `name`   varchar(50)   default   null,

    `chinesename`   varchar(50)   default   null,

    `registerdatetime`   datetime   default   null,

    `jf`   decimal(20,2)   default   null,

    `description`   longtext,

    primary   key     (`userid`)

)   engine=innodb   default   charset=gb2312;

插入些数据:

insert   into   `mysqltestuser`   (`userid`,   `name`,   `chinesename`,   `registerdatetime`,   `jf`,   `description`)   values

    (1, 'xuu1 ', 'www.aimeige.com.cn ', '2007-03-29   12:54:41 ',1.5, 'description1 '),

    (2, 'xuu2 ', 'www.phpfensi.com ', '2007-03-29   12:54:41 ',2.5, 'description2 '),

存储过程调用测试:

-- 方法原型 `mysqltestuser_select_pageable`(条件,排列顺序,每页记录数,第几页,是否统计数据)

-- call `mysqltestuser_select_pageable`(_whereclause,_orderby ,_pagesize,_pageindex,_docount)

-- 统计数据

call `mysqltestuser_select_pageable`(null,null,null,null,1)

-- 输出数据,没条件限制,10条记录/页,第一页

call `mysqltestuser_select_pageable`(null, null, 10, 1,0)

-- 输出数据,条件限制,排列, 10条记录/页,第一页

call `mysqltestuser_select_pageable`( 'chinesename like ' '%飞3% ' ' ', 'userid asc ', 10, 1, 0)

一款mysql.net的方法

mysql + asp.net来写网站,既然mysql已经支持存储过程了,那么像分页这么常用的东西,当然要用存储过程啦.

不过在网上找了一些,发现都有一个特点——就是不能传出总记录数,干脆自己研究吧,终于,算是搞出来了,效率可能不是很好,但是我也觉得不错了,贴代码吧直接,也算是对自己学习mysql的一个记录.

create procedure p_pagelist

　　(

　　m_pageno int ,

　　m_perpagecnt int ,

　　m_column varchar(1000) ,

　　m_table varchar(1000) ,

　　m_condition varchar(1000),

　　m_orderby varchar(200) ,

　　out m_totalpagecnt int

　　)

　　begin

　　set @pagecnt = 1; -- 总记录数

　　set @limitstart = (m_pageno - 1)*m_perpagecnt;

　　set @limitend = m_perpagecnt;

　　set @sqlcnt = concat('select count(1) into @pagecnt from ',m_table); -- 这条语句很关键，用来得到总数值

　　set @sql = concat('select ',m_column,' from ',m_table);

　　if m_condition is not null and m_condition <> '' then

　　set @sql = concat(@sql,' where ',m_condition);

　　set @sqlcnt = concat(@sqlcnt,' where ',m_condition);

　　end if;

　　if m_orderby is not null and m_orderby <> '' then

　　set @sql = concat(@sql,' order by ',m_orderby);

　　end if;

　　set @sql = concat(@sql, ' limit ', @limitstart, ',', @limitend);

　　prepare s_cnt from @sqlcnt;

　　execute s_cnt;

　　deallocate prepare s_cnt;

　　set m_totalpagecnt = @pagecnt;

　　prepare record from @sql;

　　execute record;

　　deallocate prepare record;

　　end

方法四:mysql的通用存储过程,本着共享的精神,为大家奉献这段mysql分页查询通用存储过程,假设所用数据库为guestbook:

use guestbook;

delimiter $$

drop procedure if exists prc_page_result $$

create procedure prc_page_result (

in currpage      int,

in columns       varchar(500),

in tablename     varchar(500),

in scondition    varchar(500),

in order_field   varchar(100),

in asc_field     int,

in primary_field varchar(100),

in pagesize      int

)

begin

    declare stemp  varchar(1000);

    declare ssql   varchar(4000);

    declare sorder varchar(1000);



    if asc_field = 1 then

        set sorder = concat( order by , order_field, desc );

        set stemp  = <(select min;

    else

        set sorder = concat( order by , order_field, asc );

        set stemp  = >(select max;

    end if;



    if currpage = 1 then

        if scondition <> then

            set ssql = concat(select , columns, from , tablename, where );

            set ssql = concat(ssql, scondition, sorder, limit ?);

        else

            set ssql = concat(select , columns, from , tablename, sorder, limit ?);

        end if;

    else

        if scondition <> then

            set ssql = concat(select , columns, from , tablename);

            set ssql = concat(ssql, where , scondition, and , primary_field, stemp);

            set ssql = concat(ssql, (, primary_field, ), from (select );

            set ssql = concat(ssql, , primary_field, from , tablename, sorder);

            set ssql = concat(ssql, limit , (currpage-1)*pagesize, ) as tabtemp), sorder);

            set ssql = concat(ssql, limit ?);

        else

            set ssql = concat(select , columns, from , tablename);

            set ssql = concat(ssql, where , primary_field, stemp);

            set ssql = concat(ssql, (, primary_field, ), from (select );

            set ssql = concat(ssql, , primary_field, from , tablename, sorder);

            set ssql = concat(ssql, limit , (currpage-1)*pagesize, ) as tabtemp), sorder);

            set ssql = concat(ssql, limit ?);

        end if;

    end if;

    set @ipagesize = pagesize;

    set @squery = ssql;

    prepare stmt from @squery;

    execute stmt using @ipagesize;

end;

$$

delimiter;