使用内部查询时 mysql 错误 1175

Question

假设我有 2 个表 - item_images 和 images。

当我运行查询时

SELECT image_id FROM item_images WHERE item_id=1

我得到 image_id 值 5, 6

当我运行时

DELETE FROM images WHERE id in (5, 6);

它也可以删除这两行。

但是当我尝试将这 2 个查询链接在一起时，它失败并出现错误 1175。

DELETE FROM images WHERE id in (SELECT image_id FROM item_images WHERE item_id=1);

    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. 0.000 sec

id 字段设置为私钥，不为空。

如果 WHERE 中的 id 显然是私钥，为什么会发生这种情况？

解决此问题的唯一方法是禁用安全模式，还是有其他方法？

谢谢!

Answer 1

假设 id 列(images 表)始终大于零 (0):

mysql> SET SESSION SQL_SAFE_UPDATES := 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `item_images`, `images`;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE IF NOT EXISTS `images` (
    ->   `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `item_images` (
    ->   `item_id` BIGINT UNSIGNED NOT NULL,
    ->   `image_id` BIGINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `images`
    -> VALUES (NULL), (NULL), (NULL),
    ->        (NULL), (NULL), (NULL);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `item_images`
    ->   (`item_id`, `image_id`)
    -> VALUES (1, 5), (1, 6), (2, 1),
    ->        (2, 3), (3, 2), (4, 2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT `image_id`
    -> FROM `item_images`
    -> WHERE `item_id` = 1;
+----------+
| image_id |
+----------+
|        5 |
|        6 |
+----------+
2 rows in set (0.00 sec)

mysql> DELETE
    -> FROM `images`
    -> WHERE `id` IN (SELECT `image_id`
    ->                FROM `item_images`
    ->                WHERE `item_id` = 1);
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> DELETE
    -> FROM `images`
    -> WHERE `id` > 0 AND
    ->       `id` IN (SELECT `image_id`
    ->                FROM `item_images`
    ->                WHERE `item_id` = 1);
Query OK, 2 rows affected (0.01 sec)

参见 db-fiddle .

更新

在第一个 DELETE 中，未达到索引(键)。

mysql> SET SESSION SQL_SAFE_UPDATES := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN DELETE
    -> FROM `images`
    -> WHERE `id` IN (SELECT `image_id`
    ->                FROM `item_images`
    ->                WHERE `item_id` = 1);
+----+--------------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE             | images      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | item_images | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+--------------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN DELETE
    -> FROM `images`
    -> WHERE `id` > 0 AND
    ->       `id` IN (SELECT `image_id`
    ->                FROM `item_images`
    ->                WHERE `item_id` = 1);
+----+--------------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type        | table       | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+--------------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | DELETE             | images      | NULL       | range | PRIMARY       | PRIMARY | 8       | const |    6 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | item_images | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    6 |    16.67 | Using where |
+----+--------------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set (0.01 sec)

参见 db-fiddle .