MySQL错误代码:UPDATE期间为1175(MySQL-Workbench与控制台) [英] MySQL error code: 1175 during UPDATE (MySQL-Workbench vs. console)

问题描述

我很清楚可以通过禁用安全更新模式来解决此问题(例如，请参见此处:

Am very aware of that this issue can be resolved with disabling safe update mode enabled (e.g. see here: MySQL error code: 1175 during UPDATE in MySQL Workbench). However, I do not wish to disable safe update mode (and there are many many solutions that propose this).

类似地，我知道将WHERE子句设置为与所有内容都匹配的KEY-VALUE应该起作用.但是，在mysql-workbench上似乎不起作用-至少不是我希望的方式(或它在控制台上的工作方式).

Similarly, I am aware that setting the WHERE clause to KEY-value that matches everything is supposed to work. However, doesn't appear to work on mysql-workbench - at least not the way I hoped (or the way it did work on the console).

例如，以下内容在mysql-workbench上不起作用(但在控制台上):

For example, the following didn't work on mysql-workbench (but did on the console):

UPDATE FUEL_SOURCES AS FS
INNER JOIN
    FUEL_CATEGORY FC ON FC.FUEL_CATEGORY = FS.FUEL_CATEGORY 
SET 
    FS.FUEL_CATEGORY_ID = FC.ID
WHERE
    FC.ID <> 0 AND FS.ID <> 0

...如果我明确/准确地设置了ID(例如WHERE FC.ID = 20 AND FS.ID <> 10)，它将在mysql-workbench中工作.但是，这样做将涉及遍历每个密钥对组合.

...If I explicitly / exactly set the ID's (e.g. WHERE FC.ID = 20 AND FS.ID <> 10 for example) it would work in mysql-workbench. But doing this would involve iterating through every key-pair combination.

很想知道是什么原因导致了这种行为，或者我是否做错了什么可怕的事情.使用mysql-workbench 6.3

Be intereted to know what is causing this behaviour, or if I am doing something horribly wrong. Using mysql-workbench 6.3

推荐答案

来自

默认情况下，Workbench配置为不执行DELETE或UPDATE 在KEY列中不包含WHERE子句的查询.

By default, Workbench is configured to not execute DELETE or UPDATE queries that do not include a WHERE clause on a KEY column.

这种配置可防止您错误地删除或更新表，因为您正在对没有密钥的数据进行批量更新.

Such configuration prevents you from deleting or updating table mistakenly, since you are doing a batch update on data without a key.

要解决此问题，您可能已经知道以下选项.

To resolve this, as you may be already aware the following options.

1. 打开工作台首选项"，选择"SQL编辑器"部分，并禁用以下首选项: 安全更新"-禁止在WHERE子句或LIMIT子句中没有键的UPDATE和DELETE.
2. 运行SET SQL_SAFE_UPDATES=0;

这篇关于MySQL错误代码:UPDATE期间为1175(MySQL-Workbench与控制台)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！