如何在删除前在mySQL中获取多表delete查询将影响的行数？

Question

我有一个相当复杂的DELETE查询，我需要将其分解成批处理，但在执行查询之前，我无法确定要删除多少行。下面是我想要统计的查询：

DELETE parts, binaries FROM parts LEFT JOIN binaries ON binaries.ID = parts.binaryID LEFT JOIN releasenfo rn ON rn.binaryID = binaries.ID WHERE binaries.procstat IN (4, 6) AND (rn.binaryid IS NULL OR (rn.binaryid IS NOT NULL AND rn.nfo IS NOT NULL))) OR binaries.dateadded < '2013-01-04 22:01:17' - INTERVAL 36 HOUR;

以下是我认为可能有效的方法，但它是无效的：

Select COUNT(*) FROM (DELETE parts, binaries FROM parts LEFT JOIN binaries ON binaries.ID = parts.binaryID LEFT JOIN releasenfo rn ON rn.binaryID = binaries.ID WHERE binaries.procstat IN (4, 6) AND (rn.binaryid IS NULL OR (rn.binaryid IS NOT NULL AND rn.nfo IS NOT NULL))) OR binaries.dateadded < '2013-01-04 22:01:17' - INTERVAL 36 HOUR) AS countme;

有人能给我指个方向吗？

为了把它放到上下文中，我想通过下面的php伪代码使用它：

$limit = 10000; // Do in batches to give user status            
$totalRows = $db->query("Count query goes here");

while($rowsLeft > 0)
{
    echo "$rowsLeft remaining to be deleted\n";
    $db->query("DELETE ...... LIMIT ".$limit);
    $rowsLeft -= $limit;
}   

Answer 1

您可以简单地尝试以下操作：

SELECT * FROM parts p 
LEFT JOIN binaries b ON b.ID = p.binaryID AND b.procstat IN (4, 6) 
LEFT JOIN releasenfo rn ON rn.binaryID = b.ID 
WHERE rn.binaryid IS NULL OR (rn.binaryid IS NOT NULL AND rn.nfo IS NOT NULL) OR 
        b.dateadded < '2013-01-04 22:01:17' - INTERVAL 36 HOUR;

DELETE p, b FROM parts p 
LEFT JOIN binaries b ON b.ID = p.binaryID AND b.procstat IN (4, 6) 
LEFT JOIN releasenfo rn ON rn.binaryID = b.ID 
WHERE rn.binaryid IS NULL OR (rn.binaryid IS NOT NULL AND rn.nfo IS NOT NULL) OR 
        b.dateadded < '2013-01-04 22:01:17' - INTERVAL 36 HOUR;