PHP query MySQL Returns a large number of data result sets resulting in memory overflow resolution

Web Development If you encounter PHP query MySQL return a large amount of data caused by memory overflow, or memory is not enough to use the situation that need to look at the MySQL C API Association, then what is the result of PHP query MySQL return a large amount of data when the memory is not enough to use the situation?

The answer is: mysql_query and mysql_unbuffered_query two functions

First of all, to analyze a typical example: when the following code is executed, it will cause PHP to request MySQL to return too many results (more than 10W) cause PHP memory is not enough.

while ($row = Mysql_fetch_assoc ($result))

{

...

}

for the above problem, first of all, from the principle of MySQL analysis of MySQL is the classic C/s (client/server, client/server) model, before traversing the result set, the underlying implementation may have all the data through the network (assuming that the use of TCP/IP) read the client buffer, there is another possibility, that is, the data is also in the server side of the send buffer, and did not pass to the client.

find two similar MySQL functions in PHP and MySQL by looking at the source code: mysql_query () and Mysql_unbuffered_query (),

When the previous function was found executing, all the result sets were read from the server side to the client's buffer, and the latter was not, which is " Unbuffered (not buffered) "means. In other words, if you use Mysql_unbuffered_query ()   Executes a SQL statement that returns a large number of result sets, and PHP's memory is not occupied by the result set until the result is traversed. And with mysql_query ()   to execute the same statement, when the function returns, the memory consumption of PHP increases sharply and consumes the memory immediately.
php_function (mysql_query)
{
     php_mysql_do_ Query (Internal_function_param_passthru, mysql_store_result);
}
Php_function (mysql_unbuffered_query)
{
     php_mysql_do_query (Internal_function_param_passthru, Mysql_use_result);
}

All two functions call Php_mysql_do_query (), except for the difference of the 2nd parameter, Mysql_store_result and Mysql_use_result. Look at Php_mysql_do_query again. Implementation of ():

if (Use_store = = Mysql_use_result)
{
Mysql_result=mysql_use_result (&mysql->conn);
} 
Else
{
Mysql_result=mysql_store_result (&mysql->conn);
}

Mysql_use_result () and Mysql_store_result () are MySQL's C API functions, the difference between the two C API functions is that the latter reads the result set from the MySQL server to the Clien T-end, the former simply reads the meta-information of the result set.

pull away: Back to just the topic using mysql_unbuffered_query (), you can avoid the immediate use of memory, if the returned results are stored in the array is not a problem, there will be no PHP query MySQL data volume is too large Causes a memory overflow problem.

PHP query MySQL Returns a large number of data result sets resulting in memory overflow resolution