Problems with table names as parameters in MySQL

Recently due to the exception of the program, resulting in the database created a large number of tables (about 4,000), tangled in the table data is also useful.

Need to merge into a table, the first thought is to use stored procedures to handle, but because the table names are generated dynamically, you need to resolve the problem of handling table names as parameters in stored procedures.

1, using SET or Declare statement to define the table name as a variable, in the SQL table name location using variables, validation does not work, the database will be the variable name as the table name.

2, the operation to be done directly with concat stitching out, and then directly execute the SQL. How you can execute a stitched SQL in a stored procedure can be done by looking for it using prepare.

Here is the sample code:

Inserts the data from the table test_1~test_100 into the test table.

A. Data insertion using INSERT INTO ... select. From ... on duplicate key update xxx=$$; ("None is inserted, there is Update").

B. The existence of the table is determined before the operation of the table test_x. The related systems in the MySQL library are stored in the library Information_schema , and the information related to the tables is stored in the table TABLES .

1 BEGIN2         DECLARETotalINT DEFAULT  -;3 4          whileTotal>0 Do5 # First determine if the table test_x exists6             SELECT Count(table_name) into @tbCount  fromInformation_schema. TABLESWHERETable_schema='Test'  andtable_name=CONCAT ('Test_', total); 7             IF @tbCount >0  Then8                 SET @InsertData =CONCAT ('INSERT INTO Test (id,name) Select T2.id,t2.name from Test_', Total,'T2 on duplicate key update Name=t2.name');9                 PREPAREstmt from @InsertData; Ten                 EXECUTEstmt; One                 SET @dropTable =CONCAT ('DROP TABLE test_', Total,';'); A                 PREPAREStmt1 from @dropTable;  -                 EXECUTESTMT1; -             END IF; the  -             SETTotal=Total-1; -  -         END  while; +  - END;

Problems with table names in MySQL as parameters