使用方法
call
USP_P_SPLITPAGE 'select * from tt',1,10;------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS `USP_P_SPLITPAGE` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `USP_P_SPLITPAGE`(`sql` varchar(1024),pagesize int,CURRENTPAGE int)
BEGIN
SET @skip= (CURRENTPAGE - 1 )* pagesize ;
SET @numrows=pagesize;
set @sql = concat('select sql_calc_found_rows',substr(ltrim(`sql`),7),' limit ?,?');
PREPARE stmt FROM @sql;
EXECUTE stmt USING @skip, @numrows;
set @rows= found_rows();
set @pages = round(@rows / pagesize +0.5) ;
set @pages=if(CURRENTPAGE<=0,1,if(CURRENTPAGE>@pages and @pages>=1,@pages,CURRENTPAGE));
select @rows as recordcount,@pages as CURRENTPAGE;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;