MySQL 你能自动创建一个不强制外键约束的mysqldump文件吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2429655/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can you automatically create a mysqldump file that doesn't enforce foreign key constraints?
提问by Tai Squared
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 documentationand I've found answers like thisthat say to update the file after it's created to include:
当我在我的数据库上运行 mysqldump 命令然后尝试导入它时，它会失败，因为它尝试按字母顺序创建表，即使它们可能有一个引用文件后面的表的外键。似乎没有成为任何东西的文档，我已经找到答案，像这样是说，它的创建后，包括更新文件：
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.
有没有办法自动设置这些行或按必要的顺序导出表（无需手动指定所有表名，因为这可能很乏味且容易出错）？我可以将这些行包装在脚本中，但想知道是否有一种简单的方法可以确保我可以转储文件，然后在不手动更新的情况下导入它。
回答by Phil Ross
The mysqldumpcommand included with MySQL since version 4.1.1by default produces a script that turns off the foreign key checks. The following line is included near the top of the dump file:
mysqldumpMySQL 自4.1.1版起默认包含的命令会生成一个关闭外键检查的脚本。转储文件顶部附近包含以下行：
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
The /*!40014 ... */syntax is a conditional commentthat 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 ... */语法是有条件的评论将在MySQL版本4.0.14以后执行。旧的外键检查设置在转储文件的末尾恢复：
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
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 命令行客户端加载转储文件：
mysql -hserver -uuser -p database < dumpfile.sql
It's also worth noting that if mysqldumpis run with the --compactoption, then the commands to disable and re-enable the foreign key checks are omitted from the dump file.
还值得注意的是，如果mysqldump使用该--compact选项运行，则转储文件中将省略禁用和重新启用外键检查的命令。
回答by edtruant
Beware. For some reason mysqldump doesn't write the FOREIGN_KEY_CHECKS=0 if the --compact option is used.
谨防。出于某种原因，如果使用 --compact 选项，mysqldump 不会写入 FOREIGN_KEY_CHECKS=0。
Ciao.
再见。
回答by alds
If you're using phpMyAdmin when exporting SQL, choose Custom Export Method. Then among the checkbox options, click "Disable foreign key checks". The exported SQL statement will have the disable and enable foreign key checks at the beginning and end of the output file respectively.
如果您在导出 SQL 时使用 phpMyAdmin，请选择Custom Export Method。然后在复选框选项中，单击“禁用外键检查”。导出的 SQL 语句将分别在输出文件的开头和结尾处禁用和启用外键检查。
It's not "automatic", but you won't have to write the statements yourself for every export.
它不是“自动”的，但您不必为每次导出都自己编写语句。
回答by iRonin
This may happen if you use --compactas one of your mysqldumpcommand.
--compactincludes --skip-commentsso instead --compactone should use --skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset
如果您将其--compact用作mysqldump命令之一，则可能会发生这种情况。
--compact包括--skip-comments所以--compact应该使用--skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset
回答by freezed
Beware of your MySQL client you use, with the mysqlcommand, no problem. Dumping:
小心你用的mysql客户端，用mysql命令，没问题。倾倒：
% mysqldump -u ocp6 -pocp6 ocp6 --single-transaction --result-file=dump.sql
Restoring:
恢复：
% mysql -u ocp6 -pocp6 ocp6 < dump.sql
Everything's fine.
一切安好。
With the use of another MySQL client(mycliin my situation) to restore the dump-file:
使用另一个 MySQL 客户端（在我的情况下是mycli）来恢复转储文件：
mysql ocp6@:(none)> \. dump.sql
[…]
(1005, 'Can\'t create table `ocp6`.`composition` (errno: 150 "Foreign key constraint is incorrectly formed")')
I assume that myclido not understand conditional comments.
