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

问题描述

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

我发现生成的drop语句集很有用，并推荐这些调整：

1. 将生成的drop限制为这样的数据库：

  SELECT concat（'DROP TABLE IF EXISTS' ，table_name，';'）
 FROM information_schema.tables 
 WHERE table_schema ='MyDatabaseName'; 
  

2. 注意，根据 http://dev.mysql.com/doc/refman/5.5/en/drop-table.html ，级联下降是毫无意义的/误导性的：
   
   
   lockquote
   RESTRICT和CASCADE被允许
   

   因此，为了使drop语句能够正常工作，如果您需要：
   
   $ p $ SET FOREIGN_KEY_CHECKS = 0


   这将禁用引用完整性检查 - 因此，当您完成执行所需的卸载时，您将需要重置键检查。
   
   

     SET FOREIGN_KEY_CHECKS = 1 
     

   
   

   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';
   

   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
   

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