"Can' t 背后的 MySQL 基本原理在 FROM 子句中指定更新目标表”

Question

在 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 试图建立的关于 ACID-ness 的一些保证有关。我担心如果解决这个限制，我可能会破坏我的数据或在边缘情况下产生有趣的竞争条件。

不幸的是，the MySQL manual关于此错误的基本原理不是很明确:

Currently, you cannot update a table and select from the same table in a subquery.

所以，我的问题是:

为什么 MySQL 不允许这种非常常见的习惯用法，其中 UPDATE 或 DELETE 语句中的表不得在任何谓词中引用？

Answer 1

解决方法是 suggested in the documentation ，其中解释说它有效，因为数据是 materialized进入临时表，作为优化器“技巧”以获得更好的性能。由于它已记录在案，我认为使用它没有任何注意事项。

文档中解决方法的“最小”示例是:

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

我也找不到解释为什么来自子查询的简单 UPDATE 在 MySQL 中不起作用的原因，但是这种具体化解决方法让我认为它与解决锁定冲突有关:您无法获得独占锁一行 UPDATE 因为它已经有一个用于 SELECT 的共享锁。当 SELECT 来自第二个“物化”临时表时，没有冲突。

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

也许它给出的错误消息是 MyISAM 实现的剩余故障保护，因为它只支持全表锁，但我不知道为什么它应该适用于其他引擎。