MySQL 删除所有表，忽略外键。

Question

MySQL 删除所有表，忽略外键。

mysqlforeign-keysinnodbsql-drop

565

有没有一种简便的方法可以从MySQL数据库中删除所有表，忽略可能存在的任何外键约束？

- bcmcfc

8

如果你没有大量的其他实体需要保留，为什么不直接使用 "DROP DATABASE" 从头开始呢？ - StuartLC

221

为保护用户特权。 - bcmcfc

7

我刚意识到，与此同时，Dion Truter给出的答案比我的更加完整，建议接受他的答案。（"删除所有表"的部分不包含在我的答案中） - chiccodoro

6

如果您安装了phpMyAdmin，选择所有表并删除它们非常容易。 - User

1

这是正确的，但仅适用于phpMyAdmin 4.x版本。如果您选择所有表并从下拉菜单中选择“删除”，则可以取消选中“外键检查”复选框。 - jmarceli

显示剩余3条评论

27个回答

161

来自http://www.devdaily.com/blog/post/mysql/drop-mysql-tables-in-any-order-foreign-keys：

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists customers;
drop table if exists orders;
drop table if exists order_details;
SET FOREIGN_KEY_CHECKS = 1;

(请注意，这个回答解决的是如何禁用外键检查，以便能够任意顺序删除表格。它不是回答如何自动生成所有现有表格的drop-table语句并在单个脚本中执行它们。 Jean的回答可以解决这个问题。)

- chiccodoro

17

如果您使用MySQL Workbench，可以通过在左列选择所有表，右键单击，然后选择“删除表”的选项来避免输入所有表名。它将生成SQL语句，您可以将其复制并粘贴在SET FOREGIN_KEY_CHECKS语句之间 - 在其他GUI中可能类似。 - chris

感谢您的帮助，@chris非常有帮助，应该添加一个答案。 - Andrew

129

这里是SurlyDre的存储过程，已经修改为忽略外键：

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    SET FOREIGN_KEY_CHECKS = 0;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END$$

DELIMITER ;

call drop_all_tables(); 

DROP PROCEDURE IF EXISTS `drop_all_tables`;

- Jean-François Beauchamp

2

可以用于表格，但无法删除视图。仍然大大减少了我的打字量 :) 谢谢。 - chrisinmtown

1

7年后，我会建议您使用反引号包装_tableName。否则，在某些表格（如“group”或其他关键字）上将无法正常运行。 - sashaaero

注意视图！ SELECT table_name FROM information_schema.TABLES WHERE table_schema = SCHEMA() AND table_type="BASE TABLE"; - Glaubule

谢谢 @Jean-François和@Alexey！你们的回答帮助了我将近十年之久！赞！ - theHeman

73

据我所知，以上每种方法都比这种方法需要更多的工作...

( mysqldump --add-drop-table --no-data -u root -p database | grep 'DROP TABLE' ) > ./drop_all_tables.sql
mysql -u root -p database < ./drop_all_tables.sql

- SkyLeach

2

为什么不直接将 mysqldump 的输出通过管道传递给 mysql，而不需要通过文件来传递？ - Rolf

3

@Rolf 没有特别的原因。尽情享受吧。 - SkyLeach

3

谢谢，我已经按照您的建议进行了调整，去掉了括号，并将 mysqldump 的输出通过管道传递给 grep 再传递给 mysql，现在可以正常运行了。再次感谢您的解决方案，非常好。 - Rolf

8

这个缺少外键约束；我根据你的答案添加了一个基于bash脚本的链接：https://gist.github.com/cweinberger/c3f2882f42db8bef9e605f094392468f - cweinberger

神保佑你！！！我在这里添加了您的片段，供Docker用户使用，并进行了静默修改。https://gist.github.com/jrichardsz/1552426802d46e0f7dd06b9dde1101c1#improve-docker - JRichardsz

显示剩余3条评论

41

从这个答案中得知：

执行：

  use `dbName`; --your db name here
  SET FOREIGN_KEY_CHECKS = 0; 
  SET @tables = NULL;
  SET GROUP_CONCAT_MAX_LEN=32768;

  SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables
  FROM   information_schema.tables 
  WHERE  table_schema = (SELECT DATABASE());
  SELECT IFNULL(@tables, '') INTO @tables;

  SET        @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
  PREPARE    stmt FROM @tables;
  EXECUTE    stmt;
  DEALLOCATE PREPARE stmt;
  SET        FOREIGN_KEY_CHECKS = 1;

这将从当前使用的数据库中删除表。您可以使用use设置当前数据库。

或者，Dion的答案更简单，但您需要执行两次，首先获取查询，然后执行查询。我提供了一些愚蠢的反引号来转义db和表名称中的特殊字符。

  SELECT CONCAT('DROP TABLE IF EXISTS `', table_schema, '`.`', table_name, '`;')
  FROM   information_schema.tables
  WHERE  table_schema = 'dbName'; --your db name here

- nawfal

