MySQL 具有相同连接的多个准备好的语句
同时设置多个准备好的语句（使用单个数据库连接）的规则到底是什么？我经常有包含多个查询的循环；最好设置 3 个准备好的语句，循环遍历它们，然后将它们全部关闭。
我发现有时他们可以一起工作，有时则不然。例如，我已经能够同时设置 3 个 SELECT 语句。但我认为在同一个表上准备好 SELECT 和 INSERT 并循环运行会使我与数据库断开连接（或者至少它无法正常工作）。 但是，当我将准备好的语句更改为常规查询时，我可以按相同的顺序运行相同的查询，就很好了。
我几乎是一个新手，希望能澄清其工作原理！
What exactly are the rules for setting up multiple prepared statements simultaneously (with a single DB connection)? I often have loops that include multiple queries; it would be nice to set up 3 prepared statements, cycle through them, then close them all.
What I've found is that sometimes they work together, sometimes they don't. For instance I've been able to set up 3 simultaneous SELECT statements. But I think that having a prepared SELECT and an INSERT on the same table, running in a loop, disconnects me from the database (or at least it doesn't work as it should).
However, when I change the prepared statements to regular queries, I can run the same queries in the same order, just fine.
I'm pretty much a novice and would appreciate some clarification on how this works!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
听起来你有一个很好的存储过程候选者。当您需要根据另一个选择查询的结果将数据插入到一个表中时，通常可以在存储过程中执行此操作，速度比使用编程语言代码稍快一些。
至于为什么你现在的系统不行；我认为您可能会看到“命令不同步；您现在无法在客户端代码中运行此命令，您正在以错误的顺序调用客户端函数。
这种情况发生在准备好的语句中，因为它们实际上返回两个结果集。因此，当您从选择查询中检索一行然后尝试执行插入时，实际上有另一个结果集在管道中等待。您需要在插入之前检索第二个结果。
总而言之，您最好使用常规查询或使用存储过程。
Sounds like you have a good candidate for a stored procedure. When you need to insert data into one table based on the results from another select query, you can usually do that inside a stored procedure slightly quicker than with programming language code.
As for why your current system doesn't work; I think you are probably seeing the 'Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order'
This happens with prepared statements because they actually return two result sets. So when you retrieve a row from your select query and then try to do an insert there is actually another resultset waiting in the pipe. You need to retrieve this second result before doing your insert.
So to sum it up, you are probably bettter off using a regular query or using a stored procedure.