当前位置:首页 > PHP教程 > php分页 > 列表

mysql 分页存储过程实例

发布:smiling 来源: PHP粉丝网  添加日期:2014-09-11 20:03:58 浏览: 评论:0 

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

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

  1. drop procedure if exists pr_pager; 
  2. create procedure pr_pager( 
  3.     in     p_table_name        varchar(1024),        /*表名*/ 
  4.     in     p_fields            varchar(1024),        /*查询字段*/ 
  5.     in     p_page_size            int,                /*每页记录数*/ 
  6.     in     p_page_now            int,                /*当前页*/ 
  7.     in     p_order_string        varchar(128),        /*排序条件(包含order关键字,可为空)*/ 
  8.     in     p_where_string        varchar(1024),        /*where条件(包含where关键字,可为空)*/ 
  9.      out     p_out_rows            int                    /*输出记录总数*/ 
  10.      
  11.     not deterministic 
  12.      sql security definer 
  13.      comment '分页存储过程' 
  14.      
  15. begin 
  16.     /*定义变量*/ 
  17.     declare m_begin_row int default 0; 
  18.     declare m_limit_string char(64); 
  19.     /*构造语句*/     
  20.     set m_begin_row = (p_page_now - 1) * p_page_size; 
  21.     set m_limit_string = concat(' limit ', m_begin_row, ', ', p_page_size); 
  22.      
  23.     set @count_string = concat('select count(*) into @rows_total from ', p_table_name, ' ', p_where_string); 
  24.     set @main_string = concat('select ', p_fields, ' from ', p_table_name, ' ', p_where_string, ' ', p_order_string, m_limit_string); 
  25.     /*预处理*/ 
  26.     prepare count_stmt from @count_string; 
  27.     execute count_stmt; 
  28.     deallocate prepare count_stmt; 
  29.     set p_out_rows = @rows_total; 
  30.     prepare main_stmt from @main_string; 
  31.     execute main_stmt; 
  32.     deallocate prepare main_stmt; 
  33.      
  34. end 

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

  1. create   procedure   `mysqltestuser_select_pageable`(  
  2.         _whereclause   varchar(2000),     --   查找条件  
  3.         _orderby   varchar(2000),     --   排序条件  
  4.         _pagesize     int   ,       --   每页记录数  
  5.         _pageindex   int   ,     --   当前页码  
  6.         _docount       bit       --   标志:统计数据/输出数据  
  7. )  
  8.         not   deterministic  
  9.         sql   security   definer  
  10.         comment   ' '  
  11. begin  
  12.   --   定义key字段临时表  
  13.   drop   table   if   exists   _temptable_keyid;     --   删除临时表,如果存在  
  14.   create   temporary     table     _temptable_keyid  
  15.   (  
  16. userid   int  
  17.   )type=heap; 
  18.   --   构建动态的sql,输出关键字key的id集合  
  19.   --   查找条件  
  20.   set   @sql   =   'select     userid   from   mysqltestuser ';  
  21.   if   (_whereclause   is   not   null)     and   (_whereclause   <>   ' ')   then  
  22.   set   @sql=   concat(@sql,   '   where   '   ,_whereclause);  
  23.   end   if
  24.   if   (_orderby   is   not   null)     and     (_orderby   <> ' ')   then  
  25.   set   @sql=   concat(   @sql   ,   '   order   by   '   ,   _orderby);  
  26.   end   if
  27.   --   准备id记录插入到临时表  
  28.   set   @sql=concat( 'insert   into   _temptable_keyid(userid) ',   @sql);  
  29.   prepare   stmt   from   @sql;  
  30.   execute   stmt   ;  
  31.   deallocate   prepare   stmt;  
  32. --   key的id集合     [end
  33. --   下面是输出  
  34. if   (_docount=1)   then     --   统计  
  35.           begin  
  36.                     select   count(*)   as   recordcount   from   _temptable_keyid;  
  37.           end;  
  38. else                                   --   输出记录集  
  39.           begin  
  40.                   --   计算记录的起点位置  
  41.   set   @startpoint   =   ifnull((_pageindex-1)*_pagesize,0);  
  42.                   set   @sql= '                 select           a.*  
  43.       from       mysqltestuser   a  
  44.       inner   join   _temptable_keyid   b  
  45.       on     a.userid   =b.userid     '; 
  46.   set   @sql=concat(@sql, "     limit     ",@startpoint, "   , ",_pagesize);  
  47.                   prepare   stmt   from   @sql;  
  48.   execute   stmt   ;  
  49.   deallocate   prepare   stmt;  
  50.           end;  
  51. end   if
  52.   drop   table   _temptable_keyid;  
  53. end

下面是mysqltestuser表的ddl:

  1. create   table   `mysqltestuser`   (  
  2.     `userid`   int(11)   not   null   auto_increment,  
  3.     `name`   varchar(50)   default   null,  
  4.     `chinesename`   varchar(50)   default   null,  
  5.     `registerdatetime`   datetime   default   null,  
  6.     `jf`   decimal(20,2)   default   null,  
  7.     `description`   longtext,  
  8.     primary   key     (`userid`)  
  9. )   engine=innodb   default   charset=gb2312; 

插入些数据:

  1. insert   into   `mysqltestuser`   (`userid`,   `name`,   `chinesename`,   `registerdatetime`,   `jf`,   `description`)   values    
  2.     (1, 'xuu1 ''www.aimeige.com.cn ''2007-03-29   12:54:41 ',1.5, 'description1 '),  
  3.     (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的一个记录.

  1. create procedure p_pagelist 
  2.   ( 
  3.   m_pageno int , 
  4.   m_perpagecnt int , 
  5.   m_column varchar(1000) , 
  6.   m_table varchar(1000) , 
  7.   m_condition varchar(1000), 
  8.   m_orderby varchar(200) , 
  9.   out m_totalpagecnt int 
  10.   ) 
  11.   begin 
  12.   set @pagecnt = 1; -- 总记录数 
  13.   set @limitstart = (m_pageno - 1)*m_perpagecnt; 
  14.   set @limitend = m_perpagecnt; 
  15.   set @sqlcnt = concat('select count(1) into @pagecnt from ',m_table); -- 这条语句很关键,用来得到总数值 
  16.   set @sql = concat('select ',m_column,' from ',m_table); 
  17.   if m_condition is not null and m_condition <> '' then 
  18.   set @sql = concat(@sql,' where ',m_condition); 
  19.   set @sqlcnt = concat(@sqlcnt,' where ',m_condition); 
  20.   end if
  21.   if m_orderby is not null and m_orderby <> '' then 
  22.   set @sql = concat(@sql,' order by ',m_orderby); 
  23.   end if
  24.   set @sql = concat(@sql, ' limit ', @limitstart, ',', @limitend); 
  25.   prepare s_cnt from @sqlcnt; 
  26.   execute s_cnt; 
  27.   deallocate prepare s_cnt; 
  28.   set m_totalpagecnt = @pagecnt; 
  29.   prepare record from @sql; 
  30.   execute record; 
  31.   deallocate prepare record; 
  32.   end 

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

  1. use guestbook; 
  2. delimiter $$ 
  3. drop procedure if exists prc_page_result $$ 
  4. create procedure prc_page_result ( 
  5. in currpage      int, 
  6. in columns       varchar(500), 
  7. in tablename     varchar(500), 
  8. in scondition    varchar(500), 
  9. in order_field   varchar(100), 
  10. in asc_field     int, 
  11. in primary_field varchar(100), 
  12. in pagesize      int 
  13. begin 
  14.     declare stemp  varchar(1000); 
  15.     declare ssql   varchar(4000); 
  16.     declare sorder varchar(1000); 
  17.      
  18.     if asc_field = 1 then 
  19.         set sorder = concat( order by , order_field, desc ); 
  20.         set stemp  = <(select min; 
  21.     else 
  22.         set sorder = concat( order by , order_field, asc ); 
  23.         set stemp  = >(select max; 
  24.     end if
  25.      
  26.     if currpage = 1 then 
  27.         if scondition <> then 
  28.             set ssql = concat(select , columns, from , tablename, where ); 
  29.             set ssql = concat(ssql, scondition, sorder, limit ?); 
  30.         else 
  31.             set ssql = concat(select , columns, from , tablename, sorder, limit ?); 
  32.         end if
  33.     else 
  34.         if scondition <> then 
  35.             set ssql = concat(select , columns, from , tablename); 
  36.             set ssql = concat(ssql, where , scondition, and , primary_field, stemp); 
  37.             set ssql = concat(ssql, (, primary_field, ), from (select ); 
  38.             set ssql = concat(ssql, , primary_field, from , tablename, sorder); 
  39.             set ssql = concat(ssql, limit , (currpage-1)*pagesize, ) as tabtemp), sorder); 
  40.             set ssql = concat(ssql, limit ?); 
  41.         else 
  42.             set ssql = concat(select , columns, from , tablename); 
  43.             set ssql = concat(ssql, where , primary_field, stemp); 
  44.             set ssql = concat(ssql, (, primary_field, ), from (select ); 
  45.             set ssql = concat(ssql, , primary_field, from , tablename, sorder); 
  46.             set ssql = concat(ssql, limit , (currpage-1)*pagesize, ) as tabtemp), sorder); 
  47.             set ssql = concat(ssql, limit ?); 
  48.         end if
  49.     end if
  50.     set @ipagesize = pagesize; 
  51.     set @squery = ssql; 
  52.     prepare stmt from @squery; 
  53.     execute stmt using @ipagesize; 
  54. end
  55. $$ 
  56. delimiter; 

可以存储为数据库脚本,然后用命令导入:

mysql -u root -p < pageresult.sql;

调用:call prc_page_result(1, "*", "tablename", "", "columnname", 1, "pkid", 25);

Tags: mysql分页存储 mysql分页过程

分享到: