MySQL分页存储过程及其调用

存储过程

DELIMITER $$USE `sxdb`$$DROP PROCEDURE IF EXISTS `Query_Pagination_ViewPage`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `Query_Pagination_ViewPage`(         $TableName VARCHAR(200),           $FieldList VARCHAR(2000),         $PrimaryKey VARCHAR(100),         $WHERE VARCHAR(1000),             $ORDER VARCHAR(1000),             $SortType INT,                   $RecorderCount INT,              $PageSize INT,                   $PageIndex INT,          OUT    $TotalCount INTEGER,    OUT    $TotalPageCount INTEGER   )BEGIN  IF !(($TableName IS NULL OR $TableName='') OR ($FieldList IS NULL OR $FieldList='') OR ($PrimaryKey IS NULL OR $PrimaryKey='') OR $SortType < 1 OR $SortType >3 OR $RecorderCount < 0 OR $PageSize < 0 OR $PageIndex < 0) THEN  IF ($WHERE IS NULL OR $WHERE='') THEN    SET @new_where1 = ' ' ;    SET @new_where2 = ' WHERE ' ;  ELSE    SET @new_where1 =CONCAT(' WHERE ',$WHERE);    SET @new_where2 =CONCAT(' WHERE ',$WHERE,' AND ');  END IF;  IF $ORDER='' OR $SortType = 1 OR $SortType = 2 THEN    IF $SortType = 1 THEN         SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' ASC' );      END IF;    IF $SortType = 2 THEN         SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' DESC');      END IF;  ELSE    SET @new_order =CONCAT(' ORDER BY ',$ORDER);  END IF;  SET @SqlCount = CONCAT('SELECT COUNT(*) into @TotalCount FROM ',$TableName,@new_where1);  SET @SqlCount1 = CONCAT('SELECT CEILING((COUNT(*)+0.0)/',$PageSize,') into @TotalPageCount FROM ',$TableName,@new_where1);  IF $RecorderCount = 0 THEN       PREPARE stmt1 FROM @SqlCount;       EXECUTE stmt1;       SET $TotalCount=@TotalCount;       PREPARE stmt1 FROM @SqlCount1;       EXECUTE stmt1;       SET $TotalPageCount=@TotalPageCount;  ELSE    SET $TotalCount = $RecorderCount;  END IF;  IF $PageIndex > CEILING(($TotalCount+0.0)/$PageSize) THEN    SET $PageIndex = CEILING(($TotalCount+0.0)/$PageSize);  END IF;  IF $PageIndex = 0 OR $PageIndex = 1 THEN    SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize);  ELSE    IF $SortType = 1 THEN         SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' > (SELECT max(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize);    END IF;    IF $SortType = 2 THEN         SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' < (SELECT MIN(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize);      END IF;    IF $SortType = 3 THEN        IF INSTR($ORDER,',') > 0 THEN              SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' NOT IN (SELECT ',$PrimaryKey,' FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) a)',@new_order,' limit ',$PageSize);        ELSE              SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' ASC' );              SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' > (SELECT max(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize);        END IF;    END IF;  END IF;  PREPARE stmt2 FROM @SQL;  EXECUTE stmt2;  END IF;  END$$DELIMITER ;

调用

 CALL Query_Pagination_ViewPage('t_item','*','id','','id desc',2,0,15,1,@a,@b); SELECT @a,@b