問題描述

我在使用 PHP 數據對象函數時遇到了一些嚴重的問題.我正在嘗試使用緩沖查詢遍歷一個相當大的結果集(~60k 行，~1gig)以避免獲取整個結果集.

I'm having some serious problems with the PHP Data Object functions. I'm trying to loop through a sizeable result set (~60k rows, ~1gig) using a buffered query to avoid fetching the whole set.

無論我做什么，腳本都只是掛在 PDO::query() 上 - 查詢似乎正在無緩沖地運行(否則結果集大小的變化為什么會修復"問題?).這是我重現問題的代碼:

No matter what I do, the script just hangs on the PDO::query() - it seems the query is running unbuffered (why else would the change in result set size 'fix' the issue?). Here is my code to reproduce the problem:

<?php
$Database = new PDO(
    'mysql:host=localhost;port=3306;dbname=mydatabase',
    'root',
    '',
    array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
    )
);

$rQuery = $Database->query('SELECT id FROM mytable');

// This is never reached because the result set is too large
echo 'Made it through.';

foreach($rQuery as $aRow) {
    print_r($aRow);
}
?>

如果我用一些合理的數字限制查詢，它就可以正常工作:

If I limit the query with some reasonable number, it works fine:

$rQuery = $Database->query('SELECT id FROM mytable LIMIT 10');

我嘗試過使用 PDO::MYSQL_ATTR_MAX_BUFFER_SIZE 并使用 PDO::prepare() 和 PDO::execute()(盡管上述查詢中沒有參數)，但都無濟于事.任何幫助將不勝感激.

I have tried playing with PDO::MYSQL_ATTR_MAX_BUFFER_SIZE and using the PDO::prepare() and PDO::execute() as well (though there are no parameters in the above query), both to no avail. Any help would be appreciated.

推薦答案

如果我理解正確的話，緩沖查詢涉及告訴 PHP 在開始處理之前您要等待整個結果集.在 PDO 之前，這是默認設置，您必須調用 mysql_unbuffered_query 如果你想立即處理結果.

If I understand this right, buffered queries involve telling PHP that you want to wait for the entire result set before you begin processing. Prior to PDO, this was the default and you had to call mysql_unbuffered_query if you wanted to deal with results immediately.

為什么在 PDO MySQL 驅動程序頁面上沒有解釋這一點，我不知道.

Why this isn't explained on the PDO MySQL driver page, I don't know.

這篇關于PHP PDO 緩沖查詢問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！