你能自动创建一个不强制外键约束的mysqldump文件吗？ [英] Can you automatically create a mysqldump file that doesn't enforce foreign key constraints?

问题描述

在我的数据库上运行一个mysqldump命令，然后尝试导入它时，它会尝试按字母顺序创建表，但即使它们可能有一个稍后在文件中引用表的外键，也会失败。在文档中没有任何内容，我已经找到了答案，如这说，以更新文件创建后包含：

  set FOREIGN_KEY_CHECKS = 0; 
 ...原始mysqldump文件内容... 
 set FOREIGN_KEY_CHECKS = 1; 
  

是否没有办法自动设置这些行或以必要的顺序导出表手动指定所有表名称，因为这可能是单调乏味，容易出错）？我可以在脚本中包装这些行，但想知道是否有一个简单的方法来确保我可以转储文件，然后导入它，而不需要手动更新。

解决方案

MySQL 5.0.51包含的mysqldump命令（根据自 4.1.1 ）确实关闭了外键检查。默认情况下，mysqldump在转储文件的顶部包含以下行：

  / *！40014 SET @OLD_FOREIGN_KEY_CHECKS = @@ FOREIGN_KEY_CHECKS，FOREIGN_KEY_CHECKS = 0 * /; 
  

/ *！40014 ... * / 语法是一个条件注释，它将在MySQL上执行4.0.14及更高版本。旧的外键检查设置将在转储文件的末尾恢复：

  / *！40014 SET FOREIGN_KEY_CHECKS = @ OLD_FOREIGN_KEY_CHECKS * /; 
  

When I run a mysqldump command on my database and then try to import it, it fails as it attempts to create the tables alphabetically, even though they may have a foreign key that references a table later in the file. There doesn't appear to be anything in the documentation and I've found answers like this that say to update the file after it's created to include:

set FOREIGN_KEY_CHECKS = 0;
...original mysqldump file contents...
set FOREIGN_KEY_CHECKS = 1;

Is there no way to automatically set those lines or export the tables in the necessary order (without having to manually specify all table names as that can be tedious and error prone)? I could wrap those lines in a script, but was wondering if there is an easy way to ensure I can dump a file and then import it without manually updating it.

解决方案

The mysqldump command included with MySQL 5.0.51 (and according to the change log versions since 4.1.1) does switch off foreign key checks. By default, mysqldump includes the following line at the top of the dump file:

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

The /*!40014 ... */ syntax is a conditional comment that will be executed on MySQL 4.0.14 and later. The old foreign key checks setting is restored at the end of the dump file:

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

这篇关于你能自动创建一个不强制外键约束的mysqldump文件吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！