mysql 从相同类型的多张表中提取到一张表中

末蓝、 2024-03-30 13:32 81阅读 0赞

有时候需要从多张相同类型的表中提取数据，这些表有一些相同的列或者表结构完全相同，同时表名存在一定的规律，如果表数量少还好，如果表数量多的话则会比较繁琐。可以通过存储过程将多张表的数据提取到一张表的方法来降低工作量。

先创建测试表并生成测试数据。  
以下存储过程创建10张测试表，每张表生成10条测试数据。

drop PROCEDURE if EXISTS create10tables;  
create PROCEDURE create10tables()  
BEGIN  
DECLARE t\_name VARCHAR(32);  
DECLARE i INT;  
DECLARE j INT;  
DECLARE continue HANDLER for not found set t\_name = "";  
set i = 0;  
set j = 0;  
create\_loop:LOOP  
set i = i + 1;  
set t\_name = CONCAT("t\_test\_",i);  
set @dropsql = CONCAT('drop table if EXISTS ',t\_name);  
\#select @dropsql;  
prepare dropsql from @dropsql;  
EXECUTE dropsql;  
DEALLOCATE prepare dropsql;  
set @createsql = concat('create table ',t\_name,' (id int(11) not null auto\_increment,modifytime timestamp null default current\_timestamp,vdata varchar(32) ,primary key(id));');  
\#select @createsql;  
prepare createsql from @createsql;  
EXECUTE createsql;  
DEALLOCATE prepare createsql;  
insert\_loop:LOOP  
set j = j+1;  
if j > 10 THEN  
LEAVE insert\_loop;  
end if;  
set @insertsql = concat('insert into ',t\_name,'(vdata) values(md5(rand()));');  
\#select @insertsql;  
PREPARE insertsql from @insertsql;  
EXECUTE insertsql;  
deallocate PREPARE insertsql;  
end LOOP insert\_loop;  
set j = 0;  
if i > 10 THEN  
LEAVE create\_loop;  
end if;  
end LOOP create\_loop;  
END;  
\#执行create10tables 生成表与数据  
call create10tables();

有了表和数据，可以在information\_schema数据库的innodb\_tables表中看到新生成的对应的表名。这里可以通过该表将所有的表名查出来，然后进行数据提取。SQL如下：

drop PROCEDURE if exists selectalldata;  
drop table if exists t\_test;  
create PROCEDURE selectalldata()  
BEGIN  
DECLARE done int DEFAULT FALSE;  
DECLARE t\_name VARCHAR(32);  
declare i int;  
DECLARE cur1 CURSOR for SELECT table\_name from information\_schema.TABLES where table\_name like "t\_test\_%";

\# SELECT table\_name from information\_schema.TABLES where TABLE\_SCHEMA='EMP' AND table\_name like "big\_table\_%";  
DECLARE continue HANDLER for not found set done = TRUE;  
open cur1;  
set i = 0;  
read\_loop:LOOP  
fetch cur1 into t\_name;  
if done THEN  
LEAVE read\_loop;  
end if;  
if i = 0 THEN

注:单引号''和""双引号区别  
set @createsql = concat('create table t\_test (id int(11) not null auto\_increment,modifytime timestamp null default current\_timestamp,vdata varchar(32) ,primary key(id));');  
\#select @createsql;  
prepare createsql from @createsql;  
EXECUTE createsql;  
DEALLOCATE prepare createsql;  
set i = i + 1;  
end if;  
set @insertsql = concat('insert into t\_test(modifytime,vdata) select modifytime,vdata from ',t\_name);  
prepare insertsql from @insertsql;  
EXECUTE insertsql;  
DEALLOCATE prepare insertsql;  
end LOOP;  
close cur1;  
END;  
\#执行selectdata  
call selectalldata();