#-------- 1. 创建测试表及测试数据 BEGIN ----------
drop table if exists `testTable`;
create table `testTable`(
id varchar(50)
);
INSERT into `testTable` VALUES(UUID());
INSERT into `testTable` VALUES(UUID());
INSERT into `testTable` VALUES(UUID());
INSERT into `testTable` VALUES(UUID());
INSERT into `testTable` VALUES(UUID());
INSERT into `testTable` VALUES(UUID());
INSERT into `testTable` VALUES(UUID());
INSERT into `testTable` VALUES(UUID());
INSERT into `testTable` VALUES(UUID());
INSERT into `testTable` VALUES(UUID());
#select * from `testTable`;
#-------- 2. 创建存储过程 ----------
DELIMITER $$
drop PROCEDURE if exists `Proc_GenerateTableByPager`;
$$
create PROCEDURE `Proc_GenerateTableByPager`()
begin
declare v_pageIndex ,v_pageSize ,v_recordRows,v_sql varchar(4000);
CREATE table if not exists `pager_assistant`(
rowNum int NOT NULL AUTO_INCREMENT
,id varchar(50) not null
,PRIMARY KEY (`rowNum`)
);
truncate table `pager_assistant`;
INSERT `pager_assistant`(id)
select `id` from `testTable`;
set v_pageIndex=1;
set v_pageSize=3;
select @v_recordRows:=max(rowNum) from `pager_assistant`;
while (v_pageIndex-1)*v_pageSize<=@v_recordRows do
#注:MySQL 动态SQL 不支持多语句
set @v_sql:=CONCAT('drop table if exists testTable', v_pageIndex ,';');
prepare stmt from @v_sql;
EXECUTE stmt;
set @v_sql:=concat('create table testTable', v_pageIndex, '(id varchar(50));');
prepare stmt from @v_sql;
EXECUTE stmt;
set @v_sql:=concat(' insert into testTable', v_pageIndex, '(id)'
,' select id from testTable where id in ( '
,' select id from pager_assistant where rowNum>'
,(v_pageIndex-1)*v_pageSize
,' and rowNum<='
,(v_pageIndex * v_pageSize)
,' );'
);
prepare stmt from @v_sql;
EXECUTE stmt;
set v_pageIndex=v_pageIndex+1;
end while;
end
$$
call `Proc_GenerateTableByPager`();
原贴:点击打开链接