本文章收集了四款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);

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

分享到：