PDO失败，记录太多，缓冲查询

Question

这个脚本昨天运行得很好，但是今天，由于表中有大约15万条记录是我最初选择的，所以它失败了，说我是从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";


}

Answer 1

当您使用未缓冲的查询时，这意味着您的结果集将从MySQL服务器中流进来。因此，运行(未缓冲的)查询的连接很忙，直到读取查询的最后一行为止。在您的例子中，连接是$MysqlConn。

(缓冲的查询会将整个结果集放入php程序的RAM中，并释放连接。当整个结果集不适合RAM时，使用非缓冲查询)。

当您完成非缓冲查询时，应该显式地关闭它们。所以 call。就像这样。

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

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