Detailed explanation of mysql operation buffer usage in php

Detailed explanation of mysql operation buffer usage in php_PHP tutorial

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Release： 2016-07-13 10:02:10

Original

940 people have browsed it

Detailed explanation of the usage of mysql operation buffer in php

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

The example in this article describes the usage of mysql operation buffer in php. Share it with everyone for your reference. 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 that calls the 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?

The default query of mysql uses buffer. If you do not use buffer, you need to use mysql_unbuffer_query

The default query of mysqli does not use buffer. To use buffer, you need to set MYSQLI_STORE_RESULT

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

The relevant codes are roughly as follows:

$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使用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();

后续

当然如果数据量非常大的话，大部分人还是会考虑使用分批次来提取和处理数据。所以实际上需要我们关注和使用mysql是使用buffer还是不使用buffer的场景非常少。

希望本文所述对大家的php程序设计有所帮助。