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