How to operate buffer with mysql in php

This article mainly introduces the usage of mysql buffer operation in php, and analyzes the techniques of mysql buffer operation in detail in the form of examples. It has certain reference value. Friends in need can refer to the examples of this article.

Describes the usage of mysql operation buffer in php. The specific analysis is as follows:

There are three ways to connect php to mysql, mysql, mysqli, and pdo. No matter which method is used to connect, there is a difference between using a buffer and not using a buffer.

What does it mean to use buffer and not to use buffer?

The client performs query operations with the mysql server. If the amount of data obtained during the query operation is relatively large, where should the query results be placed?

There are two places to put it: the client's buffer and the server's buffer.

The buffer we are talking about here refers to the client's buffer. If the query results have been obtained from the server and placed in the client's buffer, we call it using the buffer. If it is still stored in the buffer on the server side, we say that the buffer (unbuffer) is not used.

What is the difference between using buffer and not using buffer?

Mainly in terms of memory, using buffer will increase the memory pressure of the client. When the returned data result is particularly large, it may occupy a relatively large process of the calling client (actually a PHP process). Not using buffer will naturally put more pressure on the server (here we are talking about the server that provides mysql service).

For details, please refer to: Memory usage analysis of PHP querying MySQL large amounts of data

How to set whether to use buffer in the three modes in PHP?

Mysql's default query uses buffer. If you do not use buffer, you need to use mysql_unbuffer_query

Mysql's default query does not use buffer. To use buffer, you need to set MYSQLI_STORE_RESULT

The default quey of pdo does not use buffer. To use buffer, you need to set MYSQL_ATTR_USE_BUFFERED_QUERY

The relevant code is as follows:

 '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使用buffer
$res = mysql_query($sql, $db);
$data = array();
while($row = mysql_fetch_row($res)) {
  $data[] = $row;
}
// mysql不使用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不使用buffer
$result = mysqli_query($db, $sql);
$data = array();
while($row = $result->fetch_array()) {
  $data[] = $row;
}
// mysqli使用buffer
$result = mysqli_query($db, $sql, MYSQLI_STORE_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不使用buffer
$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = array();
$data = $stmt->fetchAll();
// pdo使用buffer
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = array();
$data = $stmt->fetchAll();

Follow-up

Of course, if the amount of data is very large, most people will still consider using batches to extract and process data. So there are very few scenarios that actually require us to pay attention to and use mysql whether to use buffer or not.

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

Related recommendations:

php processing transaction submission and rollback

##php common processing methods for file downloads

PHP encryption and decryption function generates encryption and decryption string

The above is the detailed content of How to operate buffer with mysql in php. For more information, please follow other related articles on the PHP Chinese website!