删除匹配某些通配符的 MySQL 数据库？

Drop MySQL databases matching some wildcard?

提问by DFectuoso

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

我在需要删除大量数据库的服务器中运行 mySQL（在对服务器进行了一些测试之后）。我需要删除的所有数据库都具有相同的前缀“Whatever_”。

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

在前缀之后，名称是随机的。所以你有你的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... )

编辑：我可以为 mysql 使用任何类型的语言或插件......所以我们可以以某种方式做到这一点。现在，我要求生成数据库的人给我一个 .txt，每个名称都在一行中……所以我编写了一个快速的 php，它将获取一个文件并删除其中的所有数据库，稍后我会尝试% 答案（如果它有效，它肯定会采用正确的答案，这是更简单的方法）。无论如何，我想以更简单的方式执行此操作，因为我将无法支持此代码（其他人会并且您知道...）

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

编辑 2：使用通配符不起作用：#1008 - 不能删除数据库 'whatever_%'; 数据库不存在

回答by Shalom Craimer

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.

基本思想是在您的数据库中运行“显示表”，并使用该结果来选择您想要的表。我认为 MySQL 不允许您对“显示表”的结果集执行任何操作，但我可能错了。

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

这是一个使用 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".

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

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

编辑：我忘了解释上面的内容！我不知道你对 shell 脚本有多熟悉，但这里有：

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:

打印出所有表的列表，标题为“Tables_in_your_database_name”。其输出通过下一个命令进行管道传输（| 符号表示“管道”，如传递的那样）：

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:

搜索任何以单词“Whatever_”开头（^ 符号表示“存在”）的行，并且只打印那些。最后，我们通过以下命令管理“Whatever_*”表列表：

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:

因此，如果您有一堆名为“Whatever_1”、“Whatever_2”、“Whatever_3”的表，则生成的命令将是：

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!

我希望这是足够的细节，而且我不只是用太多的信息打败任何人。祝你好运，使用“DROP TABLE”命令时要小心！

回答by tver3305

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

scraimer的回答原则是正确的，但由于问题是关于删除数据库而不是数据库中的表，正确的命令应该是：

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.

有关命令的解释，请查看scraimer的解释。

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

最后一点...... \`@@\` 我们在 bacticks(`) 中引用了我们的结果数据库名称，以防我们的数据库名称包含特殊字符，如 `-`

Cheers

干杯

回答by Explorer

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:

将 database_name 替换为数据库的名称（需要写权限）。要删除模式为 XYZ 的表，请调用输入为 XYZ 后跟通配符的过程，如下所示：

call droplike("XYZ%");

回答by mike rodent

what about this (Jython)?

这个（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 ) 

回答by james-geldart

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

这里有一些不错的整洁解决方案。但我所做的只是：

SHOW TABLES LIKE 'migrate%';

in MySQL workbench.

在 MySQL 工作台中。

Then I copied the results into a decent text editor than can find/replace using escape sequences, and replaced \nwith ;\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.

然后我将结果复制到一个不错的文本编辑器中，以便使用转义序列查找/替换，并替换\n为;\nDROP TABLE，并整理开始和结束。然后复制回MySQL工作台。与一些建议的解决方案相比，它更手动，更不优雅，但实际上同样快。

回答by arshabh

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

好吧，我认为您不能在 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 DROPcommand.

但我有一个非常令人讨厌的解决方案。您可以在 C/C++/C#/JAVA 中编程，将“ DROP DATABASE WHATEVER_<name>;”多次打印到记事本或任何文本编辑器中。之后，您可以在 MySql 的客户端命令提示符中复制粘贴，然后就可以了。不要忘记;每个DROP命令后面的“ ” 。

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

我相信这是可能的。试着写这个。

回答by depthfirstdesigner

In case someone is looking for a simple answer that mirrors scraimerand 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:

如果有人正在寻找一个简单的答案来反映scraimer和Explorer的答案，但它是使用 JDBC 用 Ja​​va 编写的（我知道我是），这里有一个快速而肮脏的答案，它为我完成了工作：

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.

警告：如果您不希望您的 SQL 服务器被洗劫一空，请不要向客户甚至其他开发人员公开这一点。

回答by Warrior

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

抱歉，我们无法使用 sql 命令一次删除多个数据库

回答by Yuval F

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.

您可以尝试使用mysql 5 通用例程库（可从此处下载）。使用它，您可以删除多个匹配正则表达式的表。我建议仔细检查正则表达式，以防止删除您确实需要的表。

回答by sanya_sp

DROP DATABASE `any-database_name`;

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

我只是`在我的数据库名称前后使用这个字符（反引号）。