mysql 分页存储过程

/*test"
CALL sp_viewPage( 
'*'#查询字段 
,'userupdatelog'#表名 
,'1=1'#条件 
,'Id desc'#排序 
,1 #页码 
,20 #每页记录数 
,@totalcount #输出总记录数 
,@pagecount #输出用页数 
); 
SELECT @totalcount,@pagecount; 
*/

DROP PROCEDURE IF EXISTS `sp_viewPage`;

CREATE PROCEDURE sp_viewPage(
_fields VARCHAR(1000), #要查询的字段，用逗号(,)分隔 
_tables TEXT, #要查询的表 
_where VARCHAR(2000), #查询条件 
_orderby VARCHAR(200), #排序规则 
_pageindex INT, #查询页码 
_pageSize INT, #每页记录数 
/*_sumfields VARCHAR(200),#求和字段 */
#输出参数 
OUT _totalcount INT, #总记录数 
OUT _pagecount INT #总页数 
/* OUT _sumResult VARCHAR(2000)#求和结果 */
)
BEGIN
#140529-xxj-分页存储过程 
#计算起始行号 
SET @startRow = _pageSize * (_pageIndex - 1); 
SET @pageSize = _pageSize; 
SET @rowindex = 0; #行号 

#合并字符串 
SET @strsql = CONCAT( 
#'select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,' #记录行号 
'select sql_calc_found_rows ' 
,_fields 
,' from ' 
,_tables 
,CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' where ', _where) END 
,CASE IFNULL(_orderby, '') WHEN '' THEN '' ELSE CONCAT(' order by ', _orderby) END 
,' limit ' 
,@startRow 
,',' 
,@pageSize 
); 

PREPARE strsql FROM @strsql;#定义预处理语句 
EXECUTE strsql; #执行预处理语句 
DEALLOCATE PREPARE strsql; #删除定义 
#通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数 
SET _totalcount = FOUND_ROWS(); 

#计算总页数 
IF (_totalcount <= _pageSize) THEN 
SET _pagecount = 1; 
ELSE IF (_totalcount % _pageSize > 0) THEN 
SET _pagecount = _totalcount DIV _pageSize + 1; 
ELSE 
SET _pagecount = _totalcount DIV _pageSize; 
END IF; 
END IF;

END

SET @PageIndex= 1;
SET @PageSize=10 ;
SET @TableName='item_prop_values' ;
SET @SelectFields='*' ;
SET @ConditionWhere='cid=1101' ;
SET @SortField='id' ;
SET @IsDesc=0 ;
SET @TotalRecords= 0;
SET @TotalPageCount= 0;
SET @ResetOrder= 0;
SET @SQLString= '';
CALL MicrobeORM_GetRecordsByPageSQLString(
@PageIndex,
@PageSize,
@TableName,
@SelectFields,
@ConditionWhere,
@SortField,
@IsDesc,
@TotalRecords,
@TotalPageCount,
@ResetOrder,
@SQLString);
select @TotalRecords;
SELECT @TotalPageCount;
select @SQLString;

CREATE DEFINER = 'root'@'%'
PROCEDURE xyproduct_test.MicrobeORM_GetRecordsByPageSQLString(
PageIndex int,
PageSize int,
TableName varchar(4000),
SelectFields varchar(8000), #查询字段，默认为 *
ConditionWhere varchar(4000), #条件例如 DirectoryID=4
SortField varchar(500),
IsDesc bit, #= 1, #是否倒序
OUT TotalRecords int,#总记录数
OUT TotalPageCount int ,#输出总页数
OUT ResetOrder bit ,#####- 1表示读取数据的时候 排序要反过来
OUT SQLString varchar(4000)
)
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;#解锁事务

#######################################--init paras--begin########################################
IF (SelectFields IS NULL OR SelectFields='') THEN
SET SelectFields='*';
END IF;
IF (IsDesc IS NULL) THEN
SET IsDesc='1';
END IF;
IF (TotalRecords<=0) THEN
SET TotalRecords=-1;
END IF;
#######################################--init paras--end########################################

#######################################-第一步 ：查询限制条件的组合--begin---#######################################
SET @WhereString1='';
SET @WhereString2='';
IF ConditionWhere IS NULL or LENGTH( ConditionWhere)<=0 THEN
SET @WhereString1 = '';
SET @WhereString2 = ' WHERE ';
ELSE
SET @WhereString1 = CONCAT(' WHERE ' , ConditionWhere);
SET @WhereString2 = CONCAT(' WHERE ' , ConditionWhere , ' AND ');
END IF;
#######################################-第一步 ：查询限制条件的组合--end---#######################################

#######################################-设置完毕查询条件后 查询本次符合条件的记录数 页数--begin#######################################
##########################--------总记录数-----begin--------##########################
SET @sqlCmd='';

IF ConditionWhere IS NULL OR ConditionWhere = ''
THEN
#没有查询条件

SET @sqlCmd= CONCAT('SELECT @TotalRecordsCount:= COUNT(*) FROM ', TableName);

PREPARE strsql FROM @sqlCmd;#定义预处理语句
EXECUTE strsql; #执行预处理语句
DEALLOCATE PREPARE strsql; #删除定义
#通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数
SET TotalRecords = @TotalRecordsCount;

