php MySQLi 查询结果：最佳方法，是否关闭，免费，两者兼而有之？

MySQLi query results: Best approach, do you close, free, both?

提问by Carvell Fenton

I have some questions about using MySQLi, queries, and related memory management. The code here is just to clarify my questions, so don't dump on it for error checking, etc. I know that needs to be done :)

我有一些关于使用MySQLi、查询和相关内存管理的问题。这里的代码只是为了澄清我的问题，所以不要转储它以进行错误检查等。我知道需要这样做:)

Suppose I have something like this:

假设我有这样的事情：

@ $db = new mysqli($dbhost, $un, $ps, $dbname);
$query = "SELECT field1, field2 ".
         "FROM table1 ".
         "WHERE field1={$some_value}";
$results = $db->query($query);

while ($result = $results->fetch_object()) {
    // Do something with the results
}

$query = "SELECT field1, field2 ".
         "FROM table2 ".
         "WHERE field1={$some_value2}";
// question 1
$results = $db->query($query);

while ($result = $results->fetch_object()) {
    // Do something with the second set of results
}

// Tidy up, question 2
if ($results) {
    $results->free();
}
if ($db) {
    $db->close();
}

// Question 3, a general one

So, based on the comments in the code above, here are my questions:

因此，根据上面代码中的注释，以下是我的问题：

1. When I assign the results of the second query to $results, what happens to the memory associated with the previous results? Should I be freeing that result before assigning the new one?

2. Related to 1, when I do clean up at the end, is cleaning up just the last results enough?

3. When I do try to clean up a result, should I be freeing it as above, should I be closing it, or both?

1. 当我将第二个查询的结果分配给 时$results，与先前结果关联的内存会发生什么变化？我应该在分配新结果之前释放该结果吗？

2. 与 1 相关，当我最后清理时，只清理最后一个结果就足够了吗？

3. 当我尝试清理结果时，我应该像上面那样释放它，我应该关闭它，还是两者兼而有之？

I ask question 3 because the PHPdocumentation for mysqli::queryhas an example that uses close, even though close is not part of mysqli_result(see example 1 in mysqli::query). And in contrast, my normal PHP reference text uses free(PHP and MySQL Web Development, Fourth Edition, Welling and Thomson).

我问问题 3，因为PHP文档中mysqli::query有一个使用 close 的示例，即使 close 不是其中的一部分mysqli_result（请参阅mysqli::query 中的示例 1 ）。相比之下，我的正常 PHP 参考文本使用free（PHP 和 MySQL Web 开发，第四版，Welling 和 Thomson）。

回答by Pascal MARTIN

When I assign the results of the second query to $results, what happens to the memory associated with the previous results?

当我将第二个查询的结果分配给 时$results，与先前结果关联的内存会发生什么变化？

When you execute this:

当你执行这个：

$results = $db->query($query);

If there was something in $resultsbefore, this old content cannot be accessed anymore, as there is no reference left to it.

如果$results之前有内容，则无法再访问这些旧内容，因为没有留下任何参考。

In such a case, PHP will mark the old content of the variable as "not needed anymore" -- and it will be removed from memory when PHP needs some memory.

在这种情况下，PHP 会将变量的旧内容标记为“不再需要”——当 PHP 需要一些内存时，它将从内存中删除。

This, at least, is true for general PHP variables; in the case of results from an SQL query, though, some data may be kept in memory on the driver-level -- over which PHP doesn't have much control.

这至少对于一般的 PHP 变量是正确的；但是，对于 SQL 查询的结果，一些数据可能会保存在驱动程序级别的内存中——PHP 对此没有太多控制权。

Should I be freeing that result before assigning the new one?

我应该在分配新结果之前释放该结果吗？

I never do that -- but, quoting the manual page of mysqli_result::free:

我从不这样做 - 但是，引用以下手册页mysqli_result::free：

Note: You should always free your result with mysqli_free_result(), when your result object is not needed anymore

注意：当不再需要结果对象时，您应该始终使用 mysqli_free_result() 释放结果

It probably doesn't matter for a small script... And the only way to be sure would be to test, using memory_get_usagebefore and after calling that method, to see whether there is a difference or not.

