本文章收集了四款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),
-
in p_where_string varchar(1024),
-
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
