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