How to delete all MySQL tables beginning with a certain prefix?

Question

I've found another thread on this question, but I wasn't able to use its solutions, so I thought I'd ask with more clarity and detail.

I have a large MySQL database representing a vBulletin forum. For several years, this forum has had an error generated on each view, each time creating a new table named aagregate_temp_1251634200, aagregate_temp_1251734400, etc etc. There are about 20,000 of these tables in the database, and I wish to delete them all.

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

Unfortunately this command doesn't work, and the Google results for this problem are full of elaborate stored procedures beyond my understanding and all seemingly tailored to the more complex problems of different posters.

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

Answer 1

There's no single statement to do that.

The simplest approach is to generate a set of statements, and execute them individually.

We can write a simple query that will generate the statements for us:

 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

The SELECT statement returns a rowset, but each row conveniently contains the exact SQL statement we need to execute to drop a table. (Note that information_schema is a builtin database that contains metadata. We'd need to replace mydatabase with the name of the database we want to drop tables from.

We can save the resultset from this query as a plain text file, remove any heading line, and voila, we've got a script we can execute in our SQL client.

There's no need for an elaborate stored procedure.

Answer 2

From memory you have to use prepared statements, for example: plenty of samples on stack exchange

I would recommend this example:

SQL: deleting tables with prefix

The SQL from above, this one includes the specific databasename - it builds 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 is a different way to do it:

MySQL bulk drop table where table like?

Answer 3

A little googling 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 certain string

Answer 4

This will delete all tables with prefix "mg_"

No need to copy and paste rowsets and in phpadmin copying and pasting is problematic as it will cut off long table names and replace them with '...' ruining set of sql commands.

Also note that '_' is a special character so thats why 'mg_' should be encoded as 'mg\_'

(and FOREIGN_KEY_CHECKS needs to be disabled in order 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;