删除与某些通配符匹配的MySQL数据库? [英] Drop MySQL databases matching some wildcard?

问题描述

我正在服务器中运行mySQL，我需要删除大量数据库(在对该服务器进行一些测试之后).我需要删除的所有数据库都具有相同的前缀"Whatever _".

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_".

在前缀之后，名称是随机的.因此，您拥有了Whatever_something，Whatever_232，Whatever_blabla，....，Whatever_imthelast.

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?

我可以使用任何一种语言或mysql插件...因此我们可以通过某些方式做到这一点.现在，我问正在生成数据库的那个人给我一个.txt，其中每个名称都在一行中...所以我正在编码一个快速的php，它将取一个文件并删除其中的所有数据库，稍后我将尝试％答案(如果可行，则可以确定正确答案，以确保采用更简单的方法).无论如何，我想以一种更简单的方式来执行此操作，因为我将无法支持此代码(其他人也会，而且您知道...)

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... )

修改2: 使用通配符无效:#1008-无法删除数据库"whatever_％"；数据库不存在

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

推荐答案

基本思想是在数据库中运行显示表"，然后使用该结果选择 您想要的表.我认为MySQL不能让您使用显示表​​"的结果集做任何事情， 但我可能错了.

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.

以下是使用shell的快捷解决方案:

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 @@"

这将打印出所有shell命令，以删除以"Whatever_"开头的表.如果您希望它实际执行这些命令，请删除单词"echo".

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".

编辑:我忘了解释以上内容！我不知道您对Shell脚本的熟悉程度，但是可以这样:

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

打印出所有表的列表，标题为"Tables_in_your_database_name".该命令的输出通过下一条命令通过管道传递(|符号表示"piped"，如传递的一样)

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_"

搜索单词"Whatever_"开头的任何行(^符号表示与……在一起")，仅打印这些行.最后，我们通过以下命令通过管道传递"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 @@"

因此，如果您有一堆名为"Whatever_1"，"Whatever_2"，"Whatever_3"的表，则生成的命令将是:

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"

将输出以下内容:

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"

我希望这是足够的细节，而且我不只是要用太多的信息来殴打任何头上的人.祝您好运，并在使用"DROP TABLE"命令时要小心！

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!

这篇关于删除与某些通配符匹配的MySQL数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！