MySQL - 为什么安全更新模式会阻止此更新命令？

Question

我有一个 actor 表，如下所示:

| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
| 1        | Jack       | Nicholson | 2019-06-02 00:00:00 |

列actor_id是一个自动递增的主键。

当我尝试像这样更新表格时:

UPDATE actor
SET last_name = 'foo'
WHERE last_update > '2019-06-02 00:00:00';

我被 MySQL 的安全更新模式阻止并出现以下错误:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

事实上，last_update 列不是 KEY 列，因此基于 this SO answer我想出了以下解决方法:

CREATE TEMPORARY TABLE IF NOT EXISTS ids AS (SELECT actor_id FROM actor WHERE last_update > '2019-06-02 00:00:00');

UPDATE actor
SET last_name = 'foo'
WHERE actor_id IN (SELECT actor_id FROM ids);

但我再次被 1175 错误阻止。

为什么安全更新模式会阻止我在这里？我可以在不禁用安全更新模式的情况下解决这个问题吗？

Answer 1

您可以通过将该列设置为 KEY 列来解决此错误。换句话说，在列上创建索引(也称为键)。

mysql> set sql_safe_updates=ON;

mysql> UPDATE actor SET last_name = 'foo' WHERE last_update > '2019-06-02 00:00:00';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

mysql> alter table actor add key (last_update);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE actor SET last_name = 'foo' WHERE last_update > '2019-06-02 00:00:00';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

该错误的目的是防止您在非索引列上有条件时无意中锁定表中的每一行。

锁定的工作方式是，它锁定查询为测试条件而检查的所有行，而不仅仅是满足条件的所有行。如果您运行一个带有测试未索引查询的条件的查询，则它必须检查表中的每一行，这可能会锁定比您预期锁定的更多的行。