DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`sum`$$CREATE PROCEDURE `test`.`sum`() BEGIN DECLARE cnt int; declare cnt2 int; declare cnt3 int; declare i int; select count(*) from tbl into cnt; select ceil(cnt/100) into cnt2; set i=0; loop1:loop set i = i + 1; select 100*i into cnt3; if i > cnt2 then leave loop1; end if; select * from tb1 limit cnt3,1; end loop loop1; END$$DELIMITER ;
2楼的方法只适合于ID为INT型的表。
上面的存储过程报错: 贴出正确的: DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`sp_sum`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_gehang`() BEGIN DECLARE cnt int; declare cnt2 int; -- declare cnt3 int; declare i int; select count(*) from song into cnt; select ceil(cnt/100) into cnt2; set i=0; loop1:loop set i = i + 1; -- select 2*i into cnt3; if i > cnt2 then leave loop1; end if; set @smt = 'select * from song limit ?,1;'; prepare s1 from @smt; -- set @cnt3 = 2*i; set @cnt3 = 100*i; execute s1 using @cnt3; deallocate prepare s1 ; end loop loop1; END$$DELIMITER ;
BEGIN
DECLARE cnt int;
declare cnt2 int;
declare cnt3 int;
declare i int;
select count(*) from tbl into cnt;
select ceil(cnt/100) into cnt2;
set i=0;
loop1:loop
set i = i + 1;
select 100*i into cnt3;
if i > cnt2 then
leave loop1;
end if;
select * from tb1 limit cnt3,1;
end loop loop1;
END$$DELIMITER ;
贴出正确的:
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`sp_sum`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_gehang`()
BEGIN
DECLARE cnt int;
declare cnt2 int;
-- declare cnt3 int;
declare i int;
select count(*) from song into cnt;
select ceil(cnt/100) into cnt2;
set i=0;
loop1:loop
set i = i + 1;
-- select 2*i into cnt3;
if i > cnt2 then
leave loop1;
end if;
set @smt = 'select * from song limit ?,1;';
prepare s1 from @smt;
-- set @cnt3 = 2*i;
set @cnt3 = 100*i;
execute s1 using @cnt3;
deallocate prepare s1 ;
end loop loop1;
END$$DELIMITER ;