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

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

mysql -u root -p < pageresult.sql;

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