How to solve the excessive memory problem when PHP uses mysql_query to query very large result sets

Mysql query also provides another query method, the function name is mysql_unbuffered_query. This function uses the result set immediately after finding the result, and does not cache the result set into the memory, thus avoiding exceeding the memory. situation occurs. However, the cost of using this method is that you cannot use methods such as getting the head office when querying, because this method returns results while querying. At the same time, you cannot perform other operations on the same database link when using this method. If you want to perform other operations, you must first terminate the current operation, release all result rows generated by uncached SQL queries, or re-instantiate a database. Connect and use the new link for other actions.

The following is a comparison between using cache and not using cache (there are more than 10 million rows of data in the queried table):

function selecttest()
{
try {
$pdo = new PDO("mysql:host=localhost;dbname=test", 'root', '123456');
// 不使用缓存结果集方式
// $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$sth = $pdo->prepare('select * from test');
$sth->execute();
echo '最初占用内存大小：' . memory_get_usage() . "\n";
$i = 0;
while ($result = $sth->fetch(PDO::FETCH_ASSOC)) {
$i += 1;
if ($i > 10) {
break;
}
sleep(1);
print_r($result);
echo '占用内存大小：' . memory_get_usage() . "\n";
}
} catch (Exception $e) {
echo $e->getMessage();
}
}

The method used above is to cache all result sets. When running this function, an exception will be reported. The memory error is as follows:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 204800000 bytes) in E:ProgramDevelopmentRuntimeEnvironmentxampphtdocstesttest.php on line 57

Call Stack:

0.0005 135392 1. { main}() E:ProgramDevelopmentRuntimeEnvironmentxampphtdocstesttest.php:0
0.0005 135568 2. test->selecttest() E:ProgramDevelopmentRuntimeEnvironmentxampphtdocstesttest.php:86
0.0055 142528 3. PDOStatement->execute() E:Program DevelopmentRuntimeEnvironmentxampphtdocstesttest.php:57

The memory limit is exceeded when executing $sth->execute();;

Removing the comment // $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); will use the non-cached result set. way, running this function will output the following:

Initial memory size occupied: 144808

Array
(
[id] => 1
[a] => v
[b] => w
[c] => i
)

Memory size occupied: 145544

Array
(
[id] => 2
[a] => b
[b] => l
[c] => q
)

Memory size occupied: 145544

Array
(
[id] => 3
[a] => m
[b] => p
[c] => h
)

Memory size occupied: 145536

Array
(
[id] => 4
[a] => j
[b] => i
[c] => b
)

Memory size occupied :145536

Array
(
[id] => 5
[a] => q
[b] => g
[c] => g
)

Memory size occupied: 145536

It can be seen that the memory occupied by obtaining a row of results without caching the result set is very small. This solves the problem of exceeding the memory limit.

Thank you for reading, I hope it can be helpful to you. If you want to get more related content, please pay attention to the PHP Chinese website (www.php.cn)!