mysql通用分页存储过程遇到的问题

王朝学院·作者佚名 2016-08-27

DELIMITER $$USE`tsb_asksys`$$DROPPROCEDUREIFEXISTS`P_viewPage`$$CREATEDEFINER=`root`@`localhost`PROCEDURE`P_viewPage`(

$TableNameVARCHAR(200),/*表名*/$FieldListVARCHAR(2000),/*显示列名*/$PrimaryKeyVARCHAR(100),/*单一主键或唯一值键*/$WhereStrVARCHAR(1000),/*查询条件不含'where'字符*/$OrderStrVARCHAR(1000),/*排序不含'order by'字符，如id asc,userid desc，当@SortType=3时生效*/$SortTypeINT,/*排序规则 1:正序asc 2:倒序desc 3:多列排序*/$RecorderCountINT,/*记录总数 0:会返回总记录*/$PageSizeINT,/*每页输出的记录数*/$PageIndexINT,/*当前页数*/OUT $TotalCountINTEGER,/*返回记录总数*/OUT $TotalPageCountINTEGER/*返回总页数*/)BEGINIF!(($TableNameISNULLOR$TableName='')OR($FieldListISNULLOR$FieldList='')OR($PrimaryKeyISNULLOR$PrimaryKey='')OR$SortType<1OR$SortType>3OR$RecorderCount<0OR$PageSize<0OR$PageIndex<0)THENIF($WhereStrISNULLOR$WhereStr='')THENSET@new_where1='';SET@new_where2='WHERE';ELSESET@new_where1=CONCAT('WHERE',$WhereStr);SET@new_where2=CONCAT('WHERE',$WhereStr,'AND');ENDIF;IF$OrderStr=''OR$SortType=1OR$SortType=2THENIF$SortType=1THENSET@new_order=CONCAT('ORDER BY',$PrimaryKey,'ASC');ENDIF;IF$SortType=2THENSET@new_order=CONCAT('ORDER BY',$PrimaryKey,'DESC');ENDIF;ELSESET@new_order=CONCAT('ORDER BY',$OrderStr);ENDIF;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=0THENPREPAREstmt1FROM@SqlCount;EXECUTEstmt1;SET$TotalCount=@TotalCount;PREPAREstmt1FROM@SqlCount1;EXECUTEstmt1;SET$TotalPageCount=@TotalPageCount;ELSESET$TotalCount=$RecorderCount;ENDIF;IF$PageIndex>CEILING(($TotalCount+0.0)/$PageSize)THENSET$PageIndex=CEILING(($TotalCount+0.0)/$PageSize);ENDIF;IF$PageIndex=0OR$PageIndex=1THENSET@Sql=CONCAT('SELECT',$FieldList,'FROM',$TableName,@new_where1,@new_order,'limit',$PageSize);ELSEIF$SortType=1THENSET@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);ENDIF;IF$SortType=2THENSET@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);ENDIF;IF$SortType=3THEN/*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);ENDIF;ENDIF;PREPAREstmt2FROM@Sql;EXECUTEstmt2;ENDIF;END$$

DELIMITER ;

以上存储过程是在网上找的，使用过程中遇到的问题是，当查询第二页及以后页内容时无法获得。

以下是解决办法，备注中说的非常清楚：

staticpublicDataTable getAllUser(intPageSize,intCurrPageIndex,outintAllRecordCount, String _SearchWhere)

{using(MySQLCommand cmd =newMySqlCommand("P_viewPage"))

{

cmd.CommandType=CommandType.StoredProcedure;//设置表名称：System.Text.StringBuilder TableName =newSystem.Text.StringBuilder();

TableName.Append("GQShowV_UserInfoForAdmin t1");

cmd.Parameters.AddWithValue("@$TableName", TableName.ToString());//对表名参数赋值//设置字段名称：System.Text.StringBuilder myfld_str =newSystem.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 =newStringBuilder();//保存查询条件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=newMySqlParameter();

record_num= cmd.Parameters.Add(newMySqlParameter("@$TotalCount", SqlDbType.Int));

record_num.Direction=ParameterDirection.Output;//int_TotalPageCount =0;

MySqlParameter TotalPageCount=newMySqlParameter();

TotalPageCount= cmd.Parameters.Add(newMySqlParameter("@$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());returndt;

}

以上内容转自：http://www.taoshibao.com/q/5265816609616735182