mysql 分页存储过程
1 /*test" 2 CALL sp_viewPage( 3 '*'#查询字段 4 ,'userupdatelog'#表名 5 ,'1=1'#条件 6 ,'Id desc'#排序 7 ,1 #页码 8 ,20 #每页记录数 9 ,@totalcount #输出总记录数 10 ,@pagecount #输出用页数 11 ); 12 SELECT @totalcount,@pagecount; 13 */ 14 15 DROP PROCEDURE IF EXISTS `sp_viewPage`; 16 17 CREATE PROCEDURE sp_viewPage( 18 _fields VARCHAR(1000), #要查询的字段，用逗号(,)分隔 19 _tables TEXT, #要查询的表 20 _where VARCHAR(2000), #查询条件 21 _orderby VARCHAR(200), #排序规则 22 _pageindex INT, #查询页码 23 _pageSize INT, #每页记录数 24 /*_sumfields VARCHAR(200),#求和字段 */ 25 #输出参数 26 OUT _totalcount INT, #总记录数 27 OUT _pagecount INT #总页数 28 /* OUT _sumResult VARCHAR(2000)#求和结果 */ 29 ) 30 BEGIN 31 #140529-xxj-分页存储过程 32 #计算起始行号 33 SET @startRow = _pageSize * (_pageIndex - 1); 34 SET @pageSize = _pageSize; 35 SET @rowindex = 0; #行号 36 37 #合并字符串 38 SET @strsql = CONCAT( 39 #'select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,' #记录行号 40 'select sql_calc_found_rows ' 41 ,_fields 42 ,' from ' 43 ,_tables 44 ,CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' where ', _where) END 45 ,CASE IFNULL(_orderby, '') WHEN '' THEN '' ELSE CONCAT(' order by ', _orderby) END 46 ,' limit ' 47 ,@startRow 48 ,',' 49 ,@pageSize 50 ); 51 52 PREPARE strsql FROM @strsql;#定义预处理语句 53 EXECUTE strsql; #执行预处理语句 54 DEALLOCATE PREPARE strsql; #删除定义 55 #通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数 56 SET _totalcount = FOUND_ROWS(); 57 58 #计算总页数 59 IF (_totalcount <= _pageSize) THEN 60 SET _pagecount = 1; 61 ELSE IF (_totalcount % _pageSize > 0) THEN 62 SET _pagecount = _totalcount DIV _pageSize + 1; 63 ELSE 64 SET _pagecount = _totalcount DIV _pageSize; 65 END IF; 66 END IF; 67 68 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
