— MySQL 5.7.35
CREATE DEFINER=`root`@`localhost` PROCEDURE `prPager`(
IN `PageSize` INT,
IN `CurrentCount` INT,
IN `TableName` VARCHAR (200),
IN `w` VARCHAR (200),
IN `o` VARCHAR (200)
,
OUT `TotalCount` INT
)
BEGIN
DECLARE `sqlSelect` VARCHAR (2000) ;
DECLARE `sqlGetCount` VARCHAR (2000) ;
— SET @sqlSelect = ‘SELECT * FROM (SELECT @rownum:=@rownum+1 AS rownum,e.*
— FROM ‘ + @TableName + ‘ e,(SELECT @rownum:=0) t
— WHERE ‘ + @w + ‘ ORDER BY e.’ + @o + ‘) c
— LIMIT ‘ +CONVERT(@CurrentCount,SIGNED)+ ‘,’ +CONVERT((@CurrentCount + @PageSize) ,SIGNED) ;
SET @sqlSelect = “select * from ” + TableName + ” WHERE ” + w + ” order by ” + o + ” limit ” + (CurrentCount – 1) * PageSize + “,” + PageSize ;
SET @sqlGetCount = ‘SELECT @TotalCount = COUNT(*) FROM ‘+ @TableName + ‘ WHERE ‘ + @w ;
PREPARE s1 FROM @sqlSelect ;
— 预定义sql
EXECUTE s1 ;
— 传入两个会话变量来填充sql中的 ?
DEALLOCATE PREPARE s1 ;
— 释放连接
PREPARE s2 FROM @sqlGetCount ;
— 预定义sql
EXECUTE s2 ;
— 传入两个会话变量来填充sql中的 ?
DEALLOCATE PREPARE s2 ;
select FOUND_ROW() into @TotalCount;
END