Delete all tables starting with a specific prefix?

Question

I found another question on this issue, but I could not use its solutions, so I thought I would ask with more clarity and detail.

I have a large MySQL database representing the vBulletin forum. For several years, an error occurred on this forum at each submission, each time creating a new table named aagregate_temp_1251634200 , aagregate_temp_1251734400 , etc. Etc. The database contains about 20,000 of these tables, and I want to delete all of them.

I want to issue a command that says the equivalent of DROP TABLE WHERE TABLE_NAME LIKE 'aggregate_temp%'; .

Unfortunately, this command does not work, and the Google results for this problem are full of elaborate stored procedures that are different from my understanding, and they all seem to be adapted to the more complex problems of different posters.

Is it possible to write a simple statement that splits multiple tables based on a name like match?

Answer 1

There is no single statement for this.

The easiest way is to create a set of statements and execute them individually.

A simple query can generate statements for you:

  SELECT CONCAT('DROP TABLE `',t.table_schema,'`.`',t.table_name,'`;') AS stmt FROM information_schema.tables t WHERE t.table_schema = 'mydatabase' AND t.table_name LIKE 'aggregate\_temp%' ESCAPE '\\' ORDER BY t.table_name 

It just returns a set of rows, but rows conveniently contain the exact SQL statements that need to be executed. (Note: information_schema is a built-in database containing metadata. You just need to replace mydatabase with the name of the database from which you want to delete the tables.

Save the result set from this query as a regular text file, delete any header line and voila, you have a script that you can execute in your SQL client.

No need for a complex stored procedure.

Answer 2

A small search engine found this:

 SELECT 'DROP TABLE "' + TABLE_NAME + '"' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '[prefix]%' 

This should generate a script.

Source : Drop all tables whose names begin with a specific row.

Answer 3

From memory, you should use prepared statements, for example: many samples when exchanging tables

I would recommend this example:

SQL: deleting tables with a prefix

SQL on top, this includes a specific database name - it creates it for you

 SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%'; 

Here's how to do it:

MySQL overweight table, where the table is as follows:

Answer 4

This will delete all tables with the prefix "mg_"

There is no need to copy and paste rows and copy and paste gaps in phpadmin, as it will disable long table names and replace them with "...", destroying the sql command set.

Also note that "_" is a special character, so therefore "mg_" should be encoded as "mg \ _"

(and FOREIGN_KEY_CHECKS must be disabled to avoid error messages)

 SET FOREIGN_KEY_CHECKS = 0; SET GROUP_CONCAT_MAX_LEN=32768; SET @tables = NULL; SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables FROM information_schema.tables WHERE table_schema = (SELECT DATABASE()) and table_name like 'mg\_%'; SELECT IFNULL(@tables,'dummy') INTO @tables; SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables); PREPARE stmt FROM @tables; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1;