PDO 因记录过多、缓冲查询而失败

这个脚本昨天运行良好，但是今天，由于我最初选择的表中现在有大约 150,000 条记录，所以它失败了，说我正在从 null() 获取。据我所知，这是因为我的记录太多了。

因此，我最终通过向初始查询（1000）添加限制和这一行来纠正它：

$MysqlConn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

在我运行脚本的前 15 次左右，这有效，但现在失败了，并且没有进行任何插入。我收到一个错误，因为 buffered_query 为 false，表示它可以在另一个查询正在进行时运行缓冲查询。

我以前从未使用过这个，更不用说遇到这个错误了。我确信我的代码可以为此进行优化（在使用 PDO 方面我也很陌生）。

也许有人可以提供一些见解：

$MysqlConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$MysqlConn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);



/*Delete records that were made placements more than 5 days ago*/
$deleteOld = '
        DELETE 
        FROM Order_status 
        WHERE date_updated < current_date() - INTERVAL 5 DAY';
try{
$delete = $MysqlConn->prepare($deleteOld);
$result = $delete->execute();
$count = $delete->rowcount();
echo "Records Deleted: " . $count . "\n";
}
catch(PDOException $ex)
{
    echo "QUERY FAILED!: " .$ex->getMessage();
}

/*Placement process for orders already marked as Shipped*/
//PDO statement to select from order_status
$ordStatSql = 'SELECT order_id, order_status, is_placement, date_updated 
                FROM order_status
                WHERE order_status = "S"
                AND date_updated IS NULL
                order by order_id desc
                LIMIT 1000';
try{
$ordStat = $MysqlConn->prepare($ordStatSql);
$result = $ordStat->execute();
}
catch(PDOException $ex)
{
    echo "QUERY FAILED!: " .$ex->getMessage();
}

$order_ids = [];
while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) {
    $order_ids[] = $row['order_id'];
}

