mysql通用分页存储过程遇到的问题
DELIMITER $$ USE `tsb_asksys`$$ DROP PROCEDURE IF EXISTS `P_viewPage`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `P_viewPage`( $TableName VARCHAR(200),/*表名*/ $FieldList VARCHAR(2000), /*显示列名*/ $PrimaryKey VARCHAR(100), /*单一主键或唯一值键*/ $WhereStr VARCHAR(1000), /*查询条件 不含'where'字符*/ $OrderStr VARCHAR(1000), /*排序 不含'order by'字符，如id asc,userid desc，当@SortType=3时生效*/ $SortType INT, /*排序规则 1:正序asc 2:倒序desc 3:多列排序*/ $RecorderCount INT, /*记录总数 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 ($WhereStr IS NULL OR $WhereStr='') THEN SET @new_where1 = ' ' ; SET @new_where2 = ' WHERE ' ; ELSE SET @new_where1 =CONCAT(' WHERE ',$WhereStr); SET @new_where2 =CONCAT(' WHERE ',$WhereStr,' AND '); END IF; IF $OrderStr='' 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 ',$OrderStr); 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($OrderStr,',') > 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;*/ 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); END IF; END IF; PREPARE stmt2 FROM @Sql; EXECUTE stmt2; END IF; END$$ DELIMITER ;
以上存储过程是在网上找的，使用过程中遇到的问题是，当查询第二页及以后页内容时无法获得。
以下是解决办法，备注中说的非常清楚：
static public DataTable getAllUser(int PageSize, int CurrPageIndex, out int AllRecordCount, String _SearchWhere) { using (MySqlCommand cmd = new MySqlCommand("P_viewPage")) { cmd.CommandType = CommandType.StoredProcedure; //设置表名称： System.Text.StringBuilder TableName = new System.Text.StringBuilder(); TableName.Append(" GQShowV_UserInfoForAdmin t1 "); cmd.Parameters.AddWithValue("@$TableName", TableName.ToString());//对表名参数赋值 //设置字段名称： System.Text.StringBuilder myfld_str = new System.Text.StringBuilder(); myfld_str.Append(" t1.RecordID, t1.UserID, t1.LoginID, t1.Nickname,t1.SexID_Exp,t1.UserEmail_Exp,t1.CreateDate,t1.UserName,t1.Phone, t1.Tel,t1.IsAdmin_Exp "); cmd.Parameters.AddWithValue("@$FieldList", myfld_str.ToString());//对字段参数赋值 //设置按哪个字段进行排序 cmd.Parameters.AddWithValue("@$OrderStr", "t1.createDate DESC");// --排序 不含'order by'字符，如id asc,userid desc，当@SortType=3时生效 /*设置排序方式，规则说明：1是按照PrimaryKey正序asc；2按照PrimaryKey倒序desc； 3按照非关键字或多关键字排序，比如日期，排序字段后可跟asc或desc，如t1.createDate DESC或t1.createDate DESC,t1.other asc 另外请注意：排序方式只有1,2,3，其他数字不会从数据库中读出数据*/ cmd.Parameters.AddWithValue("@$SortType", 3);//--排序规则 1:正序asc 2:倒序desc 3:多列排序 //设置主表的关键字，切记：主键前不要加前缀，否则第二页及以后页内容无法读出 cmd.Parameters.AddWithValue("@$PrimaryKey", "UserID");//t1.UserID 第二页不会显示出来，因为有前缀 //设置总查询记录数：为0时，查询实际记录，主要解决表记录非常大，那么可以限制只查询10万条记录这样的问题 cmd.Parameters.AddWithValue("@$RecorderCount", 0); //设置查询条件 System.Text.StringBuilder query_where = new StringBuilder();//保存查询条件 query_where.Append(" (1 = 1) "); if (!String.IsNullOrEmpty(_SearchWhere)) { query_where.AppendFormat("and (t1.UserName LIKE N'%{0}%')", _SearchWhere); } cmd.Parameters.AddWithValue("@$WhereStr", query_where.ToString());//设置查询条件 //设置每页要显示的记录数 cmd.Parameters.AddWithValue("@$PageSize", PageSize); //设置要显示的页码： cmd.Parameters.AddWithValue("@$PageIndex", CurrPageIndex); //保存查询到的总记录数 MySqlParameter record_num = new MySqlParameter(); record_num = cmd.Parameters.Add(new MySqlParameter("@$TotalCount", SqlDbType.Int)); record_num.Direction = ParameterDirection.Output; // int _TotalPageCount = 0; MySqlParameter TotalPageCount = new MySqlParameter(); TotalPageCount = cmd.Parameters.Add(new MySqlParameter("@$TotalPageCount", SqlDbType.Int)); TotalPageCount.Direction = ParameterDirection.Output; using (DataTable dt = ObjectMindDBForMysql.MySqlHelper.GetData(cmd)) { AllRecordCount = int.Parse(cmd.Parameters["@$TotalCount"].Value.ToString()); _TotalPageCount = int.Parse(cmd.Parameters["@$TotalPageCount"].Value.ToString()); return dt; } } }
