我该如何重写此MySQL查询，以便它不会引发此错误:您无法在FROM子句中指定目标表'crawlLog'进行更新?

问题描述

我正在尝试从公司表中获取ID，而该ID尚未在crawlLog表中.然后，我需要将该companyId插入crawlLog表中.

I'm trying to get an id from a companies table where the id is not yet in the crawlLog table. Then I need to insert that companyId into the crawlLog table.

我需要在一个调用中执行此操作，以使并行搜寻器在其他某个搜寻器选择了某个URL之后仍未提取相同的URL，但尚未将其插入到搜寻日志中.我不想锁定表，因为会产生其他问题.

I need to do this in one call so that parallel crawlers don't pull the same url after some other crawler has selected a url, but hasn't inserted it into the crawl log yet. I don't want to lock tables because of other problems that generates.

我从下面的两个查询中都收到此错误:

I get this error from both queries below:

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

这是我尝试做同样事情的两个查询.

Here are two queries i've tried to do the same thing.

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()
)

我也尝试过这种方法，但是会遇到相同的错误:

I've also tried this, but get the same error:

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

推荐答案

这可行，并且似乎是最简单的解决方案:

This works and seems like the simplest solution:

使用问题中的两个简单的语句，按照@Tocco在注释中的建议，为内部crawlLog表创建了一个别名，然后删除了必要的VALUES()封装.

Using the simpler of the two statements in my question, I created an alias for the inner crawlLog table as suggested by @Tocco in the comments, and then removed the necessary encapsulation in VALUES().

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

这篇关于我该如何重写此MySQL查询，以便它不会引发此错误:您无法在FROM子句中指定目标表'crawlLog'进行更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！