我有最奇怪的PHP PDO问题,我希望你们能为我解决这个问题.
如果我将$checkLimit设置为50000,则查询工作正常.但是,如果我将其设置为高于50k的任何值,它不会返回任何结果 – 并且它也不会抛出任何错误消息(我已经使用$db-> setAttribute(PDO :: ATTR_ERRMODE,PDO :: ERRMODE_WARNING).
$sql = "
SELECT d_domain_name AS domainName,d_domain_id AS domainID
FROM domains
ORDER BY d_domain_name_length ASC,d_domain_name ASC
LIMIT :checkLimit
";
$stmt = $db->prepare($sql);
$stmt->bindValue(':checkLimit',intval($checkLimit),PDO::PARAM_INT);
$stmt->execute();
$results = $stmt->fetchAll();
foreach ($results as $result) {
// 50k moments of magic
}
如果我在PHP之外运行查询,它可以使用任何限制(甚至500k,大约需要3分钟).
我已经尝试将$results = $stmt-> fetchAll()更改为while($result = $stmt-> fetch()){}以尝试保存内存,但遗憾的是,这没有做任何事情.
谁能告诉我这里我做错了什么？我错过了什么？为什么我不能超过50k？
PDO默认使用“缓冲查询”.
This means that query results are immediately transferred from the
MysqL Server to PHP in is then kept in the memory of the PHP process.
…. The downside of the buffered mode is that larger result
sets might require quite a lot memory. ….Following these characteristics buffered queries should be used in
cases where you expect only a limited result set or need to know the
amount of returned rows before reading all rows. Unbuffered mode
should be used when you expect larger results.
50k是一个很大的结果集.您是否可以尝试让pdo使用无缓冲模式并一次获取一行？这是从引用中复制的示例.第二行设置无缓冲模式.
PHP
$pdo = new PDO("MysqL:host=localhost;dbname=world",'my_user','my_pass');
$pdo->setAttribute(PDO::MysqL_ATTR_USE_BUFFERED_QUERY,false);
$uresult = $pdo->query("SELECT Name FROM City");
if ($uresult) {
while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
echo $row['Name'] . PHP_EOL;
}
}
?>