对于一个小脚本来说，这可能无关紧要......唯一可以确定的方法是memory_get_usage在调用该方法之前和之后进行测试，以查看是否存在差异。

Related to 1, when I do clean up at the end, is cleaning up just the last results enough?

与 1 相关，当我最后清理时，只清理最后一个结果就足够了吗？

When the scripts end:

当脚本结束时：

• The connection to the database will be closed -- which means any memory that might be used by the driver should be freed
• All variables used by the PHP script will be destroyed -- which means the memory they were using should be freed.

• 与数据库的连接将关闭——这意味着应该释放驱动程序可能使用的任何内存
• PHP 脚本使用的所有变量都将被销毁——这意味着它们使用的内存应该被释放。

So, at the end of the script, there is probably really no need to free the resultset.

所以，在脚本的最后，可能真的没有必要释放结果集。

When I do try to clean up a result, should I be freeing it as above, should I be closing it, or both?

当我尝试清理结果时，我应该像上面那样释放它，我应该关闭它，还是两者兼而有之？

If you close the connection to the database (using mysqli::closelike you proposed), this will disconnect you from the database.

如果您关闭与数据库的连接（按照mysqli::close您的建议使用），这将使您与数据库断开连接。

Which means you'll have to re-connect if you want to do another query! Which is not good at all (takes some time, resources, ... )

这意味着如果你想做另一个查询，你必须重新连接！这一点都不好（需要一些时间，资源，......）

Generally speaking, I would not close the connection to the database until I am really sure that I won't need it anymore -- which means I would not disconnect before the end of the script.

一般来说，我不会关闭与数据库的连接，直到我真的确定我不再需要它——这意味着我不会在脚本结束之前断开连接。

And as "end of the script" means "the connection will be closed" even if you don't specify it; I almost never close the connection myself.

因为“脚本结束”的意思是“连接将被关闭”，即使你没有指定它；我自己几乎从不关闭连接。

回答by mr. w

The answers already provided are good, but I wanted to add one point and clarify another.

已经提供的答案很好，但我想补充一点并澄清另一点。

First, the clarification. Regarding the use of the close() method, it's important to note that the OP was referencing the close() method of the mysqli_result class, not the mysqli class. In the result class, the close() method is simply an alias to the free() method, as shown in the documentation, while in the mysqli class, it closes the connection. Thus, it's okay to use close() on the result in place of free() if desired.

首先，澄清。关于 close() 方法的使用，重要的是要注意 OP 引用的是 mysqli_result 类的 close() 方法，而不是 mysqli 类。在结果类中，close() 方法只是 free() 方法的别名，如文档所示，而在 mysqli 类中，它关闭连接。因此，如果需要，可以在结果上使用 close() 代替 free() 。

Second, the additional point. As has already been pointed out, PHP's execution pattern means that everything will eventually be cleaned up behind you, and thus, you don't necessarily need to worry about releasing memory. However, if you're allocating a lot of result objects, or if you're allocating particularly big result objects (e.g., fetching a large amount of data), then you should probably free the memory when you're done to prevent problems further down the path of execution. This becomes especially important as your application starts to get more traffic, where the total amount of memory tied up across sessions can quickly become significant.

第二，附加点。正如已经指出的那样，PHP 的执行模式意味着一切最终都会在您身后被清除，因此您不必担心释放内存。但是，如果您正在分配大量结果对象，或者如果您正在分配特别大的结果对象（例如，获取大量数据），那么您应该在完成后释放内存以防止进一步出现问题在执行的道路上。当您的应用程序开始获得更多流量时，这变得尤为重要，其中跨会话绑定的总内存量可能会很快变得很大。

回答by Jonathan

As rare as they are, in my opinion memory leaks are a nightmare to find and correct. I go out of my way to avoid them. Below is the pattern I use, based on the code you supplied:

尽管它们很少见，但在我看来，内存泄漏是寻找和纠正的噩梦。我想尽办法避开他们。以下是我使用的模式，基于您提供的代码：

