Go-MySQL-Driver: prepared statements with variable query parameters
I would like to use prepared statements with MySQL on my Go server, but I'm not sure how to get it to work with an unknown number of parameters. One endpoint allows users to submit an array of id, and Go will fetch objects from the database that match the given id. This array can hold anywhere from 1 to 20 IDs, so how can I build a prepared statement to handle it? All the examples I've seen require you to know exactly the number of query parameters.
The only (very unlikely) option I can think of is to prepare 20 different SELECT statements and use the one that matches the number of IDs the user presents, but that seems like a terrible hack. Would I even see the performance benefits of prepared statements at this point?
I'm pretty stuck here, so any help would be appreciated!
source to share
No RDBMS that I know of can bind an unknown number of parameters. Unable to match array with unknown number of parameter placeholders. This means there is no sane way to bind an array to a request, like this:
SELECT xxx FROM xxx WHERE xxx in (?,...,?)
This is not a limitation of the client driver, it is simply not supported by the database servers.
There are various workarounds.
You can create a query with 20 ?, bind the values you have, and end the bind with NULL values. It works great because there are certain semantics for comparison operations using NULL values. Condition like "field =?" always evaluates to false when the parameter is bound to NULL, even if some of the strings will match. Suppose you have 5 values in your array, the database server has to deal with 5 provided values plus 15 NULL values. It is usually smart enough to just ignore NULL values
An alternative solution is to prepare all queries (each with a different number of parameters). It is only interesting if the maximum number of parameters is limited. It works well in a database for which prepared statements are really important (like Oracle).
As far as MySQL is concerned, the gains from using a prepared statement are quite limited. Be aware that prepared statements are only supported per session, they are not used across sessions. If you have a lot of sessions, they take memory. On the other hand, parsing with MySQL doesn't come with a lot of overhead (unlike some other database systems). Typically, it is not worth creating many prepared statements to cover a single request.
Note that some MySQL drivers offer a prepared instruction interface, although they do not use the internally prepared operational capability of the MySQL protocol (again, because it often isn't worth it).
There are also some other solutions (for example, relying on a temporary table), but they are only interesting if the number of parameters is significant.
source to share