MySQL 批量删除表 哪里的表像？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11053116/
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
MySQL bulk drop table where table like?
提问by ThinkCode
DROP TABLE (
SELECT table_name
FROM information_schema.`TABLES`
WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%');
I know this doesn't work! What is the equivalent for something like this in SQL? I can whip out a simple Python script to do this but was just wondering if we can do something with SQL directly. I am using MySQL. Thank you!
我知道这行不通！SQL 中这样的东西的等价物是什么？我可以编写一个简单的 Python 脚本来执行此操作，但只是想知道我们是否可以直接使用 SQL 执行某些操作。我正在使用 MySQL。谢谢！
回答by Devart
You can use prepared statements-
您可以使用准备好的语句-
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name,'`') INTO @tables FROM information_schema.tables
WHERE table_schema = 'myDatabase' AND table_name LIKE BINARY 'del%';
SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
It will generate and execute a statement like this -
它将生成并执行这样的语句 -
DROP TABLE myDatabase.del1, myDatabase.del2, myDatabase.del3;
回答by Ron Klein
A minor improvement to @Devart's answer:
对@Devart 的回答略有改进：
SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.`', table_name, '`') INTO @tables FROM
(select * from
information_schema.tables
WHERE table_schema = 'myDatabase' AND table_name LIKE 'del%'
LIMIT 10) TT;
SET @tables = CONCAT('DROP TABLE ', @tables);
select @tables;
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
This script should be executed repeatedly until the console's output is NULL
此脚本应重复执行，直到控制台的输出为 NULL
The changes are:
变化是：
- backtick (`) wrapping the table name (if it contains non standard characters)
- added a
LIMITto avoid the truncation issue I commented about - added a "print" (
select @tables;) to have some kind of control when to stop executing the script
- 反引号 (`) 包裹表名（如果它包含非标准字符）
- 添加了一个
LIMIT以避免我评论的截断问题 - 添加了“打印”(
select @tables;) 以控制何时停止执行脚本
回答by Rafael Kitover
If you just need to quickly drop a bunch of tables (not in pure SQL, so not directly answering this question) a one line shell command can do it:
如果你只需要快速删除一堆表（不是在纯 SQL 中，所以不直接回答这个问题）一个单行 shell 命令可以做到：
echo "show tables like 'fsm%'" | mysql | tail +2 | while read t; do echo "drop table \`$t\`;"; done | mysql
回答by A C
I found it useful to add an IFNULL to Devart's solutions to avoid generating an error if there are no tables matching the query.
我发现在 Devart 的解决方案中添加一个 IFNULL 以避免在没有与查询匹配的表时产生错误很有用。
SET @tables = IFNULL(CONCAT('DROP TABLE ', @tables),'SELECT NULL;');
回答by mbrampton
You can do this quickly and easily if you have phpMyAdmin available and the requirement is to drop tables with a specific prefix. Go to the database you want, and show the list of all the tables. Since tables are shown in alphabetic order, the tables with the prefix for deletion will all appear together. Go to the first one, and click the tick box on the left hand side. Then scroll down to the last table with the prefix, hold down shift, and click the tick box. That results in all the tables with the prefix being ticked. Go to the bottom of the list, and select the action drop for all selected tables. Go through with the deletion, checking that the SQL generated looks right!
如果您有可用的 phpMyAdmin 并且需要删除具有特定前缀的表，您可以快速轻松地完成此操作。转到您想要的数据库，并显示所有表的列表。由于表是按字母顺序显示的，带有删除前缀的表将一起出现。转到第一个，然后单击左侧的复选框。然后向下滚动到带有前缀的最后一个表，按住 shift，然后单击复选框。这会导致所有带有前缀的表都被打勾。转到列表底部，并为所有选定的表选择操作放置。执行删除操作，检查生成的 SQL 是否正确！
