问题描述
有没有一种简单的方法可以从 MySQL 数据库中删除所有表，而忽略其中可能存在的任何外键约束?
我发现生成的 drop 语句集很有用，并推荐以下调整:
- 将生成的 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`.
- 注意，根据 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;-- 分号分隔的 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:
- 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.
- 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
- 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 所有表，忽略外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！