在命令行上禁用外键检查 [英] Disabling foreign key checks on the command line

问题描述

我有一个用于MySQL数据库的备份脚本，使用 - tab 选项使用 mysqldump 一个 .sql 文件和一个 .txt 文件（管道分隔） >

有些表有外键，所以当我导入它，我得到的错误：

ERROR 1217（23000）at line 8：无法删除或更新父行：外键约束失败

使用 SET FOREIGN_KEY_CHECKS = 0 （和 SET FOREIGN_KEY_CHECKS = 1 之后）。如果我添加到每个 .sql 文件，那么导入工作。但是显然在下面 mysqldump 那些被覆盖。

我也试过作为一个单独的命令运行它，但是错误回来了：

  echoSET FOREIGN_KEY_CHECKS = 0| mysql [user / pass / database] 
 [所有导入] 
 echoSET FOREIGN_KEY_CHECKS = 1| mysql [user / pass / database] 
  

是否有其他方法在命令行中禁用FK检查？

解决方案

您可以通过将字符串连接到文件内联来完成。我确定有一个更简单的方法来连接字符串和文件，但它的工作原理。

  cat<（echoSET FOREIGN_KEY_CHECKS = 0;）imports.sql | mysql 
  

我不认为你需要将它设置为1，因为它只是一个会话。 / p>

I have a backup script for my MySQL database, using mysqldump with the --tab option so it produces a .sql file for the structure and a .txt file (pipe-separated) for the content.

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

I know about using SET FOREIGN_KEY_CHECKS=0 (and SET FOREIGN_KEY_CHECKS=1 afterward). If I add those to each .sql file then the import works. But then obviously on the next mysqldump those get overwritten.

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?

解决方案

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.

这篇关于在命令行上禁用外键检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！