$db = NULL;
try {
    $dbPool = "p:$dbhost"; // question 3: use pooling
    $db = new mysqli($dbPool, $un, $ps, $dbname);
    if ($db->connect_errno) {
        throw new Exception('' . $db->connect_error . ' ' . $db->connect_errno 
                . "\n" . $un . '@' . $dbhost . ' ' . $dbname);
        // NOTE: It's commonly considered a security 
        // risk to output connection information e.g.
        // host, user and database names.
    }

    $query = "SELECT field1, field2 ".
             "FROM table1 ".
             "WHERE field1={$some_value}";

    $results = NULL;
    try {

        if (!$results = $db->query($query)) {
            throw new Exception($db->error . " " . $db->errno 
                    . "\n" . $query);
            // NOTE: It's commonly considered a security 
            // risk to output SQL ($query).
        }
        while ($result = $results->fetch_object()) {
            // Do something with the results
        }

    } catch (Exception $ex) {
        // log, report, or otherwise handle the error
    }
    if ($results) {
        $results->free(); // question 1: why risk it?
    }

    $query = "SELECT field1, field2 ".
             "FROM table2 ".
             "WHERE field1={$some_value2}";

    $results = NULL; 
    try {

        if (!$results = $db->query($query)) {
            throw new Exception($db->error . " " . $db->errno 
                    . "\n" . $query);
            // NOTE: It's commonly considered a security 
            // risk to output SQL ($query).
        }            
        while ($result = $results->fetch_object()) {
            // Do something with the second set of results
        }

    } catch (Exception $ex) {
        // log, report, or otherwise handle the error
    }
    if ($results) {
        $results->free(); // question 2: again, why risk it?
    }

} catch (Exception $ex) {
    // log, report, or otherwise handle the error
}
if ($db) {
    $db->close();
}

In my opinion, connection pooling increases the chances for a memory leak, but according to the manual, the connection pooling libraries do a lot of cleanup for you automatically:

在我看来，连接池会增加内存泄漏的机会，但根据手册，连接池库会自动为您做很多清理工作：

The persistent connection of the mysqli extension however provides built-in cleanup handling code. The cleanup carried out by mysqli includes:

Rollback active transactions

Close and drop temporary tables

Unlock tables

Reset session variables

Close prepared statements (always happens with PHP)

Close handler

Release locks acquired with GET_LOCK()

This ensures that persistent connections are in a clean state on return from the connection pool, before the client process uses them.

然而，mysqli 扩展的持久连接提供了内置的清理处理代码。mysqli 执行的清理包括：

回滚活动事务

关闭和删除临时表

解锁桌子

重置会话变量

关闭准备好的语句（总是在 PHP 中发生）

关闭处理程序

释放通过 GET_LOCK() 获取的锁

这确保在客户端进程使用它们之前，持久连接在从连接池返回时处于干净状态。

source:http://php.net/manual/en/mysqli.persistconns.php

来源：http : //php.net/manual/en/mysqli.persistconns.php

I also agree with Pascal MARTIN that it's a good idea to open your connection at the beginning of your script and close it at the end. I think connection pooling makes that less important, but still a good idea.

我也同意 Pascal MARTIN 的观点，即在脚本开头打开连接并在结尾关闭它是个好主意。我认为连接池使这不那么重要，但仍然是一个好主意。

回答by Pete

Thanks for all the answers, I'd also like to add my experiences of running multiple MySQL queries in one script. Mysqli gave “Commands out of sync” errors after running a query after a MySql procedures with multiple queries. To solve this I had to free all the open result sets using rizwan-mirza's solution to mysqli_free_result()any mysqli_more_results()https://stackoverflow.com/a/25907704/462781

感谢所有的答案，我还想添加我在一个脚本中运行多个 MySQL 查询的经验。在具有多个查询的 MySql 过程之后运行查询后，Mysqli 出现“命令不同步”错误。为了解决这个问题，我必须使用rizwan-mirza对mysqli_free_result()任何https://stackoverflow.com/a/25907704/462781的解决方案释放所有打开的结果集mysqli_more_results()

回答by Your Common Sense

The general PHP way is not to close any opened resource. Everything will be automatically closed at the script end. The onlycase where you have to take care of manual close is if you have long heavy codeto run, which is not very common for PHP.

PHP 的一般方式是不关闭任何打开的资源。一切都将在脚本结束时自动关闭。在只有在那里你必须照顾手动关闭的情况是，如果你有长期重码跑，这是不是很常见的PHP。