如何在没有 DROP 数据库权限的情况下从命令行删除所有 MySQL 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12403662/
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 to remove all MySQL tables from the command-line without DROP database permissions?
提问by Mantas
How do I drop all tables in Windows MySQL, using command prompt? The reason I want to do this is that our user has access to the database drops, but no access to re-creating the database itself, for this reason we must drop the tables manually. Is there a way to drop all the tables at once? Bear in mind that most of the tables are linked with foreign keys so they would have to be dropped in a specific order.
如何使用命令提示符删除 Windows MySQL 中的所有表？我想这样做的原因是我们的用户可以访问数据库删除，但无权重新创建数据库本身，因此我们必须手动删除表。有没有办法一次删除所有表？请记住，大多数表都与外键相关联，因此必须按特定顺序删除它们。
回答by Devart
You can generate statement like this: DROP TABLE t1, t2, t3, ...and then use prepared statements to execute it:
您可以生成这样的语句：DROP TABLE t1, t2, t3, ...然后使用准备好的语句来执行它：
SET FOREIGN_KEY_CHECKS = 0;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables
FROM information_schema.tables
WHERE table_schema = 'database_name'; -- specify DB name here.
SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;
回答by Kostanos
The @Devart's version is correct, but here are some improvements to avoid having error. I've edited the @Devart's answer, but it was not accepted.
@Devart 的版本是正确的，但这里有一些改进以避免出错。我已经编辑了@Devart 的答案，但没有被接受。
SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;
This script will not raise error with NULL result in case when you already deleted all tables in the database by adding at least one nonexistent - "dummy" table.
如果您已经通过添加至少一个不存在的“虚拟”表删除了数据库中的所有表，则此脚本不会引发 NULL 结果错误。
And it fixed in case when you have many tables.
如果你有很多桌子，它就会修复。
And This small change to drop all view exist in the Database
并且删除数据库中存在的所有视图的小更改
SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @views = NULL;
SELECT GROUP_CONCAT('`', TABLE_NAME, '`') INTO @views
FROM information_schema.views
WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@views,'dummy') INTO @views;
SET @views = CONCAT('DROP VIEW IF EXISTS ', @views);
PREPARE stmt FROM @views;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;
It assumes that you run the script from Database you want to delete. Or run this before:
它假定您从要删除的数据库运行脚本。或者在此之前运行：
USE REPLACE_WITH_DATABASE_NAME_YOU_WANT_TO_DELETE;
Thank you to Steve Horvath to discover the issue with backticks.
感谢 Steve Horvath 发现反引号问题。
回答by MutantMahesh
Try this.
尝试这个。
This works even for tables with constraints (foreign key relationships). Alternatively you can just drop the database and recreate, but you may not have the necessary permissions to do that.
这甚至适用于具有约束（外键关系）的表。或者，您可以删除数据库并重新创建，但您可能没有这样做的必要权限。
mysqldump -u[USERNAME] -p[PASSWORD] \
--add-drop-table --no-data [DATABASE] | \
grep -e '^DROP \| FOREIGN_KEY_CHECKS' | \
mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
In order to overcome foreign key check effects, add show tableat the end of the generated script and run many times until the show tablecommand results in an empty set.
为了克服外键检查的影响，show table在生成的脚本末尾添加并运行多次，直到show table命令结果为空集。
回答by user1086159
You can dropthe databaseand then recreateit with the below:-
你可以放下了database，然后重新创建与它下面： -
mysql> drop database [database name];
mysql> create database [database name];
回答by Giles B
The accepted answer does not work for databases that have large numbers of tables, e.g. Drupal databases. Instead, see the script here: https://stackoverflow.com/a/12917793/1507877which does work on MySQL 5.5. CAUTION: Around line 11, there is a "WHERE table_schema = SCHEMA();" This should instead be "WHERE table_schema = 'INSERT NAME OF DB INTO WHICH IMPORT WILL OCCUR';"
接受的答案不适用于具有大量表的数据库，例如 Drupal 数据库。相反，请参阅此处的脚本：https: //stackoverflow.com/a/12917793/1507877，它适用于 MySQL 5.5。注意：在第 11 行附近，有一个“WHERE table_schema = SCHEMA();” 这应该是“WHERE table_schema = 'INSERT NAME OF DB INTO WHICH IMPORT WILL OCCUR';”
