PHP in MySQL operation buffer usage detailed _php tips

This article illustrates the usage of MySQL operation Buffer in PHP. Share to everyone for your reference. The specific analysis is as follows:

PHP has three ways to connect with MySQL, Mysql,mysqli,pdo. Regardless of the method used to connect, there is the difference between using buffer and not using buffer.

What do you call using buffer and not using buffer?

Client and MySQL server to query operations, query operations, if the amount of data obtained is relatively large, that this query results put where?

There are two places to put: The client's buffer and the server-side buffer.

The buffer we say here refers to the client buffers, and if the query results have been retrieved from the server, placed in the client's buffer, we call it using buffer. If the buffer is still stored on the server, we say no buffer (Unbuffer) is used.

What's the difference between using buffer and not using buffer?

Mainly in memory, the use of buffer will increase the memory pressure on the client, when the result of the return of the data is particularly large may occupy the calling client (actually a PHP process) relatively large process. Not using buffer naturally has more pressure on the service side (the server that provides MySQL services here).

Specific can refer to: PHP query MySQL large amount of data memory footprint analysis

How do three modes in PHP set the use of buffer?

MySQL default query is to use the buffer, and do not use the buffer to use the Mysql_unbuffer_query

Mysqli default query is not using buffer, you need to use the buffer to set the Mysqli_store_result

PDO default Quey is not using buffer, you need to use the buffer to set the Mysql_attr_use_buffered_query

Roughly the relevant code is as follows:

<?php $dbConfig = Array (' Host ' => ' 10.128.11.101 ', ' Port ' => ' 3306 ', ' user ' => ' test ', ' Pass ' =>
' Test ', ' db ' => ' Test ',);
$sql = ' select * from So_topic_app ';
---------MySQL----------//$db = mysql_connect ($dbConfig [' Host '], $dbConfig [' User '], $dbConfig [' Pass ']);
mysql_select_db ($dbConfig [' db '], $db);
Mysql_set_charset (' UTF8 ', $db);
MySQL uses buffer $res = mysql_query ($sql, $db);
$data = Array ();
while ($row = Mysql_fetch_row ($res)) {$data [] = $row;}//MySQL does not use buffer $res = Mysql_unbuffered_query ($sql, $db);
$data = Array ();
while ($row = Mysql_fetch_row ($res)) {$data [] = $row;} mysql_close ($DB); ---------mysqli----------//$db = Mysqli_connect ($dbConfig [' Host '], $dbConfig [' User '], $dbConfig [' Pass '], $
dbconfig[' DB ']);
MYSQLI does not use buffer $result = Mysqli_query ($db, $sql);
$data = Array (); while ($row = $result->fetch_array ()) {$data [] = $row;}//mysqli use buffer $result = Mysqli_query ($db, $sql, mysqli_s
Tore_result);
$data = Array (); While($row = $result->fetch_array ())
{$data [] = $row;}
Mysqli_free_result ($result);
Mysqli_close ($DB);
---------PDO----------//$dsn = "mysql:dbname={$dbConfig [' DB ']};host={$dbConfig [' Host ']}";
$pdo = new PDO ($DSN, $dbConfig [' User '], $dbConfig [' Pass ']);
PDO does not use buffer $stmt = $pdo->prepare ($sql);
$stmt->execute ();
$data = Array ();
$data = $stmt->fetchall ();
PDO uses the buffer $pdo->setattribute (pdo::mysql_attr_use_buffered_query, true);
$stmt = $pdo->prepare ($sql);
$stmt->execute ();
$data = Array (); $data = $stmt->fetchall ();

Subsequent

Of course, if the amount of data is very large, most people will consider using batches to extract and process data. So the fact that we need to focus on and use MySQL is very little in terms of using buffer or not using buffer.

I hope this article will help you with your PHP program design.