mysql_unbuffered_query() is no more supporting, so only option is MySQLi or PDO
Queries are using the buffered mode by default. This means that query results are immediately transferred from the MySQL Server to PHP and then are kept in the memory of the PHP process. This allows additional operations like counting the number of rows, and moving (seeking) the current result pointer. It also allows issuing further queries on the same connection while working on the result set. The downside of the buffered mode is that larger result sets might require quite a lot memory. The memory will be kept occupied till all references to the result set are unset or the result set was explicitly freed, which will automatically happen during request end the latest. The terminology "store result" is also used for buffered mode, as the whole result set is stored at once.
Unbuffered MySQL queries execute the query and then return a resource while the data is still waiting on the MySQL server for being fetched. This uses less memory on the PHP-side, but can increase the load on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection. Unbuffered queries can also be referred to as "use result".
Following these characteristics buffered queries should be used in cases where you expect only a limited result set or need to know the amount of returned rows before reading all rows. Unbuffered mode should be used when you expect larger results.
Because buffered queries are the default, the examples below will demonstrate how to execute unbuffered queries with each API.
Example #1 Unbuffered query example: mysqli
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);
if ($uresult) {
while ($row = $uresult->fetch_assoc()) {
echo $row['Name'] . PHP_EOL;
}
}
$uresult->close();
?>
Example #2 Unbuffered query example: pdo_mysql
<?php
$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$uresult = $pdo->query("SELECT Name FROM City");
if ($uresult) {
while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
echo $row['Name'] . PHP_EOL;
}
}
?>
Example #3 Unbuffered query example: mysql
<?php
$conn = mysql_connect("localhost", "my_user", "my_pass");
$db = mysql_select_db("world");
$uresult = mysql_unbuffered_query("SELECT Name FROM City");
if ($uresult) {
while ($row = mysql_fetch_assoc($uresult)) {
echo $row['Name'] . PHP_EOL;
}
}
?>
add a note
User Contributed Notes 2 notes
-7
7 years ago
-36
2 years ago
if you use this, it won't work:
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
Instead do:
$sth = $pdo->prepare('SELECT * FROM my_table',
array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));
Buffered 和 Unbuffered query 的优劣势比较：
So far we've been using the mysqli_query() function to do all our data searching in PHP, and it works well enough for the vast majority of cases(绝大多数案例). However, consider this: how does the mysqli_num_rows() function know how many rows mysqli_query() returned? The answer is simple: mysqli_query() runs the query, and fetches and buffers it all so that it has the complete result set available. The mysqli_num_rows() function then has access to all the rows, and so can return the true row count.
But, what do you do if you have a large number of rows and don't want to wait before MySQL has finished fetching them all before you start using them? In this scenario, passing a special parameter to mysqli_query() causes it to execute the query and return a resource pointing to the result of the query while MySQL is still working, which means you can start reading before the query has finished. This is called an unbuffered query, and is triggered using the special query mode MYSQLI_USE_RESULT.
Earlier I used the example of a golfscores table with 1,000,000 rows. It takes MySQL about 20 seconds to return all that data to PHP when using mysqli_query(), and if you consider that on top of that PHP might do another 20 seconds of work to format and print out that data, the total is 40 seconds of work, of which you see nothing for the first 20 seconds. Using mysqli_query() in its default mode therefore has several obvious disadvantages:
使用buffered query在获取大批量数据的时候有如下明显的几点劣势：
-
PHP must wait while the entire query is executed and returned before it can start processing.
PHP必须等待整条查询执行完毕并返回之后才能继续处理后面的事务。 -
In order to return the whole result to PHP at once, all the data must be held in RAM. Thus, if you have 100MB of data to return, the PHP variable to hold it all will be 100MB.
为了可以一次返回整个运行结果给PHP，所有数据必须保持在内存中，所以，如果有100MB的数据需要返回，PHP变量也必须占用100MB内存空间。
The disadvantages of mysqli_query() happen to be the advantages of these unbuffered queries:
buffered查询的劣势正好是unbuffered查询的优势。
-
The PHP script can parse the results immediately, giving immediate feedback to users.
-
Only one row at a time need be held in RAM.
One nice feature of unbuffered queries is that, internally to PHP, it is almost identical to mysqli_query() ， you just provide an extra parameter. As a result, you can almost use them interchangeably inside your scripts. For example, this script works fine in either mode:
$db = mysqli_connect("localhost", "php", "alm65z", "phpdb");
$result = mysqli_query($db, "SELECT ID, Name FROM conferences;", MYSQLI_USE_RESULT);
while ($row = mysqli_fetch_assoc($result)) {
extract($row, EXTR_PREFIX_ALL, "conf");
print "$conf_Name\n";
}
Before you rush off to make all your queries unbuffered, be aware that there are drawbacks:
Unbuffered需要有以上好处，但是也有如下弊端：
-
You must read all rows from the return value. If you're thinking of using as a quick way to find something then stop processing the rows part way through, you're way off track （偏离轨道）- sorry!
-
If you issue another query before you finish processing all the rows from the previous query, PHP will issue a warning. SELECTs within SELECTs are not possible with unbuffered queries.
Unbuffered查询必须全部执行结束才能发起下一条sql语句。也就是说不能在Unbuffered执行期间插入任何查询。 -
Functions such as mysqli_num_rows() only return the number of rows read so far. This will be 0 as soon as the query returns, but as you call mysqli_fetch_assoc() it will increment until it has the correct number of rows at the end.
必须等所有记录轮询完毕，才能得到正确的“行数”。 -
Between the time you start your unbuffered query and your processing the last row, the table remains locked by MySQL and cannot be written to by other queries. If you plan to do lengthy processing on each row, this is not good.
在处理unbuffered query期间，整个表是被锁住的，不可能被其它进程写入数据。如果你想在每一条记录循环期间做大量的处理工作的话显然是不那么明智。
Choosing whether you want to run a buffered query or not takes a little thinking - if you're not sure what's best, you should almost certainly stay with the standard buffered queries.