MySQL is extracted from multiple tables of the same type into a single table

Snail carrying a heavy shell, sticking to the ground step by step difficult to crawl forward, do not turn back, nor look around, just toward the place where they want to go.

Sometimes you need to extract data from multiple tables of the same type, some of which have the same columns or table structure, and the table name has a certain regularity, if the number of tables is not good enough, if the number of tables is more cumbersome. You can reduce the effort by using stored procedures to extract data from multiple tables into a single table.

Create test tables and generate test data first.
The following stored procedure creates 10 test tables and produces 10 test data per table.
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 ("tTest", 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 (one) NOT NULL auto_increment,modifytime timestamp null default cur Rent_timestamp,vdata varchar (+), primary key (ID));
#select @createsql;
Prepare createsql from @createsql;
EXECUTE Createsql;
deallocate prepare createsql;
Insert_loop:loop
Set j = j+1;
If J > Ten 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 > Ten then
LEAVE Create_loop;
End If;
End LOOP Create_loop;
END;
#执行create10tables Generating tables and data
Call Create10tables ();

With tables and data, you can see the newly generated corresponding table names in the Innodb_tables table in the INFORMATION_SCHEMA database. This table allows you to isolate all the table names and then extract the data. SQL is as follows:
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.TABLESWhere table_name like "tTest%";
DECLARE continue HANDLER for don't found set done = TRUE;
Open cur1;
Set i = 0;
Read_loop:loop
Fetch CUR1 into t_name;
If do then
LEAVE Read_loop;
End If;
If i = 0 Then
Set @createsql = concat (' CREATE TABLE t_test (ID int (one) NOT NULL auto_increment,modifytime timestamp null default current _timestamp,vdata varchar (+), 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 ();
After execution, you can see in the T_test table that all the data has been extracted.

In the actual operation, modify the corresponding SQL statement as needed.

MySQL is extracted from multiple tables of the same type into a single table