本文章收集了四款mysql 分页存储过程实例代码,有高效的分页存储过程以及入门级的和通用的存储过程分页代码,如果你正在学mysql分页存储过程就进来看看吧.
mysql测试版本：5.0.41-community-nt,mysql分页存储过程
drop procedure if exists pr_pager; create procedure pr_pager( in p_table_name varchar(1024), /*表名*/ in p_fields varchar(1024), /*查询字段*/ in p_page_size int, /*每页记录数*/ in p_page_now int, /*当前页*/ in p_order_string varchar(128), /*排序条件(包含order关键字,可为空)*/ in p_where_string varchar(1024), /*where条件(包含where关键字,可为空)*/ out p_out_rows int /*输出记录总数*/ ) not deterministic sql security definer comment '分页存储过程' begin /*定义变量*/ declare m_begin_row int default 0; declare m_limit_string char(64); /*构造语句*/ set m_begin_row = (p_page_now - 1) * p_page_size; set m_limit_string = concat( ' limit ' , m_begin_row, ', ' , p_page_size); set @count_string = concat( 'select count(*) into @rows_total from ' , p_table_name, ' ' , p_where_string); set @main_string = concat( 'select ' , p_fields, ' from ' , p_table_name, ' ' , p_where_string, ' ' , p_order_string, m_limit_string); /*预处理*/ prepare count_stmt from @count_string; execute count_stmt; deallocate prepare count_stmt; set p_out_rows = @rows_total; prepare main_stmt from @main_string; execute main_stmt; deallocate prepare main_stmt; end一款高效的存储过程分页代码,存储过程分页的基本原理:我们先对查找到的记录集(支持输入查找条件_whereclause和排列条件_orderby)的key字段临时存放到临时表,然后构建真正的记录集输出.
create procedure `mysqltestuser_select_pageable`( _whereclause varchar(2000), -- 查找条件 _orderby varchar(2000), -- 排序条件 _pagesize int , -- 每页记录数 _pageindex int , -- 当前页码 _docount bit -- 标志:统计数据/输出数据 ) not deterministic sql security definer comment ' ' begin -- 定义key字段临时表 drop table if exists _temptable_keyid; -- 删除临时表,如果存在 create temporary table _temptable_keyid ( userid int )type=heap; -- 构建动态的sql，输出关键字key的id集合 -- 查找条件 set @sql = 'select userid from mysqltestuser ' ; if (_whereclause is not null) and (_whereclause <> ' ' ) then set @sql= concat(@sql, ' where ' ,_whereclause); end if ; if (_orderby is not null) and (_orderby <> ' ' ) then set @sql= concat( @sql , ' order by ' , _orderby); end if ; -- 准备id记录插入到临时表 set @sql=concat( 'insert into _temptable_keyid(userid) ' , @sql); prepare stmt from @sql; execute stmt ; deallocate prepare stmt; -- key的id集合 [ end ] -- 下面是输出 if (_docount=1) then -- 统计 begin select count (*) as recordcount from _temptable_keyid; end ; else -- 输出记录集 begin -- 计算记录的起点位置 set @startpoint = ifnull((_pageindex-1)*_pagesize,0); set @sql= ' select a.* from mysqltestuser a inner join _temptable_keyid b on a.userid =b.userid '; set @sql=concat(@sql, " limit " ,@startpoint, " , " ,_pagesize); prepare stmt from @sql; execute stmt ; deallocate prepare stmt; end ; end if ; drop table _temptable_keyid; end ;下面是mysqltestuser表的ddl:
create table `mysqltestuser` ( `userid` int (11) not null auto_increment, ` name ` varchar (50) default null , `chinesename` varchar (50) default null , `registerdatetime` datetime default null , `jf` decimal (20,2) default null , `description` longtext, primary key (`userid`) ) engine=innodb default charset=gb2312;插入些数据:
insert into `mysqltestuser` (`userid`, `name`, `chinesename`, `registerdatetime`, `jf`, `description`) values (1, 'xuu1 ' , 'www.aimeige.com.cn ' , '2007-03-29 12:54:41 ' ,1.5, 'description1 ' ), (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的一个记录.
create procedure p_pagelist ( m_pageno int , m_perpagecnt int , m_column varchar(1000) , m_table varchar(1000) , m_condition varchar(1000), m_orderby varchar(200) , out m_totalpagecnt int ) begin set @pagecnt = 1; -- 总记录数 set @limitstart = (m_pageno - 1)*m_perpagecnt; set @limitend = m_perpagecnt; set @sqlcnt = concat( 'select count(1) into @pagecnt from ' ,m_table); -- 这条语句很关键，用来得到总数值 set @sql = concat( 'select ' ,m_column, ' from ' ,m_table); if m_condition is not null and m_condition <> '' then set @sql = concat(@sql, ' where ' ,m_condition); set @sqlcnt = concat(@sqlcnt, ' where ' ,m_condition); end if ; if m_orderby is not null and m_orderby <> '' then set @sql = concat(@sql, ' order by ' ,m_orderby); end if ; set @sql = concat(@sql, ' limit ' , @limitstart, ',' , @limitend); prepare s_cnt from @sqlcnt; execute s_cnt; deallocate prepare s_cnt; set m_totalpagecnt = @pagecnt; prepare record from @sql; execute record; deallocate prepare record; end方法四: mysql的通用存储过程,本着共享的精神,为大家奉献这段mysql分页查询通用存储过程,假设所用数据库为guestbook:
use guestbook; delimiter $$ drop procedure if exists prc_page_result $$ create procedure prc_page_result ( in currpage int, in columns varchar(500), in tablename varchar(500), in scondition varchar(500), in order_field varchar(100), in asc_field int, in primary_field varchar(100), in pagesize int ) begin declare stemp varchar(1000); declare ssql varchar(4000); declare sorder varchar(1000); if asc_field = 1 then set sorder = concat( order by , order_field, desc ); set stemp = <(select min; else set sorder = concat( order by , order_field, asc ); set stemp = >(select max; end if ; if currpage = 1 then if scondition <> then set ssql = concat(select , columns, from , tablename, where ); set ssql = concat(ssql, scondition, sorder, limit ?); else set ssql = concat(select , columns, from , tablename, sorder, limit ?); end if ; else if scondition <> then set ssql = concat(select , columns, from , tablename); set ssql = concat(ssql, where , scondition, and , primary_field, stemp); set ssql = concat(ssql, (, primary_field, ), from (select ); set ssql = concat(ssql, , primary_field, from , tablename, sorder); set ssql = concat(ssql, limit , (currpage-1)*pagesize, ) as tabtemp), sorder); set ssql = concat(ssql, limit ?); else set ssql = concat(select , columns, from , tablename); set ssql = concat(ssql, where , primary_field, stemp); set ssql = concat(ssql, (, primary_field, ), from (select ); set ssql = concat(ssql, , primary_field, from , tablename, sorder); set ssql = concat(ssql, limit , (currpage-1)*pagesize, ) as tabtemp), sorder); set ssql = concat(ssql, limit ?); end if ; end if ; set @ipagesize = pagesize; set @squery = ssql; prepare stmt from @squery; execute stmt using @ipagesize; end ; $$ delimiter;可以存储为数据库脚本,然后用命令导入:
mysql -u root -p < pageresult.sql;
调用:call prc_page_result(1, "*", "tablename", "", "columnname", 1, "pkid", 25);
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did27968