如何删除所有具有特定前缀的MySQL数据库？

Question

我需要删除数百个MySQL数据库，它们都有一个共同的前缀，但其余部分的名称是随机ID（例如 database_0123456789，database_9876543210）。所有这些数据库都在同一台服务器上。在服务器上还有其他我不想删除的数据库。

以下是我想做的事情：

DROP DATABASE `database_*`;

我该如何高效地删除它们？是否有一个MySQL查询可以运行？或者一个shell脚本？

- T. Brian Jones

---

展示类似于“database_%”数据库的命令可能是一个不错的开始，我相信你可以继续。删除数据库：SHOW DATABASES LIKE "database_%" - VoronoiPotato

这个链接可能会有帮助：http://dba.stackexchange.com/questions/1018/mysql-drop-table-starting-with-a-prefix - Arash Mousavi

@Raymond N：这是有可能的。但也有可能这些数据库是由于测试（JUnit测试）而创建的，没有被清理掉，或者是用于测试部署模式升级和回滚的模式。还有一些其他的可能性可以解释为什么有很多（现在不再需要的）数据库。 - spencer7593

@spencer7593 真的从未跨越过我的思维，感谢您的评论。 - Raymond Nijland

可能是重复的问题：SQL：删除具有前缀的表 - goozez

Answer 1

DROP DATABASE语句的语法仅支持单个数据库名称。您需要为每个数据库执行单独的DROP DATABASE语句。

您可以运行查询以返回数据库名称列表，或者更有用的是生成实际需要运行的语句。如果您想删除所有以字面字符串database_（包括下划线字符）开头的数据库，则应执行以下操作:

SELECT CONCAT('DROP DATABASE `',schema_name,'` ;') AS `-- stmt`
  FROM information_schema.schemata
 WHERE schema_name LIKE 'database\_%' ESCAPE '\\'
 ORDER BY schema_name

将查询结果复制，您就得到了一个SQL脚本。

---

(将结果保存为纯文本文件（例如dropdbs.sql），使用您喜欢的文本编辑器进行审核以删除任何奇怪的头部和尾部行，确保脚本看起来正确，保存它，然后从mysql命令行工具中执行 mysql> source dropdbs.sql。)

显然，您可以变得更加复杂，但对于一次性的操作，这可能是最有效的方法。

- spencer7593

---

应该使用CONCAT('DROP DATABASE \',schema_name,'` ;')`，否则在许多不同的情况下会出现错误，比如数据库名称包含破折号时。 - But those new buttons though..

Answer 2

不需要外部脚本文件。使用准备语句的存储过程可能会起到作用：

CREATE PROCEDURE kill_em_all()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE dbname VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT schema_name
      FROM information_schema.schemata
     WHERE schema_name LIKE 'database\_%' ESCAPE '\\'
     ORDER BY schema_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO dbname;

        IF done THEN
          LEAVE read_loop;
        END IF;

        SET @query = CONCAT('DROP DATABASE ',dbname);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
    END LOOP;
END;

一旦您掌握了该过程，您只需要：

CALL kill_em_all();

完成后：

DROP PROCEDURE kill_em_all

- Sylvain Leroux

---

好的。但我个人认为，如果这样的一个过程在我的MySQL实例中潜伏着，我会晚上难以入睡，并且白天会有神经性抽搐。不过，运行 DROP PROCEDURE kill_em_all 就可以解决这个问题。 - spencer7593

Answer 3

在创建文件之前，这个问题缺乏答案。

我们的构建服务器在运行单元测试时自动为每个主题分支创建一个数据库。当information_schema查询变得非常缓慢时，会导致我们的测试失败。

我创建了一个批处理文件，每天运行一次。我不想处理临时文件。所以这是我的解决方案。

@ECHO OFF
REM drops all databases excluding defaults
SET user=user
SET pass=pass

mysql ^
-u %user% ^
-p%pass% ^
-NBe "SELECT CONCAT('drop database `', schema_name, '`;') from information_schema.schemata where schema_name NOT IN ('mysql', 'test', 'performance_schema', 'information_schema')" | mysql -u %user% -p%pass%

- Jürgen Steinblock

Answer 4

修改Spencer7593的答案

以下是找到所需结果并将其保存在文件中的命令，其中前缀为数据库前缀

 SELECT CONCAT('DROP DATABASE ',schema_name,' ;') AS stmt
 FROM information_schema.schemata
 WHERE schema_name LIKE 'prefix\_%' ESCAPE '\\'
 ORDER BY schema_name into outfile '/var/www/pardeep/file.txt';

如果您收到“权限被拒绝”的错误，则可以使用以下命令将文件夹权限更改为777或更改文件夹组为mysql：

chown -R mysql /var/www/pardeep/

然后运行这个查询

source /var/www/pardeep/file.txt;

- Pardeep Kumar

---

嗨，warvariuc，我收到了关于代码的通知，但是除了格式之外，我没有发现你改变了任何东西。我在这里错过了什么吗？？？ - Pardeep Kumar