Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief description of how mysql operates multiple tables of the same type to extract to a table. You can look up relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope that this article on how to operate multiple tables of the same type in mysql can bring you some practical help.

Sometimes it is necessary to extract data from multiple tables of the same type, which have the same columns or the same table structure, and the table name has certain rules. If the number of tables is small, it will be more cumbersome if the number of tables is large. You can reduce the workload by using stored procedures to extract data from multiple tables into one table.

First create the test table and generate the test data.

The following stored procedure creates 10 test tables, each generating 10 pieces of test data.

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', 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 = juni1

If j > 10 THEN

LEAVE insert_loop

End if

Set @ insertsql = concat ('insert into', '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

# execute create10tables to generate tables and data

Call create10tables ()

With the tables and data, you can see the newly generated corresponding table names in the innodb_ tables table of the information_schema database. Here, you can look up all the table names through this table, 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.TABLES where table_name like "ttest%"

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

# execute selectdata

Call selectalldata ()

After execution, you can see in the t _ test table that all the data has been extracted.

In practice, you can modify the corresponding SQL statement as needed.

Mysql how to operate the same type of multiple tables to extract to a table will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.