How to use mysql buffer in php _ PHP Tutorial

Detailed description of buffer usage for mysql operations in php. Mysql buffer operations in php this article mainly introduces the usage of mysql buffer operations in php, and analyzes in detail the mysql buffer operation skills in the form of instances, for more information, see mysql buffer operations in php.

This article mainly introduces the usage of mysql buffer operations in php, analyzes the mysql buffer operation skills in details in the form of instances, and has some reference value. For more information, see

This example describes the usage of mysql buffer operations in php. Share it with you for your reference. The specific analysis is as follows:

There are three connection methods for php and mysql: mysql, mysqli, and pdo. No matter which method is used for connection, there is a difference between using buffer and not using buffer.

What is the use of buffer and the use of no buffer?

The client performs a query operation with the mysql server. if the data volume obtained during the query operation is large, where is the query result?

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

Buffer here refers to the client buffer. if the query result has been obtained from the server and is placed in the client buffer, we call it buffer. If it is still stored in the server's buffer, we will say that no buffer (unbuffer) is used ).

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

In terms of memory, using buffer increases the memory pressure on the client. when the returned data results are very large, it may occupy a large process that calls the client (actually a php process. Without using the buffer, the pressure on the server (the server that provides the mysql service) is greater.

For details, refer to: Analysis on memory usage of PHP to query a large amount of MySQL data

In php, how do I set whether to use buffer in three modes?

Mysql uses buffer for the default query, instead of using buffer, mysql_unbuffer_query is required.

The default query of mysqli 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 code is 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' => '123 ', '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; } // Use buffer for mysqli $ 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 does not use buffer $ Stmt = $ pdo-> prepare ($ SQL ); $ Stmt-> execute (); $ Data = array (); $ Data = $ stmt-> fetchAll (); // Use buffer for pdo $ 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 data size is very large, most people will still consider using batch data extraction and processing. Therefore, we need to pay attention to and use mysql in either buffer or no buffer scenarios.

I hope this article will help you with php programming.

This article mainly introduces the usage of mysql buffer operations in php, and analyzes the mysql buffer operation skills in details in the form of instances. it has some reference...