如何删除具有特定前缀的所有 MySQL 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18279468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I drop all MySQL Databases with a certain prefix?
提问by T. Brian Jones
I need to drop hundreds of mysql databases that all share a common prefix, but have random id's as the rest of their name ( eg. database_0123456789, database_9876543210 ). All these databases are on the same server. There are other databases on that same server that I don't want to drop.
我需要删除数百个 mysql 数据库，它们都共享一个公共前缀，但名称的其余部分是随机 ID（例如 database_0123456789、database_9876543210）。所有这些数据库都在同一台服务器上。我不想删除同一台服务器上的其他数据库。
This is what I'd like to do:
这就是我想做的：
DROP DATABASE `database_*`;
How can I drop these efficiently? Is there a MySQL query I can run? Maybe a shell script?
我怎样才能有效地丢弃这些？是否有我可以运行的 MySQL 查询？也许是一个shell脚本？
回答by spencer7593
The syntax of the DROP DATABASEstatement supports only a single database name. You will need to execute a separate DROP DATABASEstatement for each database.
该DROP DATABASE语句的语法仅支持单个数据库名称。您需要DROP DATABASE为每个数据库执行单独的语句。
You can run a query to return a list of database names, or maybe more helpful, to generate the actual statements you need to run. If you want to drop all databases that start with the literal string database_(including the underscore character), then:
您可以运行查询以返回数据库名称列表，或者可能更有帮助，以生成您需要运行的实际语句。如果要删除所有以文字字符串database_（包括下划线字符）开头的数据库，则：
SELECT CONCAT('DROP DATABASE `',schema_name,'` ;') AS `-- stmt`
FROM information_schema.schemata
WHERE schema_name LIKE 'database\_%' ESCAPE '\'
ORDER BY schema_name
Copy the results from that query, and you've got yourself a SQL script.
复制该查询的结果，您就拥有了一个 SQL 脚本。
(Save the results as plain text file (e.g. dropdbs.sql), review with your favorite text editor to remove any goofy header and footer lines, make sure the script looks right, save it, and then from the mysql command line tool, mysql> source dropdbs.sql.)
（将结果保存为纯文本文件（例如 dropdbs.sql），使用您最喜欢的文本编辑器查看以删除任何愚蠢的页眉和页脚行，确保脚本看起来正确，保存它，然后从 mysql 命令行工具中，mysql> source dropdbs.sql. )
Obviously, you could get more sophisticated than that, but for a one-time shot, this is probably the most efficient.)
显然，您可以变得更复杂，但对于一次性拍摄，这可能是最有效的。）
回答by Sylvain Leroux
Don't need of an external script file. A stored procedure using prepare statements might do the trick:
不需要外部脚本文件。使用准备语句的存储过程可能会起作用：
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;
Once you have that procedure, you just have to:
完成该程序后，您只需：
CALL kill_em_all();
When done:
完成后：
DROP PROCEDURE kill_em_all
回答by Jürgen Steinblock
This question lacks an answer without creating a file first.
如果不先创建文件，这个问题就没有答案。
Our build server automatically creates a database for every topic branch while running unit tests. After information_schema queries get really slow which causes our tests to fail.
我们的构建服务器在运行单元测试时自动为每个主题分支创建一个数据库。在 information_schema 查询变得非常缓慢导致我们的测试失败之后。
I created a batch file which runs every day. I did not want to deal with temporary files. So here is my solution.
我创建了一个每天运行的批处理文件。我不想处理临时文件。所以这是我的解决方案。
@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%
回答by Pardeep Kumar
Modifying spencer7593 answer
修改 spencer7593 答案
Here is the command to find desired results and save it in file where prefix is database prefix
这是查找所需结果并将其保存在文件中的命令，其中前缀是数据库前缀
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';
if you get permission denied then change folder permission to 777 or change folder group to mysql using this
如果您的权限被拒绝，则将文件夹权限更改为 777 或使用此将文件夹组更改为 mysql
chown -R mysql /var/www/pardeep/
then run this query
然后运行这个查询
source /var/www/pardeep/file.txt;
