你能否自动创建一个不强制执行外键约束的mysqldump文件？

Question

当我在数据库上运行mysqldump命令并尝试导入时，它会尝试按字母顺序创建表，即使它们可能有一个引用文件中后面的表的外键，因此失败了。 在文档中似乎没有任何东西，我找到了像这样的答案，建议在创建文件后更新文件以包含以下内容：

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

有没有办法自动设置这些行或者按照必要的顺序导出表格（而不必手动指定所有表格名称，因为这可能很繁琐且容易出错）？我可以将这些行包装在脚本中，但想知道是否有一种简单的方法来确保我可以转储文件并在不手动更新的情况下导入它。

- Tai Squared

Answer 1

自MySQL 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 */;

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

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

值得注意的是，如果使用--compact选项运行mysqldump，则禁用和重新启用外键检查的命令将从转储文件中省略。

- Phil Ross

---

我看到文件里确实有这个问题。另一个试图导入该文件的人在外键行遇到错误，认为这是由于创建顺序引起的。 - Tai Squared

这怎么回答问题了？Tai说他在文件中加入这些注释后遇到了一个错误，所以问题是如何解决的呢？ - JoeTidee

这个回答怎么能被接受呢？它完全不符合提问的问题:/ - Pragyan

我的问题是"--compact"，非常感谢！ - Allan Andrade

Answer 2

注意。 由于使用 --compact 选项，mysqldump 不会写入 FOREIGN_KEY_CHECKS=0。

Ciao.

- edtruant

---

原因是 --compact 包括了 --skip-comments，所以应该使用 --skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset 代替 --compact。 - iRonin

你实际上也可以使用 --skip-comments 来跳过注释。手册中说 --compact "启用了 --skip-add-drop-table、--skip-add-locks、--skip-comments、--skip-disable-keys 和 --skip-set-charset 选项"，但显然 --compact 还会除此之外去掉其他一些输出，例如 FOREIGN_KEY_CHECKS 语句。 - dpi

Answer 3

如果您在导出SQL时使用phpMyAdmin，请选择自定义导出方法。然后在复选框选项中，单击“禁用外键检查”。导出的SQL语句将在输出文件的开头和结尾分别加上禁用和启用外键检查。

这并不是“自动”的，但您不必为每个导出编写这些语句。

- alds

---

哇！因为提出了一个比所要求的更容易实现的替代方案而被踩了。 - alds

这是因为“更容易”这个词非常主观（例如，我认为它根本没有用），并且它实际上并没有回答问题。 - sehe

+1 绝对对于 PhpMyAdmin 用户有用。其他人可以主观地忽视它，或者极其主观地给予反对票 :) - chim

Answer 4

如果您在mysqldump命令中使用--compact，可能会出现这种情况。 --compact包括--skip-comments，因此应该使用--skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset而不是--compact。

- iRonin

Answer 5

请注意您使用的MySQL客户端，使用mysql命令时无问题。导出：

% mysqldump -u ocp6 -pocp6 ocp6 --single-transaction --result-file=dump.sql 

恢复：

% mysql -u ocp6 -pocp6 ocp6 < dump.sql

一切正常。

使用另一个MySQL客户端（在我的情况下是mycli）来恢复转储文件：

mysql ocp6@:(none)> \. dump.sql
[…]
(1005, 'Can\'t create table `ocp6`.`composition` (errno: 150 "Foreign key constraint is incorrectly formed")')

我假设 mycli 不理解 条件注释。

- freezed

Answer 6

对我有效的一种方法是使用MySQL WorkBench。

首先，我将数据库反向工程成模型。然后，在打开模型选项卡时，我使用了EXPORT -> 正向工程SQL创建脚本选项。它会弹出一个对话框，允许我选择（除其他选项外）是否导出FK约束和FK索引。

我使用的MySQL Workbench版本是8.0.29版。它应该支持旧版本，但在我尝试之前我不确定，所以如果您使用旧版本，则可能会有所不同。

- Aakash Sahai

Answer 7

使用 --single-transaction 表示在一个事务中完成所有操作。该选项无法忽略格式错误的 外键约束。 在任何事务中，任何错误都会停止继续查询执行。你的问题是你有一个指向不存在表的 外键约束。

- Abadis