会员登录 注册新帐号电脑店行业门户 | U盘启动工具

首页

 电脑店主页 > 产品技术 > 系统应用 > 数据库 > MySQL > MySQL分页查询通用存储过程的代码总结

MySQL分页查询通用存储过程的代码总结

发布时间:2011-10-05 11:54      点击:     关注官方微博:

此文章主要向大家讲述的是MySQL分页查询通用存储过程,我们前一段时间没有给出SQLServer转到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 Prima(最完善的虚拟主机管理系统)
ry_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 ', 
Prima(最完善的虚拟主机管理系统)ry_field, sTemp);  
set sSql = concat(sSql, '(', Prima(最完善的虚拟主机管理系统)ry_field, ')', '
 from (select ');  set sSql = concat(sSql, ' ', 
Prima(最完善的虚拟主机管理系统)ry_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 ', 
Prima(最完善的虚拟主机管理系统)ry_field, sTemp); 
 set sSql = concat(sSql, 
'(', Prima(最完善的虚拟主机管理系统)ry_field, ')', '
 from (select ');  set sSql = concat(sSql, ' ', 
Prima(最完善的虚拟主机管理系统)ry_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(和PHP搭配之最佳组合) -u root -p < pageResult.sql;

调用:call prc_page_result(1, "*", "Tablename", "", "columnname", 1, "PKID", 25);

以上的相关内容就是对MySQL分页查询通用存储过程的介绍,望你能有所收获。

★★★ 电脑店行业门户(www.diannaodian.com)独家文章,欢迎大家转载 ★★★

    相关文章