Drop multiple MySQL tables

Since MySQL's DROP TABLE statement requires specifying each table you wish to drop, dropping many tables at once can be a pain; especially if you have InnoDB tables with foreign key constraints.  With the following stored procedure, tweaked from some of the helpful comments on the MySQL documentation, you can drop any tables matching a particular regular expression pattern.

delimiter $$
create procedure drop_tables_rlike(pattern varchar(255), db varchar(255))
    begin
        -- Try not to chop off the table list
        SET SESSION group_concat_max_len = 10000;
   
        select @str_sql:=concat('drop table ', group_concat(table_name))
        from information_schema.tables
        where table_schema=db and table_name rlike pattern;

        -- disable foreign key checks
        set foreign_key_checks=0;
       
        prepare stmt from @str_sql;
        execute stmt;
        drop prepare stmt;
        -- re-enable foreign key checks
        set foreign_key_checks=1;
    end$$
delimiter ;

And to run it:

call drop_tables_rlike('prefix_.*', 'database');

If you don't know regular expressions, or would just like to use the simpler LIKE matching, remove the 'r' from each 'rlike'.

One issue is that if you are dropping a very large number of tables, you may exceed the GROUP_CONCAT length limit.  You can try upping the limit set in the procedure to prevent this, but it may be subject to the server's max_allowed_packet setting.  Though an error occurs when the server tries to drop the final, truncated, table in the list, all prior tables are dropped succesfully; running the same command again will drop the remaining tables.