MySQL DROP所有表，忽略外键

是否有一个很好的简单的方法来删除MySQL数据库中的所有表，忽略任何外键约束，可能在那里?

当前回答

简单明了(可能)。

也许不是一个很好的解决方案，但这对我很有效，帮了我大忙。

服务版本:5.6.38 MySQL Community Server (GPL)

我遵循的步骤:

 1. generate drop query using concat and group_concat.
 2. use database
 3. disable key constraint check
 4. copy the query generated from step 1
 5. enable key constraint check
 6. run show table

MySQL壳

mysql> SYSTEM CLEAR;
mysql> SELECT CONCAT('DROP TABLE IF EXISTS `', GROUP_CONCAT(table_name SEPARATOR '`, `'), '`;') AS dropquery FROM information_schema.tables WHERE table_schema = 'emall_duplicate';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dropquery                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DROP TABLE IF EXISTS `admin`, `app`, `app_meta_settings`, `commission`, `commission_history`, `coupon`, `email_templates`, `infopages`, `invoice`, `m_pc_xref`, `member`, `merchant`, `message_templates`, `mnotification`, `mshipping_address`, `notification`, `order`, `orderdetail`, `pattributes`, `pbrand`, `pcategory`, `permissions`, `pfeatures`, `pimage`, `preport`, `product`, `product_review`, `pspecification`, `ptechnical_specification`, `pwishlist`, `role_perms`, `roles`, `settings`, `test`, `testanother`, `user_perms`, `user_roles`, `users`, `wishlist`; |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> USE emall_duplicate;
Database changed
mysql> SET FOREIGN_KEY_CHECKS = 0;                                                                                                                                                   Query OK, 0 rows affected (0.00 sec)

// copy and paste generated query from step 1
mysql> DROP TABLE IF EXISTS `admin`, `app`, `app_meta_settings`, `commission`, `commission_history`, `coupon`, `email_templates`, `infopages`, `invoice`, `m_pc_xref`, `member`, `merchant`, `message_templates`, `mnotification`, `mshipping_address`, `notification`, `order`, `orderdetail`, `pattributes`, `pbrand`, `pcategory`, `permissions`, `pfeatures`, `pimage`, `preport`, `product`, `product_review`, `pspecification`, `ptechnical_specification`, `pwishlist`, `role_perms`, `roles`, `settings`, `test`, `testanother`, `user_perms`, `user_roles`, `users`, `wishlist`;
Query OK, 0 rows affected (0.18 sec)

mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW tables;
Empty set (0.01 sec)

mysql>

2019-03-05 17:09:12

其他回答

我发现生成的drop语句集很有用，并推荐以下调整:

将生成的drop限制在数据库中，如下所示:

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

注1:这并不执行DROP语句，它只是给你一个它们的列表。您需要将输出剪切并粘贴到SQL引擎中才能执行它们。

注2:如果你有视图，你必须手动将每个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中，它们什么都不做。”

因此，为了让drop语句工作，如果你需要:

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选项可以帮助。

2011-11-23 19:54:08

如果在linux(或任何其他支持管道、echo和grep的系统)中，你可以用一行代码完成:

echo "SET FOREIGN_KEY_CHECKS = 0;" > temp.txt; \
mysqldump -u[USER] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP >> temp.txt; \
echo "SET FOREIGN_KEY_CHECKS = 1;" >> temp.txt; \
mysql -u[USER] -p[PASSWORD] [DATABASE] < temp.txt;

我知道这是一个老问题，但我认为这个方法又快又简单。

2014-11-11 18:17:48

DB="your database name" \
    && mysql $DB < "SET FOREIGN_KEY_CHECKS=0" \
    && mysqldump --add-drop-table --no-data $DB | grep 'DROP TABLE' | grep -Ev "^$" | mysql $DB \
    && mysql $DB < "SET FOREIGN_KEY_CHECKS=1"

2018-09-14 09:28:36

不从SQL复制返回值的一步解决方案选择使用过程的查询。

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

2020-09-20 20:00:45