Usage of MySQL Buffer in PHP

Usage of MySQL Buffer in PHP

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 Buffer, we will say that bufferunbuffer is not used ).

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

Mainly in terms of memory, the use of buffer will increase the client's memory pressure, when the returned data results are particularly large, it may occupy the calling client is actually a php process) relatively large process. Without using the buffer, the server is naturally more stressed by providing mysql services.

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:

 
 

  
  
<? Php
  
  
 
  
  
$ 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;
  
  
 
  
  
$ 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;
  
  
 
  
  
$ 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-> execute;
  
  
 
  
  
$ Data = array;
 
 

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.