MySQL 错误代码:更新期间的 1175(MySQL-Workbench 与控制台)

2022-01-17 00:00:00 sql-update mysql mysql-workbench

我非常清楚这个问题可以通过禁用安全更新模式来解决(例如，请参见此处: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.

  1. 打开您的 Workbench Preferences，选择 SQL Editor 部分，然后禁用以下首选项:安全更新" - 禁止 WHERE 子句中没有键或没有 LIMIT 子句的 UPDATE 和 DELETE.
  2. 运行 SET SQL_SAFE_UPDATES=0;

相关文章