Buffered and Unbuffered queries and non-cached query examples of pdo in MySQL

MySQL's Buffered and Unbuffered queries

Today we will further talk about the advanced functions of MySQL's query cache, that is, query cache and query uncaching!

Cause: (memory overflow warning)

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

1. Official

Mainly say that the cache query is as follows If you check out all the content and put it into the memory, it will accumulate more and more; while the non-cached query will directly return one by one from the MySQL server, which means it will wait for the PHP process to get the next piece of data. (It’s enough to mainly understand this meaning. If you want to fully understand, please translate on Weibo or Baidu)

2.Buffer and unBuffer query

a ) Cache queries are generally used to obtain query data at one time and will be stored in memory;

b) Non-cache queries are returned directly from MySQL one by one and will not be stored in memory;

3.mysqli, non-cache query example of pdo

<?php##mysqli
    $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();
    
##pdo
    $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;
          }
    }
##mysql 会被抛弃的，了解下即可
    $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;
           }
    }

Summary:
Here Cached queries and non-cached queries actually cause memory overflow when operating a large amount of data. At this time, non-cached queries can be used to prevent this situation from happening, but you have to pay attention at this time. Because mysql will wait for the PHP program to obtain the data until all the data is obtained, it will consume the performance of MySQL. So how do we use them correctly? We have to specifically grasp the core, memory overflow, and MySQL performance consumption. This If you grasp 2 well, you will know what scene to use it in. If you still don’t know how to use it, you can leave a message or tweet me!

Related articles:

How MySQL 'queries' and 'questions' are measured

How to find and kill misbehaving MySQL queries

Related videos:

Han Shunping’s latest MySQL basic video tutorial in 2016

The above is the detailed content of Buffered and Unbuffered queries and non-cached query examples of pdo in MySQL. For more information, please follow other related articles on the PHP Chinese website!