mysql分页存储_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','');