How drop multiple tables in MySQL

To create DROP statements for all table begins with 'prefix_' from database 'my_database':

> SELECT CONCAT('DROP TABLE',table_name,';') FROM information_schema.TABLES 
    WHERE table_schema = 'my_database' AND table_name LIKE 'prefix_%';

Or better yet, create a one-liner:

> SELECT CONCAT('DROP TABLE',GROUP_CONCAT(table_name),';') FROM information_schema.TABLES 
    WHERE table_schema = 'my_database' AND table_name LIKE 'prefix_%';

Example of creating store procedure:

mysql> delimiter $$
mysql> create procedure drop_tables_like(pattern varchar(255), db varchar(255))
    -> begin
    -> select @str_sql:=concat('drop table', group_concat(table_name))
    -> from information_schema.tables where table_schema=db and table_name like pattern;
    -> prepare stmt from @str_sql;
    -> execute stmt;
    -> drop prepare stmt;
    -> end$$
mysql> call drop_tables_like('prefix_','my_database')$$
mysql> drop procedure if exists drop_tables_like$$
mysql> delimiter;