您可以自动创建一个不强制执行外键约束的 mysqldump 文件吗？
当我在数据库上运行 mysqldump 命令然后尝试导入它时，它会失败，因为它尝试按字母顺序创建表，即使它们可能具有引用文件后面的表的外键。 文档中似乎没有任何内容，我找到了类似 this 的答案，表示要更新文件创建后包括：
set FOREIGN_KEY_CHECKS = 0;
...original mysqldump file contents...
set FOREIGN_KEY_CHECKS = 1;
是否没有办法自动设置这些行或按必要的顺序导出表（无需手动指定所有表名，因为这可能很乏味且容易出错）？我可以将这些行包装在脚本中，但想知道是否有一种简单的方法来确保我可以转储文件，然后导入它而无需手动更新它。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
自版本 4.1.1 默认情况下会生成一个关闭外键检查的脚本。转储文件顶部附近包含以下行：
/*!40014 ... */语法是 条件注释 将在 MySQL 版本 4.0.14 及更高版本上执行。旧的外键检查设置在转储文件末尾恢复：请注意，条件注释为 由客户端解释（而不是服务器）。如果您使用不支持它们的客户端加载转储文件，则外键检查将不会被禁用，并且您可能会遇到错误。为了获得最佳结果，我建议使用官方 mysql 命令行客户端加载转储文件：
还值得注意的是，如果
mysqldump与--compact选项，则禁用和重新启用外键检查的命令为从转储文件中省略。The
mysqldumpcommand 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: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: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:
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.提防。
由于某种原因，如果使用 --compact 选项，mysqldump 不会写入 FOREIGN_KEY_CHECKS=0。
再见。
Beware.
For some reason mysqldump doesn't write the FOREIGN_KEY_CHECKS=0 if the --compact option is used.
Ciao.
如果您在导出 SQL 时使用 phpMyAdmin，请选择自定义导出方法。然后在复选框选项中，单击“禁用外键检查”。导出的 SQL 语句将分别在输出文件的开头和结尾处禁用和启用外键检查。
它不是“自动”的，但您不必为每次导出都自己编写语句。
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.
It's not "automatic", but you won't have to write the statements yourself for every export.
如果您使用
--compact作为mysqldump命令之一，则可能会发生这种情况。--compact包含--skip-comments因此应该使用--skip-add-drop-table 而不是--compact--skip-add-locks --skip-disable-keys --skip-set-charsetThis may happen if you use
--compactas one of yourmysqldumpcommand.--compactincludes--skip-commentsso instead--compactone should use--skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset当心你使用的MySQL客户端，使用
mysql命令，没问题。倾倒：恢复：
一切都很好。
使用另一个MySQL客户端（在我的情况下是mycli）来恢复转储文件：
我假设 mycli 不理解 条件评论。
Beware of your MySQL client you use, with the
mysqlcommand, no problem. Dumping:Restoring:
Everything's fine.
With the use of another MySQL client (mycli in my situation) to restore the dump-file:
I assume that mycli do not understand conditional comments.
使用
--single-transaction意味着在一个事务中完成所有操作。该选项无法忽略格式错误的外键约束。在任何事务中，任何错误都将停止继续执行查询。您的问题是您对不存在的表有一个
外键约束。using
--single-transactionmeans do all in one transaction. That option can not ignore malformedForeign key constraint.In any transaction any error will stop continuing query execution. Your problem is that you have a
Foreign key constraintto a table which does not exist.对我有用的一种方法是使用 MySQL WorkBench。
首先，我将数据库逆向工程化为模型。然后，在模型选项卡打开的情况下，我使用了 EXPORT ->正向工程 SQL 创建脚本选项。它会弹出一个对话框，允许我选择（除其他事项外）是否导出 FK 约束和 FK 索引。
我使用的MySQL Workbench版本是8.0.29版本。旧版本应该支持它，但在尝试之前我不确定，所以如果您使用旧版本，您的里程可能会有所不同。
One way that worked for me was to use MySQL WorkBench.
First I reversed engineered the DB into a model. Then with the model tab open, I used the EXPORT -> forward-engineer SQL Create Script option. It brings up a dialog box that allowed me to pick (among many other things) whether or not to export FK Constraints and FK indices.
The MySQL Workbench version I used is version 8.0.29. It should be supported in older versions but I am not sure until I could try, so your mileage may vary if you are using an older version.