Drop MySQL databases matching some wildcard?

Question

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

Answer 1

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!

Answer 2

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