mysql 页存储_mysql 分页存储过程

最新推荐文章于 2023-11-09 23:13:59 发布

Denny W

最新推荐文章于 2023-11-09 23:13:59 发布

阅读量261

点赞数

文章标签： mysql 页存储

本文链接：https://blog.csdn.net/weixin_29589063/article/details/113138428

版权

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 */

15 DROP PROCEDURE IF EXISTS`sp_viewPage`;16

17 CREATE PROCEDUREsp_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 ,_fields42 ,'from'

43 ,_tables44 ,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 EXECUTEstrsql; #执行预处理语句54 DEALLOCATE PREPAREstrsql; #删除定义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--##########################

##########################---------开始查询，组合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;

ELSE

SET ResetOrder=0;

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;

############---------------执行SQL查询---------------------##################

SET @sqlQueryCmd=SQLString;

PREPARE strsql FROM @sqlQueryCmd;#定义预处理语句

EXECUTE strsql; #执行预处理语句

DEALLOCATE PREPARE strsql; #删除定义

############---------------执行完毕查询，加锁---------------------##################

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

END

Denny W

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 页存储_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 */1415 DROP...
复制链接

扫一扫