Drop MySQL databases matching some wildcard?

Im runing mySQL in a server where i need to drop tons of databases (after some testing with the server). All databases that i need to drop have the same prefix "Whatever_".

After the prefix, the names are random. So you have your Whatever_something, Whatever_232, Whatever_blabla, .... , Whatever_imthelast.

I will be doing this job quite some times so i was wondering what would be the best way to do this?

EDIT: I can use any kind of language or plug in for mysql... so we CAN do this in some ways. Right now, i asked the guy that is generating the databases to give me a .txt with each name in a line... so im coding a quick php that will take a file and delete all the databases in it, later i will try the % answer(if it works, it takes the correct answer for sure its the easier way). Anyway i would like to do this the easier way coz i wont be able to support this code(other guys will and you know... )

edit 2: The use of a wildcard didnt work: #1008 - Can't drop database 'whatever_%'; database doesn't exist

The basic idea is to run "show tables" in your database, and use the results from that to select the tables you want. I don't think MySQL lets you do anything with the resultset from "show tables", but I'm probably wrong.

Here's a quick-and-dirty solution using the shell:

mysql -u your_user -D your_database_name -e "show tables" -s | 
  egrep "^Whatever_" | 
  xargs -I "@@" echo mysql -u your_user -D your_database_name -e "DROP TABLE @@"

That will print out all the shell commands to drop the tables beginning with "Whatever_". If you want it to actually execute those commands, remove the word "echo".

EDIT: I forgot to explain the above! I don't know how familiar you are with shell scripting, but here goes:

mysql -u your_user -D your_database_name -e "show tables" -s

prints out a list of all your tables, with the header "Tables_in_your_database_name". The output from that is piped (the | symbol means "piped", as in passed-on) through the next command:

egrep "^Whatever_"

searches for any lines that begin (that ^ symbols means "beings with") the word "Whatever_" and only prints those. Finally, we pipe that list of "Whatever_*" tables through the command:

xargs -I "@@" echo mysql -u your_user -D your_database_name -e "DROP TABLE @@"

which takes each line in the list of table names, and inserts it instead of the "@@" in the command

echo mysql -u your_user -D your_database_name -e "DROP TABLE @@"

So if you had a bunch of tables named "Whatever_1", "Whatever_2", "Whatever_3", the generated commands would be:

echo mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_1"
echo mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_2"
echo mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_3"

Which would output the following:

mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_1"
mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_2"
mysql -u your_user -D your_database_name -e "DROP TABLE Whatever_3"

I hope that was enough detail, and that I'm not just beating anyone over the head with too much information. Good luck, and be careful when using the "DROP TABLE" command!

The principle of the answer by scraimer is correct, but since the question was about dropping a database not a table in a database, the correct command should be:

mysql -u your_username -p'your password' -e 'show databases' 
| grep Whatever_* 
| xargs -I "@@" mysql -u your_username -p'your password' -e "DROP database \`@@\`"

For explanations of the command, look at scraimer's explanation.

The last bit... \`@@\` we have our resulting database name quoted in bacticks(`) in case our database name has special characters like `-`

Cheers

We can do this with stored procedures. Here is one below:

drop procedure if exists droplike;
delimiter //
create procedure droplike(pattern varchar(20))
begin
  set group_concat_max_len = 65535;
  select @drop:= concat( 'drop table ', group_concat(table_name) , ';' ) from information_schema.tables where table_schema = "database_name" and table_name like pattern;
  prepare statement from @drop;
  execute statement;
end //
delimiter ;

Replace database_name with the name of the database (write permission required). To drop tables with pattern XYZ call the procedure with the input as XYZ followed by wild card as given below:

call droplike("XYZ%");

Some nice tidy solutions here. But what I did was just:

SHOW TABLES LIKE 'migrate%';

in MySQL workbench.

Then I copied the results into a decent text editor than can find/replace using escape sequences, and replaced \n with ;\nDROP TABLE , and tidied up the start and finish. Then copied back into MySQL workbench. A bit more manual and less elegant than some of the proposed solutions, but actually just as quick.

well I think that you cannot delete multiple databases in MySql.

But I have a very geeky solution. you can program in C/C++/C#/JAVA to print many times "DROP DATABASE WHATEVER_<name>;" into a note pad or any text editor. After that you can copy paste in the client command prompt of MySql and there you go. don't forget the ";" after every DROP command.

I believe this is possible. try out to write this.

what about this (Jython)?

rs = stmt.executeQuery( "SHOW DATABASES LIKE 'Whatever_%'" )
databases_for_dropping = []
while rs.next():
  databases_for_dropping.append( rs.getString( 1 ))
for database_for_dropping in databases_for_dropping:
  stmt.execute( "DROP DATABASE %s" % database_for_dropping ) 

In case someone is looking for a simple answer that mirrors scraimer and Explorer's but is written in Java using JDBC (I know I was), here's a quick and dirty one that got the job done for me:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class DatabaseDeleter {
    // Pass your database wildcard as the only argument to program ( "%MY_CRAZY_PATTERN%")
    public void main(String[] args) throws SQLException, ClassNotFoundException{

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://databaseURL");
        PreparedStatement pst = connection.prepareStatement("show databases like ?");
        pst.setString(1, args[0]);
        ResultSet rs = pst.executeQuery();

        ArrayList<String> databases = new ArrayList<String>();
        while (rs.next()) databases.add(rs.getString(1));

        for (String s : databases){
            Statement st = connection.createStatement();
            st.execute("drop database " + s);
        }
        connection.close();
    }
}

Warning: Do not surface this to customers or even other developers if you don't want your SQL server ransacked.

Sorry we cannot drop multiple database at a time using sql commands

You can try the mysql 5 general purpose routine library (downloadable from here). Using it, you can drop multiple tables which match a regular expression. I suggest checking the regular expression carefully in order to prevent dropping tables you do need.

DROP DATABASE `any-database_name`;

I just use this character `` ` (backtick) before and after the name of my database.

Use the

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

statement as described here.

I don't know if you can use wildcars to do something like

DROP DATABASE Whatever_%

but I think you should try it.