SQL: deleting tables with prefix

Some of the earlier answers were very good. I have pulled together their ideas with some notions from other answers on the web.

I needed to delete all tables starting with 'temp_' After a few iterations I came up with this block of code:

-- Set up variable to delete ALL tables starting with 'temp_'
SET GROUP_CONCAT_MAX_LEN=10000;
SET @tbls = (SELECT GROUP_CONCAT(TABLE_NAME)
               FROM information_schema.TABLES
              WHERE TABLE_SCHEMA = 'my_database'
                AND TABLE_NAME LIKE 'temp_%');
SET @delStmt = CONCAT('DROP TABLE ',  @tbls);
-- SELECT @delStmt;
PREPARE stmt FROM @delStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I hope this is useful to other MySQL/PHP programmers.

You cannot do it with just a single MySQL command, however you can use MySQL to construct the statement for you:

In the MySQL shell or through PHPMyAdmin, use the following query

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

This will generate a DROP statement which you can than copy and execute to drop the tables.

EDIT: A disclaimer here - the statement generated above will drop all tables in all databases with that prefix. If you want to limit it to a specific database, modify the query to look like this and replace database_name with your own database_name:

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