mysql 分页存储过程转自于wenlujun 的BLOG(备注如果出现编码格式错误一定要加charset设置编码格式)

CREATE   PROCEDURE  `P_viewPage`(  
       $TableName  VARCHAR (200),  
       $FieldList  VARCHAR (2000),  
       $PrimaryKey  VARCHAR (100),  
       $ Where   VARCHAR (1000) charset 'gbk',  
       $ 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  ;