使用准备好的语句有缺点吗？
我读了很多有关准备好的陈述的文章，在我读过的所有内容中，没有人谈论使用它们的缺点。因此，我想知道是否有一些人们容易忽视的“有龙”的地方？
I've been reading a lot about prepared statements and in everything I've read, no one talks about the downsides of using them. Therefore, I'm wondering if there are any "there be dragons" spots that people tend to overlook?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
准备好的语句只是一个已解析和预编译的
SQL语句，它只是等待提供的绑定变量来执行。任何执行的语句迟早都会被准备好（它需要被解析、优化、编译然后执行）。
准备好的语句只是重用解析、优化和编译的结果。
通常，即使您自己不使用准备好的查询，数据库系统也会使用某种优化来节省查询准备时间。
例如，Oracle 在解析查询时首先检查库缓存，如果已经解析了相同的语句，则会使用缓存的执行计划。
Prepared statement is just a parsed and precompiled
SQLstatement which just waits for the bound variables to be provided to be executed.Any executed statement becomes prepared sooner or later (it need to be parsed, optimized, compiled and then executed).
A prepared statement just reuses the results of parsing, optimization and compilation.
Usually database systems use some kind of optimization to save some time on query preparation even if you don't use prepared queries yourself.
Oracle, for instance, when parsing a query first checks the library cache, and if the same statement had already been parsed, it uses the cached execution plan instead.如果您只使用一个语句一次，或者如果您自动生成动态 sql 语句（并且正确地转义所有内容或确定您的参数只有安全字符），那么您不应该使用准备好的语句。
If you use a statement only once, or if you automatically generate dynamic sql statements (and either properly escape everythin or know for certain your parameters have only safe characters) then you should not use prepared statements.
准备好的语句与动态 SQL 相比还有一个小问题，那就是调试它们可能更困难。使用动态 SQL，您始终可以将问题查询写入日志文件，然后直接在服务器上运行它，就像您的程序所看到的那样。使用准备好的语句，可能需要做更多的工作来使用从崩溃数据确定的一组特定参数来测试查询。但也仅此而已，额外的安全性绝对证明了成本的合理性。
There is one other small issue with prepared statements vs dynamic sql, and that is that it can be harder to debug them. With dynamic sql, you can always just write out a problem query to a log file and run it directly on the server exactly as your program sees it. With prepared statements it can take a little more work to test your query with a specific set of parameters determined from crash data. But not that much more, and the extra security definitely justifies the cost.
在某些情况下，数据库引擎在使用准备好的语句时可能会提出较差的查询计划（因为如果没有搜索的实际绑定值，它无法做出正确的假设）。
“注释”部分
例如，请参阅http://www.postgresql.org 的 /docs/current/static/sql-prepare.html
因此，可能值得测试使用和不使用准备语句的查询，以找出哪个更快。理想情况下，您可以根据每个语句决定是否使用准备好的语句，尽管并非所有 ORM 都允许您这样做。
in some situations, the database engine might come up with an inferior query plan when using a prepared statement (because it can't make the right assumptions without having the actual bind values for a search).
see e.g. the "Notes" section at
http://www.postgresql.org/docs/current/static/sql-prepare.html
so it might be worth testing your queries with and without preparing statements to find out which is faster. ideally, you would then decide on a per-statement basis whether to use prepared statements or not, although not all ORMs will allow you to do that.
我能想到的唯一缺点是它们占用服务器上的内存。虽然不多，但可能有一些边缘情况会成为问题，但我很难想到任何情况。
The only downside that I can think of is that they take up memory on the server. It's not much, but there are probably some edge cases where it would be a problem but I'm hard pressed to think of any.