基于Mysql的通用分页存储过程

最新推荐文章于 2023-06-28 22:20:30 发布

打豆豆的隔壁小王

最新推荐文章于 2023-06-28 22:20:30 发布

阅读量653

点赞数

分类专栏：数据库基础知识文章标签： mysql 存储 integer sql null

本文链接：https://blog.csdn.net/ahwr24/article/details/4909107

版权

数据库基础知识专栏收录该内容

32 篇文章 0 订阅

订阅专栏

CREATE PROCEDURE `P_viewPage`(
       $TableName VARCHAR(200),   --表名称
       $FieldList VARCHAR(2000),     --显示列名
       $PrimaryKey VARCHAR(100),   --主键
       $Where VARCHAR(1000),      --查询条件不含'where'字符
       $Order 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 ($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 ;