MySQL buffered queries and non-buffered queries

The following error has been encountered in developing a PHP program recently:

PHP Fatal error:allowed Memory size of 268 435 456 bytes Exhausted

The error message shows that the maximum allowable memory has been exhausted. I was surprised at the first time, but in a blink of an eye, it is not surprising, because I am developing this program is to use a foreach circular statement in a table with 40,000 records to search for a specific feature of the data, that is, to take 40,000 of data at once, and then check each day data. It can be imagined that 40,000 of all the data loaded into memory, the memory is not explosive only strange.

After all, after all these years of programming, I vaguely remember that PHP provides an API that does not load all the data at once, and it is a query method that, as with streaming media, is dropped with random, data does not accumulate in memory. After a simple search, sure enough, the correct usage is found on the official website.

This issue is called buffer queries and non-buffered queries (Buffered and unbuffered queries) on the official PHP website. PHP's query default mode is buffer mode. In other words, the results of the query data are extracted to the memory for the PHP program processing. This gives the PHP program extra functionality, such as calculating the number of rows, pointing a pointer to a row, and so on. What's more, the program can perform two queries and filters on the data set repeatedly. But the flaw of this buffer query mode is to consume memory, that is, to use space for speed.

In contrast, the other PHP query mode is a non-buffered query, the database server will return a single piece of data, rather than return all at once, the result is a PHP program consumes less memory, but increases the pressure on the database server, because the database will wait for PHP to fetch data, Until the data is all taken out.

It is clear that the buffered query pattern is suitable for small data volume queries, and non-buffered queries are adaptable to large data volume queries.

For PHP's buffered mode queries, we all know that the example below is how to execute the non-buffered query API.

Non-buffered Query method one: mysqli

<?PHP$mysqli=NewMysqli ("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 ();?>

Non-Buffered Query method two: Pdo_mysql

<?PHP$pdo=NewPDO ("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; }}?>

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[' Name '].Php_eol; }}?>

Note: introduction of Http://www.webhek.com/php-buffered-and-unbuffered-queries

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

MySQL buffered queries and non-buffered queries