如何在不删除数据库权限的情况下从命令行删除所有MySQL表?(复制)

This question already has an answer here:

这个问题已经有了答案:

MySQL DROP all tables, ignoring foreign keys 20 answers
MySQL删除所有表，忽略外键20个答案

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中删除所有表?我这样做的原因是，我们的用户可以访问数据库的下降，但是不能重新创建数据库本身，因此我们必须手动删除表。有办法把所有的桌子同时放下吗?请记住，大多数表都与外键连接，因此必须按特定的顺序删除它们。

5 个解决方案

#1

283

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;

#2

241

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.

如果您已经通过添加至少一个不存在的“虚拟”表来删除数据库中的所有表，那么这个脚本将不会引发错误。

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发现背勾的问题。

#3

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 table at the end of the generated script and run many times until the show table command results in an empty set.

为了克服外键检查效果，在生成的脚本末尾添加show table，并运行多次，直到显示表命令导致空集。

#4

You can drop the database and then recreate it with the below:-

您可以删除数据库，然后重新创建它与以下:-

mysql> drop database [database name];
mysql> create database [database name];

#5

The accepted answer does not work for databases that have large numbers of tables, e.g. Drupal databases. Instead, see the script here: https://*.com/a/12917793/1507877 which 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://*.com/a/12917793/1507877，它可以在MySQL 5.5上工作。注意:在第11行附近，有一个“WHERE table_schema = SCHEMA()”;它应该是"WHERE table_schema = 'INSERT NAME OF DB，以便进行导入";

#1

283