一个使用limit的存储过程.
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_test`$$
CREATE PROCEDURE `test`.`sp_test`(IN table_name varchar(255),IN total int)
BEGIN
set @stmt = concat('select * from ',table_name,' limit ?');
prepare s1 from @stmt;
set @s = total;
execute s1 using @s;
deallocate prepare s1;
END$$
DELIMITER ;
调用:
call sp_test('表名',数量); //取出前几个
$$
DROP PROCEDURE IF EXISTS `test`.`sp_test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test`(IN table_name varchar(255),
IN page_begin int,IN page_end int)
BEGIN
set @stmt = concat('select * from ',table_name,' limit ?,?');
prepare s1 from @stmt;
set @s1 = page_begin;
set @s2 = page_end;
execute s1 using @s1,@s2;
deallocate prepare s1;
END$$
DELIMITER ;
select * from cs_test;
call sp_test('cs_test',2,3); //取出从第2个后面开始(第三个数据开始)的3个数据