问题描述
我非常清楚这个问题可以通过禁用安全更新模式来解决(例如，请参见此处:MySQL 错误代码:在 MySQL Workbench 中更新期间出现 1175).但是，我不希望禁用安全更新模式(并且有很多解决方案都建议这样做).
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
推荐答案
来自 https://dev.mysql.com/doc/workbench/en/workbench-faq.html#faq-workbench-delete-safe
默认情况下，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.
- 打开您的 Workbench Preferences，选择 SQL Editor 部分，然后禁用以下首选项:安全更新" - 禁止 WHERE 子句中没有键或没有 LIMIT 子句的 UPDATE 和 DELETE.
- 运行
SET SQL_SAFE_UPDATES=0;
这篇关于MySQL 错误代码:更新期间的 1175(MySQL-Workbench 与控制台)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！