VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > PHP >

  • mysql 分页存储过程实例

本文章收集了四款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. ) 
  12.     not deterministic 
  13.      sql security definer 
  14.      comment '分页存储过程' 
  15.      
  16. begin 
  17.     /*定义变量*/ 
  18.     declare m_begin_row int default 0; 
  19.     declare m_limit_string char(64); 
  20.     /*构造语句*/     
  21.     set m_begin_row = (p_page_now - 1) * p_page_size; 
  22.     set m_limit_string = concat(' limit ', m_begin_row, ', ', p_page_size); 
  23.      
  24.     set @count_string = concat('select count(*) into @rows_total from ', p_table_name, ' ', p_where_string); 
  25.     set @main_string = concat('select ', p_fields, ' from ', p_table_name, ' ', p_where_string, ' ', p_order_string, m_limit_string); 
  26.     /*预处理*/ 
  27.     prepare count_stmt from @count_string; 
  28.     execute count_stmt; 
  29.     deallocate prepare count_stmt; 
  30.     set p_out_rows = @rows_total; 
  31.     prepare main_stmt from @main_string; 
  32.     execute main_stmt; 
  33.     deallocate prepare main_stmt; 
  34.      
  35. 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. ) 
  14. begin 
  15.     declare stemp  varchar(1000); 
  16.     declare ssql   varchar(4000); 
  17.     declare sorder varchar(1000); 
  18.      
  19.     if asc_field = 1 then 
  20.         set sorder = concat( order by , order_field, desc ); 
  21.         set stemp  = <(select min; 
  22.     else 
  23.         set sorder = concat( order by , order_field, asc ); 
  24.         set stemp  = >(select max; 
  25.     end if; 
  26.      
  27.     if currpage = 1 then 
  28.         if scondition <> then 
  29.             set ssql = concat(select , columns, from , tablename, where ); 
  30.             set ssql = concat(ssql, scondition, sorder, limit ?); 
  31.         else 
  32.             set ssql = concat(select , columns, from , tablename, sorder, limit ?); 
  33.         end if; 
  34.     else 
  35.         if scondition <> then 
  36.             set ssql = concat(select , columns, from , tablename); 
  37.             set ssql = concat(ssql, where , scondition, and , primary_field, stemp); 
  38.             set ssql = concat(ssql, (, primary_field, ), from (select ); 
  39.             set ssql = concat(ssql, , primary_field, from , tablename, sorder); 
  40.             set ssql = concat(ssql, limit , (currpage-1)*pagesize, ) as tabtemp), sorder); 
  41.             set ssql = concat(ssql, limit ?); 
  42.         else 
  43.             set ssql = concat(select , columns, from , tablename); 
  44.             set ssql = concat(ssql, where , primary_field, stemp); 
  45.             set ssql = concat(ssql, (, primary_field, ), from (select ); 
  46.             set ssql = concat(ssql, , primary_field, from , tablename, sorder); 
  47.             set ssql = concat(ssql, limit , (currpage-1)*pagesize, ) as tabtemp), sorder); 
  48.             set ssql = concat(ssql, limit ?); 
  49.         end if; 
  50.     end if; 
  51.     set @ipagesize = pagesize; 
  52.     set @squery = ssql; 
  53.     prepare stmt from @squery; 
  54.     execute stmt using @ipagesize; 
  55. end; 
  56. $$ 
  57. delimiter; 

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

mysql -u root -p < pageresult.sql;

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

 



出处：http://www.phpfensi.com/php/20140911/5374.html


相关教程