if (count($order_ids) > 0) {

    $placeholders = implode(',', array_fill(0, count($order_ids), '?'));
    $detailStatCheck = "
        SELECT 
             invnoc as INVOICE,
             fstatc as STATUS,
             cstnoc AS DEALER,
             framec AS FRAME,
             covr1c AS COVER,
             colr1c AS COLOR ,
             extd2d AS SHIPDATE,
             orqtyc AS QUANTITY
        FROM GPORPCFL
        WHERE invnoc IN ($placeholders)
    ";

    try {
        $detailCheck = $DB2conn->prepare($detailStatCheck);
        $detailRslt = $detailCheck->execute($order_ids);
        $count2 = $detailCheck->fetch();
        print_r($order_ids);
        print_r($count2);
    } catch(PDOException $ex) {
        echo "QUERY FAILED!: " .$ex->getMessage();
    }

    //Create prepared INSERT statement
    $insertPlacement = "
        INSERT ignore INTO placements_new (sku_id, group_id, dealer_id, start_date, expire_date, locations, order_num)
        SELECT 
            id, 
            sku_group_id, 
            :DEALER, 
            DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 7 DAY) as start_date,
            DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY) as expire_date, 
            :QUANTITY,
            :INVOICE  
        FROM skus s  
        WHERE  s.frame=:FRAME AND s.cover1=:COVER AND s.color1=:COLOR
    ";

    //create update statement for necessary constraints
    $updatePlacement = "
        UPDATE placements_new 
        SET expire_date = DATE_ADD(DATE_FORMAT(CONVERT(current_date(), CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY)
    ";

    //perpare query to check for existing records that are expired
    $expiredCheck = "
        SELECT 
            sku_id,
            dealer_id,
            expire_date
        FROM placements_new p
            INNER JOIN skus s
                ON p.sku_id = s.id
        WHERE p.dealer_id = :DEALER
            AND   s.frame = :FRAME
            AND   s.cover1 = :COVER
            AND   s.color1 = :COLOR
            AND   p.order_num = :INVOICE
            AND   p.expire_date <= current_date()
    ";

    //perpare query to check for existing records that are expired
    $validCheck = "
        SELECT 
            sku_id,
            dealer_id,
            expire_date
        FROM placements_new p
            INNER JOIN skus s
                ON p.sku_id = s.id
        WHERE p.dealer_id = :DEALER
            AND   s.frame = :FRAME
            AND   s.cover1 = :COVER
            AND   s.color1 = :COLOR
            AND   p.order_num = :INVOICE
            AND   p.expire_date > current_date()
    ";

    $updateShipped = '
        UPDATE order_status S
        INNER JOIN placements_new N 
        ON S.order_id = N.order_num
        set S.date_updated = current_date();
    ';


    while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)) {

        $values = [
            ":DEALER" => $row2["DEALER"],
            ":SHIPDATE" => $row2["SHIPDATE"],
            ":QUANTITY" => $row2["QUANTITY"],
            ":INVOICE" => $row2["INVOICE"],
            ":FRAME" => $row2["FRAME"],
            ":COVER" => $row2["COVER"],
            ":COLOR" => $row2["COLOR"],
        ];

        $values2 = [
            ":DEALER" => $row2["DEALER"],
            ":FRAME" => $row2["FRAME"],
            ":COVER" => $row2["COVER"],
            ":COLOR" => $row2["COLOR"],
            ":INVOICE" => $row2["INVOICE"],

        ];

        try{
            //Array will contain records that are expired
            $checkExisting = $MysqlConn->prepare($expiredCheck);
            $existingRslt = $checkExisting->execute($values2);
            $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC);

            //Array will contain records that are valid
            $checkExistingValid = $MysqlConn->prepare($validCheck);
            $existingVldRslt = $checkExistingValid->execute($values2);
            $count4 = $checkExistingValid->fetch(PDO::FETCH_ASSOC);

            //print_r($count3);

        }catch(PDOException $ex){
                echo "QUERY FAILED!!!: " . $ex->getMessage();
        }



            // IF records do not exist, or records exist and today is after expiration date
            if(empty($count3) && empty($count4)){
                for($i=0; $i<$row2["QUANTITY"]; $i++) {  
                    try{
                        $insert = $MysqlConn->prepare($insertPlacement);
                        $insertRslt = $insert->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }

                }
            }elseif(!empty($count3)){
                for($i=0; $i<$row2['QUANTITY']; $i++){
                    try{
                        $insert = $MysqlConn->prepare($insertPlacement);
                        $insertRslt = $insert->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }
                }
            }elseif(!empty($count4)){
                for($i=0; $i<$row2['QUANTITY']; $i++){
                    try{
                        $update = $MysqlConn->prepare($updatePlacement);
                        $updateRslt = $update->execute($values);
                    }catch(PDOException $ex){
                        echo "QUERY FAILED!!!: " . $ex->getMessage();
                    }
                }
            }else{
                die("No action taken");
            }
         }


                try{
                $updateStatus = $MysqlConn->prepare($updateShipped);
                $statUpdateRslt = $updateStatus->execute();
                $count = $updateStatus->rowcount();
                }
                catch(PDOException $ex)
                {
                    echo "QUERY FAILED!: " .$ex->getMessage();
                }
            echo "Records Updated: " . $count . "\n";


}

当您使用无缓冲查询时，这意味着您的结果集是从 MySQL 服务器流入的。因此，运行（无缓冲）查询的连接一直处于繁忙状态，直到您读取查询的最后一行。在你的情况下，连接是$MysqlConn.

（缓冲查询将整个结果集放入 PHP 程序的 RAM 中并释放连接。当整个结果集不适合 RAM 时，可以使用非缓冲查询）。

当您完成无缓冲查询时，应显式关闭它们。所以add a closeCursor() call http://php.net/manual/en/pdostatement.closecursor.php。像这样。

while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) {
    $order_ids[] = $row['order_id'];
}
$ordStat->closeCursor();

关闭缓冲查询也没有什么坏处。这是一个好习惯。