无法将foreign_key_checks 设置为0/关闭 [英] Cannot set foreign_key_checks to 0 / off

问题描述

我有一个小数据库，我只是向其中添加了一些表并设置了外键约束...

I have a small db that I just added some tables to and set up foreign key constraints...

现在我想上传一些数据并阅读了使用以下内容暂时关闭检查...

Now I want to upload some data and have read about using the following to temporarily turn off the check...

SET FOREIGN_KEY_CHECKS = 0;

我正在从 SQL 窗口运行此查询...我收到一条成功"消息，但是当我检查设置时(通过 SHOW Variables WHERE Variable_name='foreign_key_checks';)设置显示为 ON，如果不遇到外键检查错误/投诉，我就无法上传.

I am running this query from the SQL window...I get a 'success' message but when I check the setting (via SHOW Variables WHERE Variable_name='foreign_key_checks';) the setting shows as ON and I cannot upload without running into the foreign_key-check error/complaint.

我运行查询后的消息:

MySQL 返回一个空的结果集(即零行).(查询已0.0001 秒.)

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0001 seconds.)

我尝试使用全局版本并收到一条消息说我的用户没有必要的权限...

I tried to use the global version and got a message saying my user does not have the necessary privileges...

我什至不知道我通过哪个用户访问数据库 - 我在登录到我的云托管专用服务器后使用 phpMyAdmin...不是通过数据库用户.

I don't even know what user I'm access the DB through - I'm using phpMyAdmin after logging into my cloud hosted dedicated server...not through DB user.

我确实在 phpMyAdmin 主页的底部看到了以下内容:

I do see the following at the bottom of my phpMyAdmin home page:

您的 PHP MySQL 库版本 5.1.73 与您的 MySQL 服务器版本 5.6.36 不同.这可能会导致不可预测的行为.

Your PHP MySQL library version 5.1.73 differs from your MySQL server version 5.6.36. This may cause unpredictable behavior.

感谢任何帮助.

推荐答案

更改像 foreign_key_checks 这样的会话变量仅在会话期间持续.打开新连接后，该选项默认为全局值.

Changing a session variable like foreign_key_checks last only for the duration of the session. Once you open a new connection, the option defaults to the global value.

PhpMyAdmin 像每个 PHP 应用程序一样，为每个请求打开一个新连接.因此，只要您更改 foreign_key_checks，它就会关闭您更改该选项的会话，您的更改就结束了.

PhpMyAdmin, like every PHP application, opens a new connection for every request. So as soon as you change foreign_key_checks, it closes the session for which you changed that option, and your change is over.

如果您想更改此选项，您必须将更改作为导入脚本的一部分来执行，以便在处理导入的连接开始时进行处理.

If you want to change this option, you'll have to execute the change as part of the import script, so it gets handled at the beginning of the connection that processes the import.

另一种可能性是使用 SET GLOBAL ... 更改它，但这将影响所有会话，而不仅仅是您用于导入的会话.此外，您需要 SUPER 权限来更改全局选项，而您似乎没有该权限.

Another possibility is to change it with SET GLOBAL ..., but this will affect all sessions, not just the one you use for your import. Also, you need the SUPER privilege to change global options, and you seem not to have that privilege.

另一种选择是在导入阶段取消选中启用外键检查"...

Another option is to uncheck "Enable foreign key checks" at the Import stage...

这篇关于无法将foreign_key_checks 设置为0/关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！