Solution to the problem of memory exhaustion when querying large amounts of data in PHP

This article mainly introduces in detail the solution to the memory exhaustion problem of querying large amounts of data in PHP. It has certain reference value. Interested friends can refer to

Querying large amounts of data from the database. When the content is insufficient, a prompt will appear:

##PHP Fatal error: Allowed memory size of 268 435 456 bytes exhausted

This problem is in PHP The official website calls them buffered queries and unbuffered queries (Buffered and Unbuffered queries). PHP's default query mode is buffered mode. In other words, the query data results will be extracted into memory all at once for processing by the PHP program. This gives the PHP program additional functions, such as counting the number of rows, pointing the pointer to a certain row, etc. What's more important is that the program can repeatedly perform secondary queries and filtering operations on the data set. However, the disadvantage of this buffered query mode is that it consumes memory, that is, it trades space for speed.

In contrast, another PHP query mode is a non-buffered query. The database server will return data one by one instead of all at once. The result is that the PHP program consumes less memory, but it increases The pressure on the database server, because the database will wait for PHP to fetch the data until all the data is fetched.

Obviously, the buffered query mode is suitable for small data volume queries, while non-buffered query is suitable for large data volume queries.

Everyone knows about PHP’s buffered mode query. The following example is how to execute the non-buffered query API.

Non-buffered query method one: mysqli

<?php 
$mysqli = new mysqli("localhost", "my_user", "my_password", "world"); 
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT); 
if ($uresult) { 
  while ($row = $uresult->fetch_assoc()) { 
    echo $row[&#39;Name&#39;] . PHP_EOL; 
  } 
} 
$uresult->close(); 
?>

Non-buffered query method two: pdo_mysql

##

<?php 
$pdo = new PDO("mysql:host=localhost;dbname=world", &#39;my_user&#39;, &#39;my_pass&#39;); 
$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[&#39;Name&#39;] . PHP_EOL; 
  } 
} 
?>

Non-buffered query method three:

mysql

##

<?php 
$conn = mysql_connect("localhost", "my_user", "my_pass"); 
$db  = mysql_select_db("world"); 
$uresult = mysql_unbuffered_query("SELECT Name FROM City"); 
if ($uresult) { 
  while ($row = mysql_fetch_assoc($uresult)) { 
    echo $row[&#39;Name&#39;] . PHP_EOL; 
  } 
} 
?>

The above is the entire content of this article, I hope it will be helpful to everyone's study.

Related recommendations:

How to implement the hexadecimal color random generator function in PHP

Explanation of the method of PHP checking whether the website is down

Login page information prompt function implemented by PHP (example analysis)

The above is the detailed content of Solution to the problem of memory exhaustion when querying large amounts of data in PHP. For more information, please follow other related articles on the PHP Chinese website!