如何使PDO(pdo_pgsql)延迟获取行 [英] How to make PDO (pdo_pgsql) to lazily fetch rows

问题描述

即使我获取一行，也使用以下代码-下载了整个数据集(这需要几秒钟):

With the following code even though I fetch a single row - the whole dataset is downloaded (which takes few seconds):

$query = 'SELECT * FROM xxx WHERE id > :position ORDER BY id';
$stmt = $db->prepare($query);

$stmt->execute([
    ':position' => $position,
]);

while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
    break;
}

我试图在PDO中找到一个选项，使它可以按需(或按合理的批次)获取行，但是没有成功，至少我在PDO文档中找不到它.

I tried to find an option in PDO that would make it fetch rows on demand (or by reasonable batches), but failed to do so, at least I could not find it in the PDO documentation.

给定查询的Postgresql能够按照EXPLAIN ANALYZE服务0.28ms中的第一行.

Postgresql for the given query is able to serve the first row in 0.28ms as per the EXPLAIN ANALYZE.

我的目标是尽快开始处理行，即使更多批次的行会带来一些网络开销.

And my aim is to start processing rows as quicker as possible, even if further batches of rows will come with some network overhead.

我如何确定它会提取所有行:

How I determined that it fetches all the rows:

1. 间接:在第一行之后花费break相同的时间或遍历整个数据集
2. 我用tcpdump捕获了流量，并检查它是否提取了所有行.

1. Indirectly: it takes the same time to break after the first row or to iterate over the whole dataset
2. I captured the traffic with tcpdump and checked that it fetches all the rows.

因此，问题是:是否可以使PDO按需模式逐行(或一些合理的小批量)获取?

So, the question is: is it possible to make PDO to fetch in a row-by-row (or some reasonable small batches) on demand mode?

推荐答案

我找到了答案:

$stmt = $db->prepare($query, [\PDO::ATTR_CURSOR => \PDO::CURSOR_SCROLL]);

因此必须使用\PDO::CURSOR_SCROLL选项创建该语句

So the statement must be created with a \PDO::CURSOR_SCROLL option

参考文献:

• http://php.net/manual/en/pdo.prepare.php

这篇关于如何使PDO(pdo_pgsql)延迟获取行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！