如何避免无左值可更新时事务失败？（PDO嵌套集PHP/SQL）

嘿，我太懂你碰到的这个嵌套集的糟心问题了——明明在事务里执行添加子节点的操作，结果因为更新左值时没找到匹配行，直接把整个事务搞炸了，对吧？别担心，咱们有几种高效的办法能解决这个问题，既不让查询失败，也不会破坏事务。

解决方案1：先校验父节点有效性（最可靠） #

问题的核心其实是：当你指定的父节点不存在（或者ID无效）时，更新左值的UPDATE语句会影响0行，而PDO默认的异常模式会把这种情况当成错误抛出，直接触发事务回滚。所以最稳妥的方式是先确认父节点存在，再执行后续的更新和插入操作。

给你个PDO的代码示例：

$pdo->beginTransaction();
try {
    // 第一步：检查目标父节点是否存在，并获取它的rgt值
    $checkParentStmt = $pdo->prepare("SELECT rgt FROM categories WHERE id = :parent_id");
    $checkParentStmt->execute([':parent_id' => $targetParentId]);
    $parentData = $checkParentStmt->fetch(PDO::FETCH_ASSOC);

    if ($parentData) {
        // 父节点存在，执行嵌套集的左/右值更新
        $updateLeftStmt = $pdo->prepare("UPDATE categories SET lft = lft + 2 WHERE lft > :parent_rgt");
        $updateLeftStmt->execute([':parent_rgt' => $parentData['rgt']]);

        $updateRightStmt = $pdo->prepare("UPDATE categories SET rgt = rgt + 2 WHERE rgt >= :parent_rgt");
        $updateRightStmt->execute([':parent_rgt' => $parentData['rgt']]);

        // 插入新的子节点
        $insertStmt = $pdo->prepare("INSERT INTO categories (name, lft, rgt) VALUES (:name, :lft, :rgt)");
        $insertStmt->execute([
            ':name' => $newCategoryName,
            ':lft' => $parentData['rgt'],
            ':rgt' => $parentData['rgt'] + 1
        ]);
    } else {
        // 父节点不存在，直接插入为根节点（或者根据你的业务逻辑调整）
        // 先获取当前最大的rgt值，确保根节点的位置正确
        $getMaxRgtStmt = $pdo->query("SELECT IFNULL(MAX(rgt), 0) AS max_rgt FROM categories");
        $maxRgt = $getMaxRgtStmt->fetch(PDO::FETCH_ASSOC)['max_rgt'];

        $insertRootStmt = $pdo->prepare("INSERT INTO categories (name, lft, rgt) VALUES (:name, :lft, :rgt)");
        $insertRootStmt->execute([
            ':name' => $newCategoryName,
            ':lft' => $maxRgt + 1,
            ':rgt' => $maxRgt + 2
        ]);
    }

    $pdo->commit();
    echo "分类添加成功！";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "操作失败：" . $e->getMessage();
}

这种方式的好处是逻辑清晰，从根源上避免了无效的UPDATE操作，事务只会在真正的数据库错误（比如连接问题、语法错误）时回滚。

解决方案2：利用PDO的rowCount()判断更新结果 #

如果你不想提前查询父节点，也可以执行UPDATE后，用rowCount()检查实际影响的行数。如果返回0，说明没有行被更新（大概率是父节点不存在），这时候直接跳过后续的更新，执行插入逻辑即可。

注意：不同数据库对rowCount()的返回值有细微差异——MySQL会返回实际被修改的行数，而PostgreSQL如果行存在但值未变化也会返回0，所以结合你的业务场景判断即可。

示例代码：

$pdo->beginTransaction();
try {
    $parentRgt = 0; // 假设你从某个地方拿到了父节点的rgt值
    $updateLeftStmt = $pdo->prepare("UPDATE categories SET lft = lft + 2 WHERE lft > :parent_rgt");
    $updateLeftStmt->execute([':parent_rgt' => $parentRgt]);

    if ($updateLeftStmt->rowCount() > 0) {
        // 有行被更新，继续执行右值更新
        $updateRightStmt = $pdo->prepare("UPDATE categories SET rgt = rgt + 2 WHERE rgt >= :parent_rgt");
        $updateRightStmt->execute([':parent_rgt' => $parentRgt]);

        // 插入子节点
        $insertStmt = $pdo->prepare("INSERT INTO categories (name, lft, rgt) VALUES (:name, :lft, :rgt)");
        $insertStmt->execute([
            ':name' => $newCategoryName,
            ':lft' => $parentRgt,
            ':rgt' => $parentRgt + 1
        ]);
    } else {
        // 没有行被更新，直接插入根节点
        $getMaxRgtStmt = $pdo->query("SELECT IFNULL(MAX(rgt), 0) AS max_rgt FROM categories");
        $maxRgt = $getMaxRgtStmt->fetch(PDO::FETCH_ASSOC)['max_rgt'];

        $insertRootStmt = $pdo->prepare("INSERT INTO categories (name, lft, rgt) VALUES (:name, :lft, :rgt)");
        $insertRootStmt->execute([
            ':name' => $newCategoryName,
            ':lft' => $maxRgt + 1,
            ':rgt' => $maxRgt + 2
        ]);
    }

    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "操作失败：" . $e->getMessage();
}

解决方案3：用SQL条件过滤避免无效更新 #

你还可以在UPDATE语句里加入条件，确保只有父节点存在时才执行更新。比如在MySQL里可以用EXISTS子查询：

UPDATE categories 
SET lft = lft + 2 
WHERE EXISTS (SELECT 1 FROM categories WHERE id = :parent_id) 
AND lft > (SELECT rgt FROM categories WHERE id = :parent_id)

这样如果父节点不存在，UPDATE语句会直接跳过，不会影响任何行，也不会抛出异常。之后再用rowCount()判断是否需要执行后续的右值更新和插入操作。

这种方式适合不想额外加查询的场景，但要注意子查询的性能——如果你的分类表数据量很大，建议给id、lft、rgt字段加上索引。

额外注意事项 #

• 并发安全：如果有多用户同时操作嵌套集，记得用SELECT ... FOR UPDATE锁定父节点和相关行，避免并发修改导致结构混乱。
• 索引优化：给lft、rgt、id字段建立索引，能大幅提升嵌套集操作的性能，尤其是更新和查询操作。
• 错误模式：保持PDO的ERRMODE_EXCEPTION模式，这样能捕获真正的数据库错误，只是通过提前校验或者rowCount()来避免无效更新触发的异常。

内容的提问来源于stack exchange，提问作者Brett Williams