英文:
Go-MySQL-Driver: Prepared Statements with Variable Query Parameters
问题
我想在我的Go服务器上使用MySQL的预处理语句，但我不确定如何处理未知数量的参数。一个端点允许用户发送一个id数组，Go将从数据库中选择与给定id匹配的对象。这个数组可能包含1到20个id，那么我该如何构建一个能处理这个问题的预处理语句呢？我看到的所有示例都要求你准确地知道查询参数的数量。
我唯一能想到的（非常不太可能的）选项是准备20个不同的SELECT语句，并使用与用户提交的id数量相匹配的语句——但这似乎是一个可怕的hack。在那种情况下，我是否会看到预处理语句的性能优势呢？
我在这里遇到了困难，所以任何帮助都将不胜感激！
英文:
I'd like to use prepared statements with MySQL on my Go server, but I'm not sure how to make it work with an unknown number of parameters. One endpoint allows users to send an array of id's, and Go will SELECT the objects from the database matching the given id's. This array could contain anywhere from 1 to 20 id's, so how would I construct a prepared statement to handle that? All the examples I've seen require you to know exactly the number of query parameters.
The only (very unlikely) option I can think is to prepare 20 different SELECT statements, and use the one that matches the number of id's the user submits - but this seems like a terrible hack. Would I even see the performance benefits of prepared statements at that point?
I'm pretty stuck here, so any help would be appreciated!
答案1
得分: 3
我所了解的任何关系型数据库管理系统（RDBMS）都无法绑定未知数量的参数。无法通过未知数量的参数占位符来匹配数组。这意味着没有聪明的方法可以将数组绑定到查询中，例如：
SELECT xxx FROM xxx WHERE xxx in (?,...,?)
这不是客户端驱动程序的限制，而是数据库服务器根本不支持。
有各种解决方法。
您可以创建带有20个?的查询，绑定您拥有的值，并通过NULL值完成绑定。这是有效的，因为涉及NULL值的比较操作的特定语义。当参数绑定到NULL值时，类似于"field = ?"的条件始终计算为false，即使某些行匹配。假设您的数组中有5个值，数据库服务器将处理5个提供的值和15个NULL值。通常，它会聪明地忽略NULL值。
另一种解决方案是准备所有查询（每个查询具有不同数量的参数）。只有在参数的最大数量有限时才有意义。它在那些真正关注准备语句的数据库上效果很好（例如Oracle）。
就MySQL而言，使用准备语句的收益相当有限。请记住，准备语句仅在会话期间保持，它们不在会话之间共享。如果您有很多会话，它们会占用内存。另一方面，与MySQL解析语句不涉及太多开销（与其他一些数据库系统相反）。通常，生成大量准备语句来覆盖单个查询是不值得的。
请注意，一些MySQL驱动程序提供了准备语句接口，但它们在内部不使用MySQL协议的准备语句功能（同样，因为通常不值得）。
还有一些其他解决方案（如依赖临时表），但只有在参数数量显着时才有意义。
英文:
No RDBMS I'm aware of is able to bind an unknown number of parameters. It is never possible to match an array with an unknown number of parameter placeholders. It means there is not smart way to bind an array to a query such as:
SELECT xxx FROM xxx WHERE xxx in (?,...,?)
This is not a limitation of the client driver, this is simply not supported by database servers.
There are various workarounds.
You can create the query with 20 ?, bind the values you have, and complete the binding by NULL values. It works fine, because of the particular semantic of comparison operations involving NULL values. A condition like "field = ?" evaluates always to false when the parameter is bound to a NULL value, even if some rows would match. Supposing you have 5 values in your array, the database server will have to deal with 5 provided values, plus 15 NULL values. It is usually smart enough to just ignore the NULL values
An alternative solution is to prepare all the queries (each one with a different number of parameters). It is only interesting if the maximum number of parameters is limited. It works well on database for which prepared statements really matters (such as Oracle).
As far as MySQL is concerned, the gain of using a prepared statement is quite limited. Keep in mind that prepared statements are only maintained per session, they are not shared across sessions. If you have a lot of sessions, they take memory. On the other hand, parsing statements with MySQL does not involve much overhead (contrary to some other database systems). Generally, generating plenty of prepared statements to cover a single query is not worth it.
Note that some MySQL drivers offer a prepared statement interface, while they do not use internally the prepared statement capability of the MySQL protocol (again, because often, it is not worth it).
There are also some other solutions (like relying on a temporary table), but they are only interesting if the number of parameters is significant.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论