MySQL：如何使用单个查询删除多个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6758652/
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: how to drop multiple tables using single query?
提问by Angelin Nadar
I want to drop multiple tables with ease without actually listing the table names in the drop query and the tables to be deleted have prefix say 'wp_'
我想轻松删除多个表，而无需在删除查询中实际列出表名，并且要删除的表的前缀为“wp_”
采纳答案by Angelin Nadar
Just sharing one of the solutions:
只是分享其中一种解决方案：
mysql> SELECT CONCAT( "DROP TABLE ",
GROUP_CONCAT(TABLE_NAME) ) AS stmtFROM information_schema.TABLES
WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME LIKE "ur condition" into outfile '/tmp/a.txt';
mysql> source /tmp/a.txt;
mysql> SELECT CONCAT("DROP TABLE",
GROUP_CONCAT(TABLE_NAME)) AS stmt来自 information_schema.TABLES
WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME LIKE "ur condition" into outfile '/tmp/a.txt';
mysql> 源/tmp/a.txt;
回答by John P
I've used a query very similar to Angelin's. In case you have more than a few tables, one has to increase the max length of group_concat. Otherwise the query will barf on the truncated string that group_concatreturns.
我使用了一个与 Angelin 非常相似的查询。如果您有多个表，则必须增加group_concat. 否则，查询将在group_concat返回的截断字符串上出现。
This is my 10 cents:
这是我的 10 美分：
-- Increase memory to avoid truncating string, adjust according to your needs
SET group_concat_max_len = 1024 * 1024 * 10;
-- Generate drop command and assign to variable
SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema='databasename' AND table_name LIKE 'my_table%';
-- Drop tables
PREPARE str FROM @dropcmd; EXECUTE str; DEALLOCATE PREPARE str;
回答by marlboro51
Simple solution without risk of error:
没有错误风险的简单解决方案：
mysqldumpcreate a file that contains DROPcommand like
mysqldump创建一个包含DROP命令的文件，如
DROP TABLE IF EXISTS `wp_matable`;
a 'grep' with "DROP TABLE wp_" give us the commands to execute
带有“ grep”的“ DROP TABLE wp_”给我们要执行的命令
so drop is made by theses trhee lines (you can edit drop.sql to check which tables would be dropped before)
所以 drop 是由这些 trhee 行进行的（您可以编辑 drop.sql 以检查之前将删除哪些表）
mysqldump -u user -p database > dump.sql
grep "DROP TABLE `wp_" dump.sql > drop.sql
mysql -u user -p database < drop.sql
回答by Funkyhead
Be careful with "_", need to be written with "\" before in Mysql like:
小心“_”，在Mysql中需要在前面加上“\”，例如：
SELECT CONCAT('DROP TABLE',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM information_schema.tables WHERE table_schema='databasename' AND table_name LIKE '**my\_table**%';
回答by darkone_vt777
For the great mysqldump solution it's better to use the option --skip-quote-names
对于出色的 mysqldump 解决方案，最好使用选项 --skip-quote-names
mysqldump --skip-quote-names -u user -p database > dump.sql
grep "DROP TABLE wp_" dump.sql > drop.sql
mysql -u user -p database < drop.sql
You get rid of backticks in table names. The grep part won't work in some enviroments with the backticks.
您可以去掉表名中的反引号。grep 部分在某些带有反引号的环境中不起作用。
回答by Sohail Iqbal
Go to c:\xampp\mysql\data\your folder
转到 c:\xampp\mysql\data\您的文件夹
Select multiple tables that you want remove and then press delete button
选择要删除的多个表，然后按删除按钮
Thanks
谢谢
回答by krishnachaitanya
Dropping single table in mysql:
删除mysql中的单个表：
DROP TABLE TABLE_NAME;
删除表 TABLE_NAME;
