Mysql buffer queries and non-buffer queries, mysql Buffer

Mysql buffer queries and non-buffer queries, mysql Buffer

Recently I encountered the following error when developing a PHP program:

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

The error message shows that the maximum allowed memory is exhausted. I was surprised at first when I encountered such a mistake, but it was not surprising in the twinkling of an eye, because the program I was developing was to useforeachA circular statement searches for data with specific characteristics in a table with 40 thousand records. That is to say, 40 thousand data records must be retrieved at a time and then the daily data must be checked one by one. It can be imagined that all 40 thousand pieces of data are loaded into the memory, and the memory is not so bad.

After all, after programming for so many years, I vaguely remember that PHP provided APIs for loading data not all at once, just like processing streaming media, query methods that are lost with retrieval and data that are not accumulated in the memory. After a simple search, we found the correct usage on the official website.

This issue is called Buffered and Unbuffered queries on the official PHP website ). The default query mode of PHP is buffer. That is to say, the query data results are all extracted to the memory for processing by the PHP program at a time. This gives the PHP program additional functions, such as calculating the number of rows and pointing the pointer to a certain row. More importantly, the program can repeatedly perform secondary query and filter operations on the dataset. But the defect of this buffer query mode is memory consumption, that is, space for speed.

The other PHP query mode is non-buffered query. The database server returns data one by one instead of all at a time. The result is that the PHP program consumes less memory, but it increases the pressure on the database server, because the database will always wait for PHP to retrieve data until all the data is obtained.

Obviously, the buffer query mode is suitable for queries with small data volumes, rather than large data volumes.

For queries in the buffer mode of PHP, we all know that the example below is how to execute the non-buffer query API.

Method 1: 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['Name'] . PHP_EOL;   }}$uresult->close();?>

 

Method 2: pdo_mysql

<?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;   }}?>

 

Method 3: 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['Name'] . PHP_EOL;   }}?>

Note: Reference http://www.webhek.com/php-buffered-and-unbuffered-queries

Manual http://php.net/manual/zh/mysqlinfo.concepts.buffering.php