mysql 分页存储过程

drop procedure if exists P_viewPage; $$; delimiter $$; CREATE PROCEDURE P_viewPage( $TableName VARCHAR(200), //表名 $FieldList VARCHAR(2000), //字段集 可以是* $PrimaryKey VARCHAR(100), //主键 $Where VARCHAR(1000), //where条件,例如id > 100 $Order VARCHAR(1000), //排序条件 例如:id $SortType INT, //排序条件 1表示升序,2表示降序 $RecorderCount INT, //总数据行数，如果传0，表示重新查询数据行数，如果不为0，传入的数据就表示总数据行数 $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 P_viewPage (‘users‘, ‘*‘, ‘u_id‘, ‘u_id > 100‘, ‘u_id‘, 1, 0, 20, 1, @totalcount, @totalpagecount); select @totalcount, @totalpagecount;
--------------------------------------------------------------------------------------
来源于：http://wenlujun.blog.51cto.com/336737/71819/

 

mysql 分页存储过程

标签：