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

问题描述

当我在我的数据库上运行 mysqldump 命令然后尝试导入它时，它会失败，因为它会尝试按字母顺序创建表，即使它们可能有一个外键引用文件中稍后的表.文档中似乎没有任何内容，而我已经找到像 this 这样的答案来更新文件创建后包含:

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.

推荐答案

mysqldump 命令自版本 4.1.1 默认会生成一个关闭的脚本外键检查.转储文件顶部附近包含以下行:

The mysqldump command included with MySQL since version 4.1.1 by default produces a script that turns off the foreign key checks. The following line is included near the top of the dump file:

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

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

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

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

请注意，条件注释由解释客户端(而不是服务器).如果您使用不支持它们的客户端加载转储文件，则不会禁用外键检查，并且您可能会遇到错误.为了获得最佳效果，我建议使用官方 mysql 命令行客户端加载转储文件:

Note that the conditional comments are interpreted by the the client (rather than the server). If you load the dump file with a client that doesn't support them, then foreign key checks will not be disabled and you might encounter errors. For best results, I'd suggest loading dump files using the official mysql command line client:

mysql -hserver -uuser -p database < dumpfile.sql

还值得注意的是，如果 mysqldump 使用 --compact 选项，然后从转储文件中省略禁用和重新启用外键检查的命令.

It's also worth noting that if mysqldump is run with the --compact option, then the commands to disable and re-enable the foreign key checks are omitted from the dump file.

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