如何改写这个MySQL查询，以避免出现错误：“您不能在FROM子句中更新目标表'crawlLog'”?

Question

我正在尝试从公司表中获取一个id，该id尚未在crawlLog表中。然后，我需要将该companyId插入到crawlLog表中。

我需要一次性完成此操作，以便并行的爬虫不会在其他爬虫选择url后拉取相同的url，但尚未将其插入到爬行日志中。由于会导致其他问题，因此我不想锁定表。

我从下面的查询中都得到了这个错误：

You can't specify target table 'crawlLog' for update in FROM clause

这里有两个查询，我试图做同样的事情。

INSERT INTO crawlLog (companyId, timeStartCrawling)
VALUES
(
    (
        SELECT companies.id FROM companies
        LEFT OUTER JOIN crawlLog
        ON companies.id = crawlLog.companyId
        WHERE crawlLog.companyId IS NULL
        LIMIT 1
    ),
    now()
)

我也尝试过这个方法，但是出现了同样的错误：

INSERT INTO crawlLog (companyId, timeStartCrawling)
VALUES
(
    (
        SELECT id
        FROM companies
        WHERE id NOT IN
        (
            SELECT companyId
            FROM crawlLog
        )
        LIMIT 1
    ),
    now()
)

- T. Brian Jones

---

这只能通过事务和一些锁定来可靠地完成。如果您使用的是InnoDB，至少它只会是行级锁定而不是整个表格锁定。 - Marc B

你试过为内部的crawlLog设置别名吗？ - Tocco

@Tocco - 你太棒了！！！这就是解决方法。这是一个很棒的技巧。 - T. Brian Jones

嘿..把这个评论标记为优秀评论！ - Tocco

Answer 1

为什么要使用子查询？INSERT INTO ... SELECT存在的原因：

INSERT INTO crawlLog (companyId, timeStartCrawling)
SELECT companies.id, NOW()
FROM companies
LEFT OUTER JOIN crawlLog
ON companies.id = crawlLog.companyId
WHERE crawlLog.companyId IS NULL
LIMIT 1

这样做就不会抱怨在INSERT和SELECT部分都使用表格了。

- wonk0

Answer 2

您无法更新正在查询的行。有一种方法可以强制MySQL隐式使用临时表：

INSERT INTO crawlLog (companyId, timeStartCrawling)
VALUES
(
    SELECT id, when FROM
    (
    SELECT companies.id AS id, now() AS when FROM companies
    LEFT OUTER JOIN crawlLog
    ON companies.id = crawlLog.companyId
    WHERE crawlLog.companyId IS NULL
    LIMIT 1
    )
)

- André Laszlo

Answer 3

这个方法可行，看起来是最简单的解决方案：

使用我问题中较为简单的语句之一，按照@Tocco在评论中建议的方式为内部crawlLog表创建了一个别名，然后删除了VALUES()中必要的封装。

INSERT INTO crawlLog (companyId, timeStartCrawling)
SELECT id, now()
FROM companies
WHERE id NOT IN
(
    SELECT companyId
    FROM crawlLog AS crawlLogAlias
)
LIMIT 1

- T. Brian Jones

---

不确定这是否是一个问题，但在VALUES内部放置SELECT似乎很奇怪（不必要）。 - Chains

Answer 4

将选择操作放入临时表中，然后从临时表中进行插入选择。无法在同一语句中向表中插入并从中选择，因此请使用临时表和两个语句。

- antlersoft