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!
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.