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!