mysql之分页存储过程实例

最新推荐文章于 2024-10-22 12:41:03 发布

weixin_34007886

最新推荐文章于 2024-10-22 12:41:03 发布

阅读量231

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/zengnansheng/p/10389354.html

版权

本文详细介绍了在SQL中实现分页查询的方法，包括使用LIMIT和OFFSET关键字，以及通过预处理语句处理动态SQL，确保了查询的安全性和灵活性。同时，文章提供了多个示例，展示了如何在不同场景下进行分页查询，如根据特定条件过滤和排序。

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

DROP PROCEDURE IF EXISTS sp_page;

CREATE PROCEDURE sp_page1(    

    PageIndex INT,  #页码  

    PageSize INT,   #每页记录数    

    OUT TotalCount INT  #总记录数    

) 

BEGIN

set @strSqls='select sql_calc_found_rows * from users';

set @strSqls=CONCAT(@strSqls,' limit ',(PageIndex-1)*PageSize,',',PageSize);

prepare strsql from @strSqls;

execute strsql;

deallocate prepare strsql;



#计算总记录数

set TotalCount = found_rows(); 



END



调用：

call sp_page(1,10,@a,@b);

select @a;

DROP PROCEDURE IF EXISTS aaa;

CREATE PROCEDURE aaa(    

    PageIndex INT,  #页码  

    PageSize INT,   #每页记录数    

    OUT TotalCount INT  #总记录数   

) 

BEGIN



DECLARE StartRow int;

set StartRow=(PageIndex-1)*PageSize;



SELECT  sql_calc_found_rows * FROM 表名





limit StartRow,PageSize;





#计算总记录数

set TotalCount = found_rows(); 



END

------------------------------------------------------------

DROP PROCEDURE IF EXISTS sp_page;

CREATE PROCEDURE sp_page(

  IN UserID INT,-- 用户id

IN PageIndex INT,-- 页码 从1开始

IN PageSize INT,-- 页大小

IN Conditions VARCHAR(2000),-- 过滤

IN Orderbys VARCHAR(200),-- 排序

OUT TotalCount INT-- 总记录数

)

BEGIN



#处理where

SET @strWhere=' where 1=1 ';

IF (UserID>0) THEN

SET @strWhere = CONCAT(@strWhere, ' AND  id =',UserID);

END IF;

#处理where end



#处理condition

SET @strCondition = '';

IF (IFNULL(Conditions,'') <> '') THEN

 SET @strCondition = CONCAT(' where 1=1 ',  Conditions);

END IF;

#处理condition end



#处理排序

SET @default_order='id asc'; -- 默认排序

SET @orderBy = IF(IFNULL(Orderbys,'')='', @default_order,Orderbys);

#处理排序 end



SET @strSql = CONCAT('SELECT * from user ',@strWhere);



#计算总记录数

SET @exe_sql = CONCAT('SET @totalcount = (SELECT COUNT(1) FROM (', @strSql, ')  tb',  @StrCondition,');');

PREPARE stmt FROM @exe_sql;

EXECUTE stmt;

SET TotalCount = @totalcount;

#计算总记录数 end





SET @exe_sql = @strSql;

IF (IFNULL(@strCondition,'') > '') THEN

  SET @exe_sql = CONCAT(' SELECT * FROM ( ',@strSql,' )  tb', @strCondition,' ORDER BY ',@orderBy, ' LIMIT ', (PageIndex-1)*PageSize, ',', PageSize,' ');

ELSE

   SET @exe_sql = CONCAT( @strSql,' ORDER BY ',@orderBy, ' LIMIT ', (PageIndex-1)*PageSize, ',', PageSize,' ');

END IF;



 PREPARE stmt FROM @exe_sql;

 #select @exe_sql;

 EXECUTE stmt;



END



调用

call sp_page3(1,1,10,'and id=1','username desc',@totalcount,@pagecount);

SELECT @totalcount;







DROP PROCEDURE IF EXISTS sp_page;

CREATE PROCEDURE sp_page(

IN CategoryID INT,

IN PageIndex INT,-- 页码 从1开始

IN PageSize INT,-- 页大小

IN Orderbys VARCHAR(200),-- 排序

OUT TotalCount INT-- 总记录数

)

BEGIN



SET @strWhere=' where 1=1 ';

#处理where

IF (CategoryID>0) THEN

SET @strWhere = CONCAT(@strWhere, ' AND  CategoryID =',CategoryID);

END IF;

#处理where end



SET @strSql = CONCAT('SELECT * from Article ',@strWhere);



#处理排序

SET @default_order='ArticleID asc'; -- 默认排序

SET @orderBy = IF(IFNULL(Orderbys,'')='', @default_order,Orderbys);

#处理排序 end





#计算总记录数

#SET @exe_sql = CONCAT('SET @totalcount = (SELECT COUNT(1) FROM (', @strSql, ')  tb;');

SET @exe_sql = CONCAT('SET @totalcount = (SELECT COUNT(1) FROM (', @strSql, ')  tb',');');

PREPARE stmt FROM @exe_sql;

EXECUTE stmt;

SET TotalCount = @totalcount;

#计算总记录数 end





SET @exe_sql = CONCAT( @strSql,' ORDER BY ',@orderBy, ' LIMIT ', (PageIndex-1)*PageSize, ',', PageSize,' ');





 PREPARE stmt FROM @exe_sql;

 #select @exe_sql;

 EXECUTE stmt;



END



call sp_page(1,1,10,'',@totalcount);

SELECT @totalcount;