mysql 分页存储过程 统计_MySQL 分页存储过程

/*

--名称：MYSQL版查询分页存储过程

--输入参数：@fields -- 要查询的字段用逗号隔开

--输入参数：@tables -- 要查询的表

--输入参数：@where -- 查询条件

--输入参数：@orderby -- 排序字段

--输出参数：@page -- 当前页计数从1开始

--输出参数：@pagesize -- 每页大小

--输出参数：@totalcount -- 总记录数

--输出参数：@pagecount -- 总页数

--输出参数：_sumfields -- 统计字段

*/

DROP PROCEDURE IF EXISTS Proc_Page;

CREATE PROCEDURE Proc_Page

(

in _fields varchar(2000),

in _tables text,

in _where varchar(2000),

in _orderby varchar(200),

in _pageindex int,

in _pagesize int,

in _sumfields varchar(200),

out _totalcount int ,

out _pagecount int

)

begin

set @startRow = _pageSize*(_pageIndex -1);

set @pageSize = _pageSize;

set @rowindex = 0;

set @strsql = CONCAT('select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,',_fields,' from ',_tables,case ifnull(_where,'') when '' then '' else concat(' where ',_where) end,' order by ',_orderby,' limit ',@startRow,',',@pageSize);

prepare strsql from @strsql;

execute strsql;

deallocate prepare strsql;

set _totalcount = found_rows();

if (_totalcount <= _pageSize) then

set _pagecount = 1;

else if (_totalcount % _pageSize > 0) then

set _pagecount = _totalcount / _pageSize + 1;

else

set _pagecount = _totalcount / _pageSize;

end if;

end if;

if(ifnull(_sumfields,'') <> '')

then

set @sumsql = contact('select ',_sumfields,' from ',_tables,case ifnull(_where,'') when '' then '' else concat(' where ',_where) end);

prepare sumsql from @sumsql;

execute sumsql;

deallocate prepare sumsql;

end if;

end