四款mysql 分頁存儲過程實例 本文章收集了四款mysql 分頁存儲過程實例代碼，有高效的分頁存儲過程以及入門級的和通用的存儲過程分頁代碼，如果你正在學mysql分頁存儲過程就進來看看吧。
四款mysql教程 分頁存儲過程實例
本文章收集了四款mysql 分頁存儲過程實例代碼，有高效的分頁存儲過程以及入門級的和通用的存儲過程分頁代碼，如果你正在學mysql分頁存儲過程就進來看看吧。
mysql測試版本：5.0.41-community-nt
/*****************************************************
mysql分頁存儲過程
吳劍 2009-07-02
*****************************************************/
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.bKjia.c0m ', '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);
*/
?>
