mysql 存储过程实现分页

重点：若参数是字符串，则需要在程序中拼接’‘或者"“；
字符串参数=String.format(”%s’", 字符串参数)

CREATE DEFINER=`root`@`%` PROCEDURE `A`( queryName varchar(64),entryStatus varchar(64),entryStartDate varchar(64),entryEndDate varchar(64),pageIndex INT(10),pageSize INT(10))
BEGIN
--  获取hr员工信息列表

-- pageIndex  当前页
-- pageSize   每页记录数

-- 总记录分页数
 declare pageCount   int(10); 
-- 总记录数
 declare totalRecordCount  int(10);
 
-- 用户名
SET @queryName = '';
-- 手机号
SET @queryPhone = '';

-- 入职状态
set @entryStatus='';


-- 入职日期范围查询
set @entryDate='';



 -- 名称和手机号作为一个搜素条件
                if queryName != '' and queryName is not null then
         SET @queryName = CONCAT("  and ( name  like ", queryName," or mobile  like ", queryName ,')' );
    END if;

                
                if entryStatus != '' and entryStatus is not null then
         SET @entryStatus = CONCAT(" and entry_status  = ", entryStatus );
    END if;
                
                
                if entryStartDate != '' and entryStartDate is not null and entryEndDate != '' and entryEndDate is not null then
         SET @entryDate = CONCAT(" and  DATE_FORMAT(entry_date,'%Y-%m-%d')  between ", entryStartDate,' and ', entryEndDate );
    END if;
                
                
                
                
                

    /*获取表中的记录数*/
    set @recordCount=0;
    set @sql='';
    set @sql=CONCAT('select count(*) into @recordCount from 表A where 1=1',  @queryName,@entryStatus,@entryDate);


   prepare stmt from @sql; /*预处理 自定义sql字符串*/
   execute stmt; /*执行自定义sql语句*/
   deallocate prepare stmt; /*释放预处理资源*/
   
   set totalRecordCount = @recordCount; /*总记录数*/
    
    /*计算返回多少页*/
   set @tmp=1;
    set @tmp=@recordCount mod pageSize; /*取余数*/
    if @tmp=0 then
     set pageCount=@recordCount div pageSize;
    else
     set pageCount=@recordCount div pageSize + 1;
   end if;
 

        
  /*分页显示数据*/
  set @sql=CONCAT("select * from 表A where 1=1",@queryName,@entryStatus,@entryDate," limit ",(pageIndex-1)*pageSize,",",pageSize);


   prepare stmt from @sql; /*预处理 自定义sql字符串*/
   execute stmt;/*执行自定义sql语句*/
   deallocate prepare stmt; /*释放预处理资源*/



-- 分页信息
   select totalRecordCount,pageCount;

                
END