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:
?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
$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程序设计有所帮助。
