mysql存储过程子查询_MySQL 分页存储过程(子查询)

基于子查询的分页存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_page`( in _table varchar(256),  /*表明*/

in _index varchar(256), /*索引*/

in _fields varchar(512), /*字段*/

in _order varchar(256), /*排序*/

in _where varchar(1024), /*搜索条件*/

in _ofsset int, /*偏移记录*/

in _pagesize int, /*每页的尺寸*/

out _total int /*返回记录总数*/

)

BEGIN

set @sqlstr = concat('select ',_fields, ' from ', _table); /*执行的SQL语句*/

set @chdsqlstr = concat('select ',_index,' from ',_table); /*子查询的SQL语句*/

set @countsqlstr = concat('select count(1) into @total from ',_table); /*返回记录总数SQL语句*/

if(!isnull(_where) && _where <> '') then

set @chdsqlstr = concat(@chdsqlstr, ' where ',_where);

set @countsqlstr = concat(@countsqlstr, ' where ',_where);

end if;

/*返回记录条数*/

prepare stmt from @countsqlstr;

execute stmt;

set _total = @total;

set @countsqlstr = null;

if(!isnull(_order) && _order <> '') then

set @chdsqlstr = concat(@chdsqlstr, ' ', _order);

end if;

set @chdsqlstr = concat(@chdsqlstr,' limit ', _ofsset ,',1');

set @sqlstr = concat(@sqlstr,' where ',_index,' >= (',@chdsqlstr, ')');

set @sqlstr = concat(@sqlstr,' limit ', _pagesize, ';');

prepare stmt from @sqlstr;

execute stmt;

set @sqlstr = null;

set @chdsqlstr = null;

END

调用

call sp_page('actionlist','A_ID','*','order by A_ID','',10,10,@a);

select convert(@a, char(10));

存在缺陷,关键字不能是多个关键字。

没有使用sql_calc_found_rows和found_rows()的原因,当偏移超过0的时候found_rows返回的记录数是返回的结果集记录数不是总的记录数。因此使用count来获得。

/*返回记录条数*/

prepare stmt from @countsqlstr;

execute stmt;

set _total = @total;

set @countsqlstr = null;

请大家指点！