MySql分页存储过程

分页获取数据 code

DELIMITER $$ 
DROP PROCEDURE IF EXISTS `testDB`.`GetRecordAsPage` $$
CREATE PROCEDURE `testDB`.`GetRecordAsPage` (in tbName varchar(800),in fldName varchar(1000),
in strWhere varchar(500),in pageIndex int,in pageSize int,in orderType int,in sortName varchar(50))
BEGIN
declare startRow int;
declare sqlStr varchar(1000);
declare limitTemp varchar(1000);
declare orderTemp varchar(1000); 
set startRow = (pageIndex-1)*pageSize; 
set sqlStr = CONCAT('SELECT ',fldName,' from ',tbName);
set limitTemp = CONCAT(' limit ',startRow,',',pageSize);
set orderTemp = CONCAT(' order by ',sortName);
if orderType = 0 then
set orderTemp = CONCAT(orderTemp,' ASC ');
else
set orderTemp = CONCAT(orderTemp,' DESC ');
end if; 
set @sqlString = CONCAT(sqlStr,' ',strWhere,orderTemp,limitTemp); 
prepare sqlstmt from @sqlString;
execute sqlstmt;
deallocate prepare sqlstmt; 
END $$ 
DELIMITER ;
//执行存储过程
CALL `testDB`.`GetRecordAsPage`('article','*','',1,2,1,'aid');

　　

 获得总记录数

DELIMITER $$
DROP PROCEDURE IF EXISTS `testDB`.`GetRecordCount` $$
CREATE PROCEDURE `testDB`.`GetRecordCount`(in tbName varchar(1000),in strWhere varchar(1000))
BEGIN
set @strSQL=concat('select count(*) as countStr from ',tbName,strWhere);
prepare sqlstmt from @strSQL;
execute sqlstmt;
deallocate prepare sqlstmt;
END $$
DELIMITER ;
//执行存储过程
CALL testDB.GetRecordCount('article','');

　　

 

转载于:https://www.cnblogs.com/xsj1989/p/6482790.html