How to solve PHP using mysql_query query super-large result set hyper-memory problem _php instance

Using mysql_query to query for oversized result sets can cause fatal errors that exceed the memory limit, because Mysql_query uses a way to query all the results and then cache all the result sets in memory.

MySQL query also provides another way to query, the function named Mysql_unbuffered_query, this function is to detect the results immediately after the operation of the result set, and will not cache the result set into memory, so as to avoid the memory beyond the occurrence. But the price of using this method is to use a method such as getting a row when you cannot query, because this method returns the result on the query side. While using this method, you cannot perform other actions on the same database link, you must terminate the current operation when you want to perform other actions, release the result rows from all the SQL queries that are not cached, or instantiate a database connection and use the new link for additional operations.

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

function Selecttest ()
{
try {
$pdo = new PDO ("Mysql:host=localhost;dbname=test", ' Root ', ' 123456 ');
Do not use cached result set mode
//$pdo->setattribute (Pdo::mysql_attr_use_buffered_query, false);
$sth = $pdo->prepare (' SELECT * from test ');
$sth->execute ();
Echo ' initially occupies memory size: '. Memory_get_usage (). "\ n";
$i = 0;
while ($result = $sth->fetch (PDO::FETCH_ASSOC)) {
$i = 1;
if ($i >) {break
;
}
Sleep (1);
Print_r ($result);
Echo ' takes up memory size: '. Memory_get_usage (). "\ n";
}
} catch (Exception $e) {
echo $e->getmessage ();
}
}

The above is the way to cache all result sets, and when you run the function, you will report the error of the hyper-memory, as follows:

Fatal error:allowed memory size of 134217728 bytes exhausted (tried to allocate 204800000 bytes) in E:\ProgramDevelopment \runtimeenvironment\xampp\htdocs\test\test.php on line 57

Call Stack:

0.0005 135392 1. {main} () e:\programdevelopment\runtimeenvironment\xampp\htdocs\test\test.php:0
0.0005 135568 2. Test->selecttest () e:\programdevelopment\runtimeenvironment\xampp\htdocs\test\test.php:86
0.0055 142528 3. Pdostatement->execute () e:\programdevelopment\runtimeenvironment\xampp\htdocs\test\test.php:57

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

->setattribute//$pdo (Pdo::mysql_attr_use_buffered_query, false), when the comment for this line is removed and the result set is not cached, running the function will output the following:

Initial Memory Size: 144808

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

Memory Size: 145544

Array
(
[id] => 2
[a] => b
[b] => L
[c] => Q
)

Memory Size: 145544

Array
(
[id] => 3
[a] => m
[b] => P
[c] => H
)

Memory Size: 145536

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

Memory Size: 145536

Array
(
[id] => 5
[a] => q
[b] => G
[C] => G
)

Memory Size: 145536

As you can see, there is very little memory used to get a row of results without caching the result set. This ends the problem of exceeding the memory limit.