英文:
Issue with sql query in GO
问题
我有一个非常简单的MySQL表，有3列，A、B和C。
A和B是键。
我有一个GO应用程序，我正在尝试从数据库中检索数据。其他查询都能正常工作，但是这个查询却不能：
aParam := "aValue"
bParam := "3,4,6,9"
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( ? )")
defer stmt.Close()
rows, err := stmt.Query(aParam, bParam)
for rows.Next() {
...
}
如果我将第二个问号替换为具体的值，它就能正常工作：
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( 3,4,6,9 )")
我还尝试了这个（它不起作用）：
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( " + bParam + " )")
有什么想法吗？
英文:
I have a really simple mysql table, with 3 columns, A, B and C.
A and B are keys.
I have a GO app and I'm trying to retrieve data from db. With other queries works like a charm, but with this one it doesn't:
aParam := "aValue"
bParam := "3,4,6,9"
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( ? )")
defer stmt.Close()
rows, err := stmt.Query(aParam, bParam)
for rows.Next() {
...
}
If I replace the second ? for the values, it works perfect:
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( 3,4,6,9 )")
I also tried with this (it doesn't work):
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( " + bParam +" )")
Any idea?
答案1
得分: 2
问题在于单个的?没有被展开为SELECT语句（类似于字符串替换），而是作为一个单独的字符串值3,4,5,6。
你需要展开IN子句中的每个值，如下所示：
params := []interface{} { "aValue", 3, 4, 6, 9 }
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( ?, ?, ?, ? )")
defer stmt.Close()
rows, err := stmt.Query(params...)
为了让你的生活更轻松，你可以使用像sqlx这样的包，它对参数化的IN查询有更好的支持。
英文:
The issue is that the single ? is not expanded into the SELECT statement (like a string replace), but as a single string value 3,4,5,6
You need to expand each value of the IN clause as such:
params := []interface{} { "aValue", 3, 4, 6, 9 }
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( ?, ?, ?, ? )")
defer stmt.Close()
rows, err := stmt.Query(params...)
To make your life easier, you can use a package like sqlx, which has better support for parameterized IN queries
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论