ELSE
SET @sqlCmd= CONCAT('SELECT @TotalRecordsCount:=COUNT(*) FROM ', TableName,' ',@WhereString1);

PREPARE strsql FROM @sqlCmd;#定义预处理语句
EXECUTE strsql; #执行预处理语句
DEALLOCATE PREPARE strsql; #删除定义
#通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数
SET TotalRecords = @TotalRecordsCount;#FOUND_ROWS();
END IF;

##########################--------总记录数-----end--------##########################

##########################---------总记录数有值的时候,计算页数---begin--##########################
IF(TotalRecords>0)
THEN
SET @modNum =0; #求模运算
SET @modNum=TotalRecords%PageSize;
IF @modNum=0#整除尽
THEN
SET TotalPageCount=TotalRecords/PageSize;
ELSE#有余数
SET TotalPageCount=CEIL(TotalRecords/PageSize);
END IF;
END IF;
##########################---------总记录数有值的时候,计算页数---end--##########################



##########################---------开始查询，组合SQL语句----------begin##########################
IF PageIndex = 0 THEN
SET SQLString = CONCAT('SELECT '
, SelectFields
, ' FROM '
,TableName
,@WhereString1
,' ORDER BY '
, SortField);
IF IsDesc = 1 THEN
SET SQLString =CONCAT( SQLString , ' DESC ');
END IF;

SET ResetOrder=0;

#######设定取出的记录数目###########
SET SQLString =CONCAT( SQLString , ' LIMIT ',PageSize);

ELSE ######下面对页码 页数进行了再次确认统计
SET SQLString='';
SET @GetFromLast =0;
IF TotalRecords=-1 THEN
SET @GetFromLast=0;
ELSE
SET @TotalPage=0;

SET @ResidualCount=TotalRecords%PageSize;
#######----------------------------------------------临时页总数------------------------------------------------------------------------------------
IF @ResidualCount=0 THEN
SET @TotalPage=TotalRecords/PageSize;
ELSE
SET @TotalPage=CEIL(TotalRecords/PageSize);
END IF;

#######-----------------------------------------------是否从后面半部分取数据-----------------------------------------------------------------------------------
IF PageIndex>@TotalPage/2 THEN #从最后页算上来
SET @GetFromLast=1;
ELSE
SET @GetFromLast=0;
END IF;

IF @GetFromLast=1 THEN
IF PageIndex=@TotalPage-1 THEN

IF @ResidualCount=0 THEN
SET @ResidualCount=PageSize;
END IF;

SET SQLString=CONCAT( 'SELECT '
, SelectFields
, ' FROM '
, TableName
, @WhereString1
, ' ORDER BY '
,SortField);
IF IsDesc = 0 then#反过来
SET SQLString = CONCAT( SQLString , ' DESC ');
END IF;
SET SQLString =CONCAT( SQLString , ' LIMIT ',@ResidualCount);
SET ResetOrder=1;


ELSE IF PageIndex>@TotalPage-1 THEN #已经超过最大页数
SET SQLString =CONCAT( 'SELECT '
,SelectFields
, ' FROM '
,TableName
, ' WHERE 1=2');
SET ResetOrder=0;

ELSE
SET PageIndex=@TotalPage-(PageIndex+1);#-
IF IsDesc=1 THEN
SET IsDesc=0;
ELSE
SET IsDesc=1;
END IF;
SET ResetOrder=1;
END IF;

END IF;
ELSE
SET ResetOrder=0;
END IF;

END IF;
############--------------------------------设定完毕查询顺序-------------------------------##################
IF SQLString='' THEN
SET @TopCount =0;
IF @GetFromLast=1 THEN
IF @ResidualCount > 0 THEN
SET @TopCount=PageSize * (PageIndex-1)+@ResidualCount;
ELSE
SET @TopCount=PageSize * (PageIndex)+@ResidualCount;
END IF;
IF @TopCount = 0 THEN
SET @TopCount = PageSize;
END IF;

ELSE
SET @TopCount=PageSize * PageIndex;
END IF;

IF IsDesc = 1 THEN
SET SQLString = CONCAT( 'SELECT '
, SelectFields
,' FROM '
,TableName
, @WhereString2
,SortField
,' <
(SELECT Min('
, SortField
,') FROM
(SELECT '
,SortField
,' FROM '
,TableName
, @WhereString1
,' ORDER BY '
,SortField
,' DESC limit '
,@TopCount
,') AS IDX)
ORDER BY '
,SortField
,' DESC '
,'limit '
,PageSize);
ELSE
SET SQLString = CONCAT( 'SELECT '
, SelectFields
,' FROM '
,TableName
, @WhereString2
, SortField
,' >
(SELECT Max('
,SortField
,') FROM
(SELECT '
,SortField
,' FROM '
,TableName
, @WhereString1
, ' ORDER BY '
, SortField
,' ASC limit '
,@TopCount
,') AS IDX)
ORDER BY '
,SortField
,' ASC '
,'limit ',PageSize) ;
END IF;
END IF;

END IF;

############---------------执行SQL查询---------------------##################
SET @sqlQueryCmd=SQLString;
PREPARE strsql FROM @sqlQueryCmd;#定义预处理语句
EXECUTE strsql; #执行预处理语句
DEALLOCATE PREPARE strsql; #删除定义

############---------------执行完毕查询，加锁---------------------##################
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
END