Mysql 分页存储过程代码

最新推荐文章于 2023-11-09 23:13:59 发布

deng11342

最新推荐文章于 2023-11-09 23:13:59 发布

阅读量623

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库 mysql

本文链接：https://blog.youkuaiyun.com/deng11342/article/details/45741161

数据库同时被 2 个专栏收录

9 篇文章

订阅专栏

mysql

1 篇文章

订阅专栏

本文介绍了一个通用的MySQL存储过程分页查询方法，包括查询信息列表及查询总行数的功能，并详细解释了如何根据传入的参数进行灵活的分页和排序操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近项目是用mysql存储过程，琢磨了下通用的mysql 存储过程分页写法，觉得通用度很高，随笔记下分享。

分页信息列表查询

PROCEDURE xx.p_common_findByPage(IN tableName varchar(2000), IN fieldsNames varchar(500), IN pageIndex int, IN pageSize int, IN sortName varchar(200), IN strWhere varchar(500))
BEGIN

  --  通用分页查询存储过程，如果不传入分页参数，则可以查询全部信息

  DECLARE fieldlist varchar(200);
  IF fieldsNames = '' || fieldsNames IS NULL THEN
    SET fieldlist = '*';
  ELSE
    SET fieldlist = fieldsNames;
  END IF;


  IF strWhere = '' || strWhere IS NULL THEN
    IF sortName = '' || sortName IS NULL THEN
      IF pageIndex = '' ||  pageIndex IS NULL THEN 
        SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName);
      ELSE 
        SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName, ' LIMIT ', (pageIndex - 1) * pageSize, ',', pageSize);
      END IF ;
    ELSE
      IF  pageIndex = '' ||  pageIndex IS NULL THEN 
        SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName, ' ORDER BY ', sortName);
      ELSE 
        SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName, ' ORDER BY ', sortName, ' LIMIT ', (pageIndex - 1) * pageSize, ',', pageSize);
      END IF ;
      
    END IF;
  ELSE
    IF sortName = '' || sortName IS NULL THEN
      IF  pageIndex = '' ||  pageIndex IS NULL THEN 
        SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName, ' WHERE ', strWhere);
      ELSE 
        SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName, ' WHERE ', strWhere, ' LIMIT ', (pageIndex - 1) * pageSize, ',', pageSize);
      END IF ;
      
    ELSE
      IF  pageIndex = '' ||  pageIndex IS NULL THEN 
        SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName, ' WHERE ', strWhere, ' ORDER BY ', sortName);
      ELSE 
        SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName, ' WHERE ', strWhere, ' ORDER BY ', sortName, ' LIMIT ', (pageIndex - 1) * pageSize, ',', pageSize);
      END IF ;
    END IF;
  END IF;


  PREPARE stmt1 FROM @strSQL;
  EXECUTE stmt1;
  DEALLOCATE PREPARE stmt1;

END

分页信息查询总行数

PROCEDURE xx.p_common_findByPageCnt(IN tableName varchar(2000), IN sortName varchar(200), IN strWhere varchar(500))
BEGIN

  --  通用分页查询存储过程，查询分页信息中行数

  IF strWhere = '' || strWhere = NULL THEN
    IF sortName = '' || sortName = NULL THEN
      SET @strSQL = CONCAT('SELECT count(*) cnt FROM ', tableName);
    ELSE
      SET @strSQL = CONCAT('SELECT count(*) cnt FROM ', tableName, ' ORDER BY ', sortName);
    END IF;
  ELSE
    IF sortName = '' || sortName = NULL THEN
      SET @strSQL = CONCAT('SELECT count(*) cnt FROM ', tableName, ' WHERE ', strWhere);
    ELSE
      SET @strSQL = CONCAT('SELECT count(*) cnt FROM ', tableName, ' WHERE ', strWhere, ' ORDER BY ', sortName);
    END IF;
  END IF;


  PREPARE stmt1 FROM @strSQL;
  EXECUTE stmt1;
  DEALLOCATE PREPARE stmt1;

END