&qot；背后的MySQL原理&不能在FROM子句&qot；中指定用于更新的目标表 [英] MySQL rationale behind "Can't specify target table for update in FROM clause"

问题描述

在MySQL中，如果我在同一个表上执行DELETE或UPDATE，则不能在谓词中重用该表。例如，这是不可能的：

DELETE FROM story_category WHERE category_id NOT IN (
--          ^^^^^^^^^^^^^^ deleting from this table...

SELECT DISTINCT category.id FROM category 
       INNER JOIN story_category ON category_id=category.id);
--                ^^^^^^^^^^^^^^ ... prevents using the same table in predicates

以上示例取自this Stack Overflow question，其中接受的答案和许多其他答案指出，您可以通过将子查询嵌套到另一个子查询中来欺骗MySQL，使其无法识别表重用：

DELETE FROM story_category
--          ^^^^^^^^^^^^^^
WHERE category_id NOT IN (
    SELECT cid FROM (
        SELECT DISTINCT category.id AS cid FROM category 
        INNER JOIN story_category ON category_id=category.id
--                 ^^^^^^^^^^^^^^ apparently no longer considered the same table
    ) AS c
)

现在，此解决方法及其可能性引发了两个大问题：

• 错误检查显然没有很好地实现。一个简单的查询转换，MySQL就不再识别它试图禁止的情况(希望是)一个很好的理由。
• 因为可能有一个很好的原因，可能不是解决此限制的好主意。

我怀疑阻止在谓词中进行此类访问的原因与MySQL试图建立的关于酸度的某些保证有关。我担心如果绕过此限制，我可能会损坏我的数据或在边缘情况下生成有趣的争用条件。

遗憾的是，the MySQL manual对于此错误的原因不是很清楚：

当前，您不能更新表并从子查询中的同一个表中进行选择。

所以，我的问题是：

MySQL不允许UPDATE或DELETE语句中的表不能在任何谓词中引用这一非常常见的习惯用法的原因是什么？

推荐答案

解决方法是suggested in the documentation，其中解释说它的工作原理是将数据materialized放到临时表中，作为优化器的"诀窍"以获得更好的性能。由于它已记录在案，因此我认为使用它没有任何注意事项。

文档中的解决方法的"最小"示例是：

UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS dt ...);

我也无法解释为什么来自子查询的简单更新在MySQL中不起作用，但是这种物化变通方法让我认为它与解决锁定冲突有关：您不能获得行更新的独占锁，因为它已经有一个SELECT共享锁。当SELECT来自第二个"物化"临时表时，没有冲突。

也就是说，我承认这没有多大意义，因为InnoDB文档详细说明了它支持元组多版本控制和所有四个锁隔离级别，因此应该支持PostgreSQL、Oracle和其他产品中的这种操作。

它发出的错误消息可能是MyISAM实现的剩余故障保护，因为它只支持完全表锁，但我不知道为什么要为其他引擎设置它。

这篇关于&qot；背后的MySQL原理&不能在FROM子句&qot；中指定用于更新的目标表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！