MySQL DROP 所有表，忽略外键 [英] MySQL DROP all tables, ignoring foreign keys

问题描述

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

解决方案

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

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

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

注意 1:这不会执行 DROP 语句，它只是为您提供它们的列表.您需要将输出剪切并粘贴到 SQL 引擎中以执行它们.

注意 2:如果您有 VIEW，则必须手动将每个 DROP TABLE `VIEW_NAME` 语句更正为 DROP VIEW `VIEW_NAME`.

1. 注意，根据 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

1. 最终的执行应该是这样的:

SET FOREIGN_KEY_CHECKS = 0;-- 分号分隔的 DROP 语句列表设置 FOREIGN_KEY_CHECKS = 1；

注意:为了更轻松地使用 SELECT 的输出，mysql -B 选项会有所帮助.

Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?

解决方案

I found the generated set of drop statements useful, and recommend these tweaks:

1. Limit the generated drops to your database like this:

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

Note 1: This does not execute the DROP statements, it just gives you a list of them. You will need to cut and paste the output into your SQL engine to execute them.

Note 2: If you have VIEWs, you'll have to correct each DROP TABLE `VIEW_NAME` statement to DROP VIEW `VIEW_NAME` manually.

2. Note, per http://dev.mysql.com/doc/refman/5.5/en/drop-table.html, dropping with cascade is pointless / misleading:

"RESTRICT and CASCADE are permitted to make porting easier. In MySQL 5.5, they do nothing."

Therefore, in order for the drop statements to work if you need:

SET FOREIGN_KEY_CHECKS = 0

This will disable referential integrity checks - so when you are done performing the drops you need, you will want to reset key checking with

SET FOREIGN_KEY_CHECKS = 1

3. The final execution should look like:

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

NB: to use output of SELECT easier, mysql -B option can help.

这篇关于MySQL DROP 所有表，忽略外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！