MySQL 删除名称与模式匹配的多个数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5457286/
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
Drop multiple databases with names matching a pattern
提问by shantanuo
I want to drop all the databases starting with a word.
我想删除所有以一个单词开头的数据库。
abc
xyz
cms_db1
cms_db2
cms_xyz
pqr
In the example given above, I will like to drop all the Databases starting with the word "cms". I guess maatkit or shell script can do it. What is the best approach?
在上面给出的示例中，我想删除所有以“cms”一词开头的数据库。我猜 maatkit 或 shell 脚本可以做到。最好的方法是什么？
采纳答案by neurino
Linux way:
Linux方式：
#!/bin/bash
DB_STARTS_WITH="cms"
MUSER="root"
MPWD="yourpass"
MYSQL="mysql"
DBS="$($MYSQL -u$MUSER -p$MPWD -Bse 'show databases')"
for db in $DBS; do
if [[ "$db" =~ "^${DB_STARTS_WITH}" ]]; then
echo "Deleting $db"
$MYSQL -u$MUSER -p$MPWD -Bse "drop database $db"
fi
done
Of course use the droppart at your own risk ;)
当然，使用该drop部分的风险由您自己承担；)
回答by cloakedninjas
Here's a pure mySQL solution in two queries:
这是两个查询中的纯 mySQL 解决方案：
SELECT CONCAT('DROP DATABASE `', SCHEMA_NAME, '`;')
FROM `information_schema`.`SCHEMATA`
WHERE SCHEMA_NAME LIKE 'cms_%';
Then copy and paste the resulting recordset and run
然后复制并粘贴生成的记录集并运行
回答by swarley
I had to improve neurinos script because of special chars in my password, missing 'drop DATABASE ...' and not working comparision for DB_STARTS_WITH expression. The following script did work on Ubuntu Server:
由于我的密码中有特殊字符，我不得不改进 neurinos 脚本，缺少“drop DATABASE ...”并且无法比较 DB_STARTS_WITH 表达式。以下脚本在 Ubuntu 服务器上运行：
#!/bin/bash
DB_STARTS_WITH="grp"
MUSER="root"
MPWD="YOUR_PASSWORD"
MYSQL="mysql"
DBS="$($MYSQL -u $MUSER -p"$MPWD" -Bse 'show databases')"
for db in $DBS; do
if [[ "$db" == $DB_STARTS_WITH* ]]; then
echo "Deleting $db"
$MYSQL -u $MUSER -p"$MPWD" -Bse "drop database $db"
fi
done
回答by dkubb
I would use something like:
我会使用类似的东西：
echo "SHOW DATABASES LIKE 'cms_%'" \
| mysql \
| tail -n +2 \
| xargs -n1 mysqladmin -f drop
If you don't have your default username and password configured inside ~/my.cnf, you may need to supply the username and password via the -uand -pswitches to the mysql/mysqladmin commands above.
如果你没有在里面配置你的默认用户名和密码~/my.cnf，你可能需要通过-u和-p切换到上面的 mysql/mysqladmin 命令来提供用户名和密码。
(Edit - added -n arg to tail.)
（编辑 - 添加 -n arg 到尾部。）
回答by Léo Alves de Araujo
A Linux way:
Linux方式：
for db_name in $(mysql -u USER -pPASS -e "show databases like 'cms_%'" -ss)
do
mysql -u USER -pPASS -e "drop database ${db_name}";
done
回答by v010dya
If you wish to stay completely within MySQL/MariaDB (i.e. without using bash scripts and such) you can do the following:
如果您希望完全留在 MySQL/MariaDB 中（即不使用 bash 脚本等），您可以执行以下操作：
DELIMITER //
CREATE PROCEDURE clean()
BEGIN
SET @query := (SELECT CONCAT('DROP DATABASE ', SCHEMA_NAME, ';') FROM `information_schema`.`SCHEMATA` WHERE SCHEMA_NAME LIKE 'dbtVDB%' LIMIT 1);
WHILE @query != '' DO
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @query := (SELECT CONCAT('DROP DATABASE ', SCHEMA_NAME, ';') FROM `information_schema`.`SCHEMATA` WHERE SCHEMA_NAME LIKE 'cms%' LIMIT 1);
END WHILE;
DELETE FROM mysql.db WHERE mysql.db.Db LIKE 'cms%';
END;
//
DELIMITER ;
CALL clean();
DROP PROCEDURE clean;
回答by Bad Cat
I liked the answer suggesting a "for" loop from the shell. In my case, I had subdirectory names matching my database names so I made arrays, then used them in the command.
我喜欢从 shell 中建议“for”循环的答案。就我而言，我的子目录名称与我的数据库名称匹配，因此我创建了数组，然后在命令中使用它们。
(I could have done this using the mysql data directory come to think of it, even if I hadn't had the setup I had. On my bitnami VM this is /opt/bitnami/mysql/data.)
（想到它，我可以使用 mysql 数据目录来完成此操作，即使我没有设置。在我的 bitnami VM 上，这是 /opt/bitnami/mysql/data。）
created array from subset of files: tbtdirs=(tbt*2015*)
Tested a potentially spooky command first w/ "echo": for d in ${tbtdirs[@]}; do echo mysql -pPASS -e "drop database $d"; done
dropped all databases in the array: for d in ${tbtdirs[@]}; do mysql -pPASS -e "drop database $d"; done
从文件子集创建数组：tbtdirs=(tbt*2015*)
首先用“echo”测试了一个可能令人毛骨悚然的命令：for d in ${tbtdirs[@]}; 做 echo mysql -pPASS -e "删除数据库 $d"; 完毕
删除数组中的所有数据库：for d in ${tbtdirs[@]}; 做 mysql -pPASS -e "删除数据库 $d"; 完毕
Worked like a charm! Also modified the loop to remove subdirectories. I used Linux command line for quite some time before learning how useful the bash commands could be.
像魅力一样工作！还修改了循环以删除子目录。在了解 bash 命令的有用性之前，我使用了 Linux 命令行很长一段时间。
回答by hamboy75
Using @léo-alves-de-araujo I have modified it to ask the user/password (More secure way) from command line (with linux)
使用 @léo-alves-de-araujo 我已经修改它以从命令行（使用 linux）询问用户/密码（更安全的方式）
#!/bin/bash
echo -n "Enter Mysql User:"
read user
echo -n "Enter Mysql Password:"
read -s password
for db_name in $(mysql -u $user --password=$password -e "SHOW DATABASES LIKE 'cms_%'" -ss 2>/dev/null)
do
mysql -u $user --password=$password -e "DROP DATABASE ${db_name}" 2>/dev/null;
done
回答by Dmitry Druganov
Improved @neurino solution to avoid storing of MySQL credentials in the script and passing them through a command line (it might be visible in the list of processes then)
改进了@neurino 解决方案，以避免在脚本中存储 MySQL 凭据并通过命令行传递它们（然后它可能在进程列表中可见）
#!/bin/bash
DB_STARTS_WITH="cms"
MYSQL="mysql"
read -p "Enter MySQL user name: " MYSQL_USER
read -s -p "Enter password: " MYSQL_PASSWORD
CREDENTIALS_FILE="$(mktemp)"
chmod 600 $CREDENTIALS_FILE
cat > $CREDENTIALS_FILE <<- EOM
[client]
user=$MYSQL_USER
password=$MYSQL_PASSWORD
EOM
trap "{ rm -f $CREDENTIALS_FILE; }" EXIT
DATABASES="$(echo "show databases;" | $MYSQL --defaults-file=$CREDENTIALS_FILE)"
for DATABASE in $DATABASES; do
if [[ $DATABASE =~ ^${DB_STARTS_WITH} ]]; then
echo Removing $DATABASE...
echo "drop database $DATABASE" | $MYSQL --defaults-file=$CREDENTIALS_FILE
fi
done
回答by Madhur Bhaiya
Improvising on the excellent answerby @cloakedninjas, for easier retrieval of all the queries to execute in a single string.
即兴的出色答卷由@cloakedninjas，对于所有的查询检索更容易在一个字符串执行。
Firstly, you can set the maximum value for group_concat_max_lento the maximum possible value, for this particular session:
首先，您可以group_concat_max_len针对此特定会话将最大值设置为可能的最大值：
SET SESSION group_concat_max_len = @@max_allowed_packet;
Now, you can prepare a query string (to execute later) using SQL. Using information_schema, we can get name of all the databases matching the pattern. Now, use Concat()to prepare a single DROP DATABASE ..query, and then utilize Group_Concat()to merge them all into a single string, for easier retrieval.
现在，您可以使用 SQL 准备查询字符串（稍后执行）。使用information_schema，我们可以获得所有匹配该模式的数据库的名称。现在，用于Concat()准备单个DROP DATABASE ..查询，然后利用Group_Concat()将它们全部合并为一个字符串，以便于检索。
SELECT GROUP_CONCAT(CONCAT('DROP DATABASE `', SCHEMA_NAME, '`;')
SEPARATOR ' ') AS query_to_execute
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME LIKE 'cms_%'
Now copy the string in query_to_executeand run it separately.
现在复制字符串query_to_execute并单独运行它。
