mysql - 如何提前完成 mysql_use_result()/mysql_fetch_row()？

Question

我正在为 MySQL 5.5 编写我的第一个 C 客户端并且偶然发现了 the following page在文档中。几乎在最后，它指出(粗体强调我的，斜体强调不是我的):

An advantage of mysql_use_result() is [...]. Disadvantages are that [...]. Furthermore, you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for.

最后一句没看清楚

1) 如果我不遵循那条线会怎样？

2) 我认为如果我确定我有足够的信息，实际上必须有一种方法可以提前结束获取行(否则，这整件事在我看来没有多大意义)。

我知道如果我只是停止获取行然后尝试执行下一条语句可能会发生一些不好的事情，但是没有像 mysql_finish_fetch() 或类似的函数吗？

如果我调用 mysql_free_result() 会发生什么？即使我还没有获取所有行，这也应该释放结果，所以在检索中调用它并继续我想做的任何事情应该是安全的。我错了吗？

Answer 1

这听起来像是 MySQL 暴露给客户端的内部线程问题。把它归结为各种 MySQL gotchas .简而言之，MySQL 显然在内部拥有有限数量的“搜索器”，使用 mysql_use_result() 显然会将其中一个专门用于您的 API 请求。此外，MySQL 显然没有暴露的 API 调用来取消这样的请求。唯一的选择是看到抓取直到结束。

稍长的版本:在内部，MySQL 的游标显然有一个代码路径——我想是为了在常见情况下的性能。当光标找不到更多结果时，该代码路径仅退出。当您使用更常见的 mysql_store_result() 时，MySQL 在将结果返回给应用程序之前已经完成了此操作。但是，当您使用 mysql_use_result() 时，MySQL 要求您执行迭代结果集其余部分的“脏活”，以便清除游标。有趣。

来自documentation :

mysql_use_result() initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result() does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row(). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result(). The client allocates memory only for the current row and a communication buffer that may grow up to max_allowed_packet bytes.

On the other hand, you should not use mysql_use_result() for locking reads if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This ties up the server and prevent other threads from updating any tables from which the data is being fetched.

When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned, otherwise, the unfetched rows are returned as part of the result set for your next query. The C API gives the error Commands out of sync; you can't run this command now if you forget to do this!

因此，实际回答您的问题:

1) What happens if I don't follow that line?

C API 将返回错误信息:Commands out of sync;你现在不能运行这个命令

2) I think that there actually must be a way to prematurely end fetching rows if I decide that I have enough information (otherwise, this whole thing wouldn't make much sense in my eyes).

有人会想，但不是。您必须完全迭代结果集。