PHP PDO 缓冲查询问题

2021-12-26 00:00:00 php mysql pdo

我在使用 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.

相关文章