无法将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';)时，该设置显示为开"，如果不运行foreign_key-check，就无法上传错误/投诉.

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屋！