搞了好久都没有搞好,那位大哥帮个忙帮我看一下:
DELIMITER $$DROP PROCEDURE IF EXISTS `huangxin`.`pagination_mysql` $$
CREATE PROCEDURE `huangxin`.`pagination_mysql` (
in currpage int,
in columns varchar(500),
in tablename varchar(500),
in sCondition varchar(500),
in order_field varchar(100),
in asc_field int,
in primary_field varchar(100),
in pagesize int
)
BEGINdeclare sTemp varchar(1000);
declare sSql varchar(4000);
declare sOrder varchar(1000); if asc_field = 1 then
set sOrder = concat(' order by ',order_field,' desc');
set sTemp = '<(select min';
else
set sOrder = concat(' order by ',order_field,' asc');
set sTemp = '>)select max';
end if; if currpage = 0 then
if sCondition <> ' ' then
set sSql = concat('select ',columns,' from ',tablename,' where ');
set sSql = concat(sSql,sCondition,sOrder);
else
set sSql = concat('select ',columns,' from ',tablename,sOrder);
end if; else
if sCondition <> '' then
set sSql = concat('select ',columns,' from ',tablename);
set sSql = concat(sSql,' where ',sCondition,' and ',primary_field,sTemp);
set sSql = concat(sSql,'(private_key)',' from (select ');
set sSql = concat(sSql,primary_field,' as private_key from ', tablename,' where ',sCondition, sOrder);
set sSql = concat(sSql,' limit ',(currpage-1)*pagesize,',', pagesize, ') as tabtemp)', sOrder);
/*set sSql = concat(sSql, ' limit ?');*/
else
set sSql = concat('select ',columns,' from ',tablename);
set sSql = concat(sSql,' where ', primary_field, sTemp);
set sSql = concat(sSql,'(private_key)',' from (select ');
set sSql = concat(sSql,primary_field,' as private_key from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ',(currpage-1)*pagesize,',', pagesize, ') as tabtemp)', sOrder);
/*set sSql = concat(sSql, ' limit ?');*/
end if; end if; set @iPageSize = pagesize;
set @sQuery = sSql;
prepare stmt from @sQuery;
execute stmt using @iPageSize;
END $$DELIMITER ;
运行的代码是这样:call pagination_mysql(1, '*', 'book_search', '', 'book_id', 1, 'book_id', 8);
出现的结果是:Incorrect arguments to EXECUTE
那位大哥帮个忙.小弟被BS啊。
DELIMITER $$DROP PROCEDURE IF EXISTS `huangxin`.`pagination_mysql` $$
CREATE PROCEDURE `huangxin`.`pagination_mysql` (
in currpage int,
in columns varchar(500),
in tablename varchar(500),
in sCondition varchar(500),
in order_field varchar(100),
in asc_field int,
in primary_field varchar(100),
in pagesize int
)
BEGINdeclare sTemp varchar(1000);
declare sSql varchar(4000);
declare sOrder varchar(1000); if asc_field = 1 then
set sOrder = concat(' order by ',order_field,' desc');
set sTemp = '<(select min';
else
set sOrder = concat(' order by ',order_field,' asc');
set sTemp = '>)select max';
end if; if currpage = 0 then
if sCondition <> ' ' then
set sSql = concat('select ',columns,' from ',tablename,' where ');
set sSql = concat(sSql,sCondition,sOrder);
else
set sSql = concat('select ',columns,' from ',tablename,sOrder);
end if; else
if sCondition <> '' then
set sSql = concat('select ',columns,' from ',tablename);
set sSql = concat(sSql,' where ',sCondition,' and ',primary_field,sTemp);
set sSql = concat(sSql,'(private_key)',' from (select ');
set sSql = concat(sSql,primary_field,' as private_key from ', tablename,' where ',sCondition, sOrder);
set sSql = concat(sSql,' limit ',(currpage-1)*pagesize,',', pagesize, ') as tabtemp)', sOrder);
/*set sSql = concat(sSql, ' limit ?');*/
else
set sSql = concat('select ',columns,' from ',tablename);
set sSql = concat(sSql,' where ', primary_field, sTemp);
set sSql = concat(sSql,'(private_key)',' from (select ');
set sSql = concat(sSql,primary_field,' as private_key from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ',(currpage-1)*pagesize,',', pagesize, ') as tabtemp)', sOrder);
/*set sSql = concat(sSql, ' limit ?');*/
end if; end if; set @iPageSize = pagesize;
set @sQuery = sSql;
prepare stmt from @sQuery;
execute stmt using @iPageSize;
END $$DELIMITER ;
运行的代码是这样:call pagination_mysql(1, '*', 'book_search', '', 'book_id', 1, 'book_id', 8);
出现的结果是:Incorrect arguments to EXECUTE
那位大哥帮个忙.小弟被BS啊。
一切还得靠自己。
set @sQuery = sSql;
去手册上看看
limit ?
和 limit 具体的参数
的区别。