2

简洁明了的解决方案。比流程替代方案好得多。 - Rafael Renan Pacheco

如果没有表格，这个方法就不起作用。将SELECT IFNULL(...)和SET @tables = CONCAT(...)这两行替换为SELECT IF(@tables IS NULL, 'SELECT NULL FROM (SELECT NULL) AS empty WHERE 0=1', CONCAT('DROP TABLE IF EXISTS ', @tables)) INTO @tables; - Mirco Babin

请查看我的答案进行调整：https://dev59.com/lHA75IYBdhLWcg3wGU-I#76064028 - Mirco Babin

20

这里是基于游标的解决方案。比较冗长但可以作为单个SQL批次运行：

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;

END$$

DELIMITER ;

call drop_all_tables(); 

DROP PROCEDURE IF EXISTS `drop_all_tables`;

- SurlyDre

2

很好，但不幸的是它不能处理外键。 - Timmmm

14

删除指定数据库中所有表的一行命令：

echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"

运行此命令将从数据库DATABASE_NAME中删除所有表。

而且，这个好处是数据库名称只需显式写一次。

- mgershen

1

这对我有用，但在macOS High Sierra上，我必须将-i替换为-I。 - Ali Selcuk

1

谢谢@AliSelcuk。在Ubuntu上，-i和-I都适用于我，所以我会改为使用-I，使其在macOS上也能正常工作。 - mgershen

1

非常有用，谢谢！我必须使用 -pPASSWORD（-p 和 PASSWORD 之间没有空格）来传递密码到两个 MySQL 命令。 - Mike

1

重要的是在两个mysql命令后面也要添加-uUSER和-pPASS。将其添加到脚本中，在导入之前清空数据库非常有用。救了我的一天。 - Oliver M Grech

13

你可以这样做：

select concat('drop table if exists ', table_name, ' cascade;')
  from information_schema.tables;

接下来运行生成的查询语句。它们会删除当前数据库中的每个表。

这里提供了有关drop table命令的帮助。

- Pablo Santa Cruz

以上答案假定 || 被设置为连接运算符。更具体地说，MySQL SQL 模式包含 PIPES_AS_CONCAT。参考：http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_pipes_as_concat - Ionuț G. Stan

@Ionut：太棒了！感谢你指出这一点。已经修复了代码示例，使用concat代替||。 - Pablo Santa Cruz

13

使用存储过程实现从 SQL Select 查询中一步完成而无需复制返回值的解决方案。

SET FOREIGN_KEY_CHECKS = 0;
SET SESSION group_concat_max_len = 1000000;

SET @TABLES = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name,'`') INTO @TABLES FROM information_schema.tables 
  WHERE table_schema = 'databaseName';

SET @TABLES = CONCAT('DROP TABLE IF EXISTS ', @TABLES);

PREPARE stmt FROM @TABLES;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET SESSION group_concat_max_len = 1024;
SET FOREIGN_KEY_CHECKS = 1

- Jsowa

12

每次搜索特定主题时，Google都会把我导向这个Stack Overflow的问题，因此在这里提供可以删除表格和视图的MySQL代码：

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    SET FOREIGN_KEY_CHECKS = 0;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql1 = CONCAT('DROP TABLE IF EXISTS ', _tableName);
        SET @stmt_sql2 = CONCAT('DROP VIEW IF EXISTS ', _tableName);

        PREPARE stmt1 FROM @stmt_sql1;
        PREPARE stmt2 FROM @stmt_sql2;

        EXECUTE stmt1;
        EXECUTE stmt2;

        DEALLOCATE PREPARE stmt1;
        DEALLOCATE PREPARE stmt2;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END$$

DELIMITER ;

call drop_all_tables();

DROP PROCEDURE IF EXISTS `drop_all_tables`;

- Sergey Alaev

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Dion Truter · Accepted Answer

我发现生成的drop语句集非常有用，推荐进行以下改进：

将生成的drop语句限制在你的数据库内，可以使用如下方法：

SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';

注意1：这并不会执行DROP语句，它只会给你一个列表。你需要将输出剪切粘贴到你的SQL引擎中来执行。

注意2：如果你有视图（VIEWs），你需要手动将每个DROP TABLE `VIEW_NAME`语句更正为DROP VIEW `VIEW_NAME`。

根据http://dev.mysql.com/doc/refman/5.5/en/drop-table.html的说明，使用级联删除是毫无意义和误导性的：

"RESTRICT和CASCADE被允许以使移植更容易。在MySQL 5.5中，它们什么也不做。"

因此，如果你需要删除语句起作用，你需要：

SET FOREIGN_KEY_CHECKS = 0

这将禁用引用完整性检查 - 所以当你完成所需的删除操作后，你需要重置关键检查，使用以下命令：

SET FOREIGN_KEY_CHECKS = 1

最终执行结果应该如下所示：

SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;

注意：为了更轻松地使用SELECT语句的输出，可以使用mysql -B选项。