如何让PDO无缓冲查询结果集响应MySQL数据更新？

我需要用PDO无缓冲连接遍历MySQL大表，执行查询：SELECT ID,X,Y FROM tbl WHERE Z IS NULL。对每行调用op(X,Y)计算Z值（Z需通过PHP调用外部Web服务计算，无法用存储过程实现），再通过独立连接执行UPDATE tbl SET Z=? WHERE X=? AND Y=?，批量更新所有同(X,Y)的行（因为同(X,Y)对应的Z值相同，批量更新效率更高）。

原本希望无缓冲查询能感知数据变化，自动跳过已处理（Z已赋值）的行，但尝试降低隔离级别、关联新表等方法都无效。

原实现代码 #

$stmt = $db_unbuf->prepare('SELECT id,x,y,z FROM tbl WHERE z IS NULL');
$stmt->execute();
while($r = $stmt->fetch()) {
    $z = op($r->x, $r->y);
    $stmt2 = $db->prepare('UPDATE tbl SET z=? WHERE x=? AND y=?');
    $stmt2->execute([$z,$x,$y]);
};

可行解决方案 #

方案1：每次更新后重新执行带LIMIT的查询 #

每次处理完一行后，重新执行带LIMIT 0,1的查询，确保每次都获取最新的未处理行：

$stmt = $db_unbuf->prepare('SELECT id,x,y,z FROM tbl WHERE z IS NULL LIMIT 0,1');
$stmt->execute();
while($r = $stmt->fetch()) {
    $z = op($r->x, $r->y);
    $stmt2 = $db->prepare('UPDATE tbl SET z=? WHERE x=? AND y=?');
    $stmt2->execute([$z,$x,$y]);
    $stmt->execute(); // 重新执行查询获取下一个未处理行
};

方案2：使用GROUP BY（或SELECT DISTINCT）去重 #

利用GROUP BY X,Y直接获取唯一的(X,Y)组合，避免重复处理同一组数据，同时更新后后续查询不会再获取到已处理的组合：

$stmt = $db_unbuf->prepare('SELECT id,x,y,z FROM tbl WHERE z IS NULL GROUP BY X,Y');
$stmt->execute();
while($r = $stmt->fetch()) {
    $z = op($r->x, $r->y);
    $stmt2 = $db->prepare('UPDATE tbl SET z=? WHERE x=? AND y=?');
    $stmt2->execute([$z,$x,$y]);
};

内容的提问来源于stack exchange，提问作者Marco Marsala