MySQL 强制删除mysql绕过外键约束

Force drop mysql bypassing foreign key constraint

提问by johnnyArt

I'm trying to delete all tables from a database except one, and I end up having the following error:

我试图从数据库中删除除一个表之外的所有表，但最终出现以下错误：

Cannot delete or update a parent row: a foreign key constraint fails

无法删除或更新父行：外键约束失败

Of course I could trial and error to see what those key constraints are and eventually delete all tables but I'd like to know if there is a fast way to force drop all tables (as I'll be able to re-insert those I don't want deleted).

当然，我可以反复试验以查看这些关键约束是什么，并最终删除所有表，但我想知道是否有一种快速方法可以强制删除所有表（因为我将能够重新插入那些我不想删除）。

Google aimed me at some site that suggested the following method:

谷歌将我瞄准了一些建议使用以下方法的网站：

mysql> SET foreign_key_checks = 0;
mysql> drop table ...
mysql> SET foreign_key_checks = 1;

Short answer is it didn't really do the trick since I ended up receiving the same error while I was able to delete some more tables. I've seen on Stack Overflow ways to get all foreign keys linked to a certain table but that's way too time consuming unless I script it all (which is doable in the case there is no other option)

简短的回答是它并没有真正起作用，因为我最终收到了同样的错误，而我能够删除更多的表。我已经在 Stack Overflow 上看到了将所有外键链接到某个表的方法，但这太耗时了，除非我编写所有脚本（在没有其他选择的情况下这是可行的）

Database is 4.1 so I can't use DROP DATABASE

数据库是 4.1 所以我不能使用 DROP DATABASE

Ideas?

想法？

采纳答案by Otávio Décio

Since you are not interested in keeping any data, drop the entire databaseand create a new one.

由于您对保留任何数据不感兴趣，因此删除整个数据库并创建一个新数据库。

回答by PAT

This might be useful to someone ending up here from a search. Make sure you're trying to drop a tableand not a view.

这对于从搜索结果到这里的人可能很有用。确保您尝试删除table而不是view。

SET foreign_key_checks = 0;
-- Drop tables
drop table ...
-- Drop views
drop view ...
SET foreign_key_checks = 1;

SET foreign_key_checks = 0is to set foreign key checks to off and then SET foreign_key_checks = 1is to set foreign key checks back on. While the checks are off the tables can be dropped, the checks are then turned back on to keep the integrity of the table structure.

SET foreign_key_checks = 0是将外键检查设置为关闭，然后SET foreign_key_checks = 1将外键检查设置为重新打开。当检查关闭时，可以删除表，然后重新打开检查以保持表结构的完整性。

回答by Ali Azhar

If you are using phpmyadminthen this feature is already there.

如果您使用的是phpmyadmin，那么此功能已经存在。

• Select the tables you want to drop
• From the dropdown at the bottom of tables list, select drop
• A new page will be opened having checkbox at the bottom saying "Foreign key check", uncheck it.
• Confirm the deletion by accepting "yes".

• 选择要删除的表
• 从表列表底部的下拉列表中，选择 drop
• 将打开一个新页面，底部有一个复选框，上面写着“外键检查”，取消选中它。
• 通过接受“是”确认删除。

回答by srinivas

You can use the following steps, its worked for me to drop table with constraint,solution already explained in the above comment, i just added screen shot for that -

您可以使用以下步骤，它对我有用以删除带有约束的表，解决方案已在上面的评论中进行了解释，我只是为此添加了屏幕截图 -

回答by MindStalker

Drop database exist in all versions of MySQL. But if you want to keep the table structure, here is an idea

删除数据库存在于所有版本的 MySQL 中。但是如果你想保持表结构，这里有一个想法

mysqldump --no-data --add-drop-database --add-drop-table -hHOSTNAME -uUSERNAME -p > dump.sql

mysqldump --no-data --add-drop-database --add-drop-table -hHOSTNAME -uUSERNAME -p > dump.sql

This is a program, not a mysql command

这是一个程序，而不是一个mysql命令

Then, log into mysql and

然后，登录mysql并

source dump.sql;

源转储.sql;

回答by Sanjay

Simple solution to drop all the table at once from terminal.

从终端一次删除所有表的简单解决方案。

This involved few steps inside your mysql shell (not a one step solution though), this worked me and saved my day.

这涉及您的 mysql shell 中的几个步骤（虽然不是一步解决方案），这对我有用并挽救了我的一天。

Worked for Server version: 5.6.38 MySQL Community Server (GPL)

适用于服务器版本：5.6.38 MySQL Community Server (GPL)

Steps I followed:

我遵循的步骤：

 1. generate drop query using concat and group_concat.
 2. use database
 3. turn off / disable foreign key constraint check (SET FOREIGN_KEY_CHECKS = 0;), 
 4. copy the query generated from step 1
 5. re enable foreign key constraint check (SET FOREIGN_KEY_CHECKS = 1;)
 6. run show table

MySQL shell

MySQL外壳

$ mysql -u root -p
Enter password: ****** (your mysql root password)
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>