MySQL 在命令行上禁用外键检查
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15938786/
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
Disabling foreign key checks on the command line
提问by DisgruntledGoat
I have a backup script for my MySQL database, using mysqldumpwith the --taboption so it produces a .sqlfile for the structure and a .txtfile (pipe-separated) for the content.
我有一个用于 MySQL 数据库的备份脚本，mysqldump与该--tab选项一起使用，因此它.sql为结构生成一个文件，并.txt为内容生成一个文件（管道分隔）。
Some tables have foreign keys, so when I import it I'm getting the error:
有些表有外键，所以当我导入它时，我收到错误：
ERROR 1217 (23000) at line 8: Cannot delete or update a parent row: a foreign key constraint fails
第 8 行的 ERROR 1217 (23000)：无法删除或更新父行：外键约束失败
I know about using SET FOREIGN_KEY_CHECKS=0(and SET FOREIGN_KEY_CHECKS=1afterward). If I add those to each .sqlfile then the import works. But then obviously on the next mysqldumpthose get overwritten.
我知道使用SET FOREIGN_KEY_CHECKS=0（和SET FOREIGN_KEY_CHECKS=1之后）。如果我将它们添加到每个.sql文件中，则导入工作。但是很明显，接下来mysqldump这些会被覆盖。
I also tried running it as a separate command, like below but the error comes back:
我也尝试将它作为单独的命令运行，如下所示，但错误又回来了：
echo "SET FOREIGN_KEY_CHECKS=0" | mysql [user/pass/database]
[all the imports]
echo "SET FOREIGN_KEY_CHECKS=1" | mysql [user/pass/database]
Is there some other way to disable FK checks on the command line?
有没有其他方法可以在命令行上禁用 FK 检查？
回答by Wiktor
You can also use --init-commandparameter of mysqlcommand.
您也可以使用命令的--init-command参数mysql。
I.e.: mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" ...
IE： mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" ...
回答by Explosion Pills
You can do this by concatenating the string to the file inline. I'm sure there's an easier way to concatenate strings and files, but it works.
您可以通过将字符串连接到文件内联来做到这一点。我确信有一种更简单的方法来连接字符串和文件，但它有效。
cat <(echo "SET FOREIGN_KEY_CHECKS=0;") imports.sql | mysql
I don't think you need to set it back to 1 since it's just one session.
我认为您不需要将其设置回 1，因为它只是一个会话。
回答by fx991
Just another one to do the same:
只是另一个做同样的事情：
{ echo "SET FOREIGN_KEY_CHECKS=0;" ; cat imports.sql ; } | mysql
回答by deepak
Login to mysql command line:
登录mysql命令行：
mysql -u <username> -p -h <host_name or ip>Then run
mysql -u <username> -p -h <host_name or ip>然后运行
1 SET FOREIGN_KEY_CHECKS=0;
1 SET FOREIGN_KEY_CHECKS=0;
2 SOURCE /pathToFile/backup.sql;
2 SOURCE /pathToFile/backup.sql;
3 SET FOREIGN_KEY_CHECKS=1;
3 SET FOREIGN_KEY_CHECKS=1;
回答by xtrapizzasauce
Based off the comments and answers, I ended up using this for a zipped database import with both InnoDB and MyISAM:
根据评论和答案，我最终将其用于 InnoDB 和 MyISAM 的压缩数据库导入：
{ echo "SET FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0;" ; zcat dump.gz ; } | mysql
