海量数据分页示例

根据条件分页显示海量数据

调用：call test(1,10,10179,'0701')

执行时间: 0.188ms

CREATE PROCEDURE `test`(
 in _pagecurrent int,
 in _pagesize int,
 in INPHONEMODNO varchar(10),
 in INSecondTypeID varchar(10)
)
BEGIN
declare _order varchar(500);
declare _where varchar(1000);
set _order=' order by PRODCODE desc';
set _where=concat('CONVERT(SecondTypeID using utf8)=',"'",CONVERT(INSecondTypeID using utf8),"'",' and  MATCH (PHONEMODNO) AGAINST ',CONCAT("('",CONVERT(INPHONEMODNO using utf8),"')"));
 if _pagesize<=1 then
  set _pagesize=20;
 end if;
 if _pagecurrent < 1 then
  set _pagecurrent = 1;
 end if;
 set @strsql = concat('select PRODCODE,PRODNAME,PRODENAME,PRODSPELL,SUPCODE,ProdPrice,0 as Integral,ProdImg,ProdMovie,FileSize,FilePath,ZY  from xa_dg_prodinfo where ' ,_where,_order,' limit ',_pagecurrent*_pagesize-_pagesize,',',_pagesize);
 prepare stmtsql from @strsql;
 execute stmtsql;
 deallocate prepare stmtsql;
 set @strsqlcount=concat('select count(*) as count from xa_dg_prodinfo where ',_where);
 prepare stmtsqlcount from @strsqlcount;
 execute stmtsqlcount;
 deallocate prepare stmtsqlcount;
END

返回两个结果集，一条为查询数据集，另一条为总条数.