MySQL foreign_key_checks 会影响整个数据库吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8538636/
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
Does MySQL foreign_key_checks affect the entire database?
提问by Sean Nguyen
When I execute this command in MySQL:
当我在 MySQL 中执行此命令时：
SET FOREIGN_KEY_CHECKS=0;
Does it affect the whole engine or it is only my current transaction?
它会影响整个引擎还是只是我当前的事务？
回答by Almo
It is session-based, when set the way you did in your question.
当您按照您在问题中的方式进行设置时，它是基于会话的。
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
According to this, FOREIGN_KEY_CHECKSis "Both" for scope. This means it can be set for session:
据此FOREIGN_KEY_CHECKS，范围是“两者”。这意味着它可以设置为会话：
SET FOREIGN_KEY_CHECKS=0;
or globally:
或全球：
SET GLOBAL FOREIGN_KEY_CHECKS=0;
回答by Ron Inbar
Actually, there are two foreign_key_checksvariables: a global variable and a local (per session) variable. Upon connection, the session variable is initialized to the value of the global variable.
The command SET foreign_key_checksmodifies the session variable.
To modify the global variable, use SET GLOBAL foreign_key_checksor SET @@global.foreign_key_checks.
实际上，有两个foreign_key_checks变量：一个全局变量和一个局部（每个会话）变量。连接后，会话变量被初始化为全局变量的值。
该命令SET foreign_key_checks修改会话变量。
要修改全局变量，请使用SET GLOBAL foreign_key_checks或SET @@global.foreign_key_checks。
Consult the following manual sections:
http://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
请参阅以下手册部分：
http://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html
http://dev.mysql.com/doc/refman/5.7/en/server -system-variables.html
回答by Bouke Versteegh
As explained by Ron, there are two variables, local and global. The local variable is always used, and is the same as global upon connection.
正如 Ron 所解释的，有两个变量，局部变量和全局变量。始终使用局部变量，并且在连接时与全局变量相同。
SET FOREIGN_KEY_CHECKS=0;
SET GLOBAL FOREIGN_KEY_CHECKS=0;
SHOW Variables WHERE Variable_name='foreign_key_checks'; # always shows local variable
When setting the GLOBAL variable, the local one isn't changed for any existing connections. You need to reconnect or set the local variable too.
设置 GLOBAL 变量时，不会为任何现有连接更改本地变量。您也需要重新连接或设置局部变量。
Perhaps unintuitive, MYSQL does not enforce foreign keys when FOREIGN_KEY_CHECKS are re-enabled. This makes it possible to create an inconsistent database even though foreign keys and checks are on.
也许不直观，当 FOREIGN_KEY_CHECKS 重新启用时，MYSQL 不会强制执行外键。即使启用了外键和检查，这也使得创建不一致的数据库成为可能。
If you want your foreign keys to be completely consistent, you need to add the keys while checking is on.
如果您希望您的外键完全一致，您需要在检查时添加外键。
回答by Mike Karras
# will get you the current local (session based) state.
SHOW Variables WHERE Variable_name='foreign_key_checks';
If you didn't SET GLOBAL, only your session was affected.
如果您没有设置 GLOBAL，则只会影响您的会话。
回答by Saeed cr7
I had the same error when I tried to migrate Drupal database to a new local apache server(I am using XAMPP on Windows machine). Actually I don't know the meaning of this error, but after trying steps below, I imported the database without errors. Hope this could help:
当我尝试将 Drupal 数据库迁移到新的本地 apache 服务器时，我遇到了同样的错误（我在 Windows 机器上使用 XAMPP）。其实我不知道这个错误的含义，但是在尝试了下面的步骤之后，我没有错误地导入了数据库。希望这可以帮助：
Changing php.ini at C:\xampp\php\php.ini
在 C:\xampp\php\php.ini 更改 php.ini
max_execution_time = 600
max_input_time = 600
memory_limit = 1024M
post_max_size = 1024M
Changing my.ini at C:\xampp\mysql\bin\my.ini
在 C:\xampp\mysql\bin\my.ini 更改 my.ini
max_allowed_packet = 1024M
回答by user2682955
In case of using Mysql query browser, SET FOREIGN_KEY_CHECKS=0;does not have any impact in version 1.1.20. However, it works fine on Mysql query browser 1.2.17
在使用Mysql查询浏览器的情况下，SET FOREIGN_KEY_CHECKS=0;在1.1.20版本中没有任何影响。但是，它在 Mysql 查询浏览器 1.2.17 上运行良好
