【MySql-多表联合查询分页存储过程】

最新推荐文章于 2023-01-11 01:34:12 发布

小何同学_

最新推荐文章于 2023-01-11 01:34:12 发布

阅读量1.6k

点赞数 1

分类专栏： [08] MySql 文章标签： mysql 存储

本文链接：https://blog.csdn.net/heyangyi_19940703/article/details/51275410

版权

[08] MySql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

分享一个好用的MySql多表联合按条件查询查询分页排序存储过程：

-- ----------------------------
-- Procedure structure for `pPage`
-- ----------------------------
DROP PROCEDURE IF EXISTS `pPage`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pPage`(in _pagecurrent int, /*当前页*/
 in _pagesize int,        /*每页的记录数*/
 in _ifelse varchar(1000),/*显示字段*/
 in _where varchar(1000), /*条件*/
 in _order varchar(1000))
    COMMENT '分页存储过程\r\n调用例1  call sp_Page(1,3,''*'',''test'',''order by id desc'');\r\n'
BEGIN
 if _pagesize<=1 then
  set _pagesize=20;
 end if;
 if _pagecurrent < 1 then
  set _pagecurrent = 1;
 end if;
 
 set @strsql = concat('select ',_ifelse,' from ',_where,' ',_order,' limit ',_pagecurrent*_pagesize-_pagesize,',',_pagesize);
 prepare stmtsql from @strsql;
 execute stmtsql;
 deallocate prepare stmtsql; 

 set @strsqlcount=concat('select count(0) AS count from ',_where);/*count(1) 这个字段最好是主键*/
 prepare stmtsqlcount from @strsqlcount;
 execute stmtsqlcount;
 deallocate prepare stmtsqlcount;

END
;;
DELIMITER ;