PHP + MySQL buffer query and unbuffered query http://php.net/manual/zh/mysqlinfo.concepts.buffering.php
Http://php.net/manual/zh/mysqli.query.php
PHP MySQL Query (mysqli, pdo_mysql) uses the buffer mode by default.
That is to say, the query results will be transmitted from MySQL to the PHP process memory at a time,
You can calculate the number of rows in the result set and move the result set pointer.
In buffer mode, if the result set is large, the PHP process also occupies a large amount of memory,
Until the result set is unset or free.
Store_result is used in the buffer mode. all results are stored in the PHP process at one time:
Mysqli: query MYSQLI_STORE_RESULT
Mysqli: store_result
Mysqli_stmt: store_result
If libmysqlclient is used at the underlying layer of the MySQL database driver in PHP, memory_limit cannot count the memory occupied by the result set,
Unless the result set has been assigned to the PHP variable, statistics can be collected if mysqlnd is used as the driver at the underlying layer (mysqlnd is used by default in PHP from 5.4 ).
In the cache-free mode, a resource reference is returned for queries executed. the query results in MySQL are waiting for PHP to obtain.
In the buffer-free mode, the PHP process occupies a small amount of memory, but it will increase the load on the MySQL server.
No other query requests can be sent under the current database connection before PHP retrieves all results.
Use_result indicates no buffer query:
Mysqli: query MYSQLI_USE_RESULT
Mysqli: use_result
Summary:
When the result set is not large, or the number of rows in the result set needs to be obtained before all rows are read, the buffer query is used (default ).
When the result set is large, queries without buffering are used to prevent PHP processes from occupying a large amount of memory.
$ Rs = $ mysqli-> query ("SELECT * FROM City", MYSQLI_USE_RESULT );
$ Pdo-> setAttribute (PDO: MYSQL_ATTR_USE_BUFFERED_QUERY, false );
$ Rs = $ pdo-> query ("SELECT * FROM City ");
By default, the SELECT query results of mysqli_stmt will be left on the MySQL server, waiting for the fetch method to return records one by one back to the PHP program. This will reduce performance, but save memory.
If you need to process all the records, you can call mysqli_stmt: store_result to upload all the results to the PHP program at one time,
This is more efficient and can reduce the burden on the MySQL server, although the memory usage will be more.
You can use mysqli_stmt: $ num_rows to obtain the number of records found in the SELECT statement.
This attribute can be used only when the mysqli_stmt: store_result method is executed in advance and all query results are returned to the PHP program.
Compared with mysqli_result: $ num_rows, this restriction is not imposed.
Use mysqli_stmt: free_result to disable mysqli_stmt: store_result:
$ Stmt-> store_result ();
Echo $ stmt-> num_rows;
$ Stmt-> free_result ();
Http://php.net/manual/zh/mysqli-stmt.fetch.php
Mysqli_stmt: store_result can make mysqli_stmt: fetch more efficient, but you also need to use mysqli_stmt: free_result to explicitly close it.
You can use mysqli_stmt: get_result to get the result set Object $ result, and then mysqli_result: fetch_all to get the query array $ results:
$ Result = $ stmt-> get_result ();
$ Results = $ result-> fetch_all (MYSQLI_ASSOC );
Http://php.net/mysqli
Mysqli: The query executes the SQL statement. if the SQL statement succeeds, the mysqli_result (SELECT, SHOW, DESCRIBE operation) object or TRUE (other operations) is returned. if the SQL statement fails, FALSE is returned. use mysqli: close to close the SQL statement.
Mysqli: prepare: Pre-processes the SQL statement. The statement object is returned successfully. if the SQL statement fails, FALSE is returned.
Mysqli_stmt: execute to execute the SQL statement. use mysqli_stmt: close to close the SQL statement.
Mysqli_stmt: store_result: retrieves all query results (SELECT, SHOW, DESCRIBE, and EXPLAIN) to PHP. optional. close it with mysqli_stmt: free_result.
Mysqli_stmt: bind_result binds the structure generated by prepare and execute to the variable, and then outputs or assigns values to these variables in mysqli_stmt: fetch.
Mysqli_stmt: fetch: each time a result set is returned, the variable assigned to mysqli_stmt: bind_result is bound.
Mysqli_stmt: get_result to obtain the result object, and then call mysqli_result: fetch_all to return the number of result sets. available under mysqlnd.
Mysqli_result: fetch_all returns a number Group of result sets (MYSQLI_NUM (default), MYSQLI_ASSOC, MYSQLI_BOTH). use mysqli_result: close to close. available under mysqlnd.
Mysqli_result: fetch_array each time a result set is returned, which contains a one-dimensional numeric array and associated array.
Mysqli_result: fetch_assoc returns one result set each time, that is, a one-dimensional associated array.
Mysqli_result: One of the result sets returned by fetch_row each time, that is, a one-dimensional number array.
From: http://my.oschina.net/eechen/blog/591426
