英文:
parameterizing SQL subquery in golang
问题
请考虑以下问题。
我需要在golang中对SQL（子）查询进行参数化。请考虑下面的伪代码或者在https://go.dev/play/p/F-jZGEiDnsd中查看。
hayStack的详细信息以字符串切片lookIn的形式传递给我，可能会有所变化。我需要在所有这些hayStacks中搜索%eedl%（needle）。
注释中的代码是我目前的处理方式 - 我只对我要查找的needle进行参数化。
如何对hayStacks进行参数化？
package main
import "fmt"
func main() {
fmt.Println("Hello, 世界")
lookIn := []string{"hayStack.1", "hayStack.2", "hayStack.3", "hayStack.4", "hayStack.5"}
needle := "eedl"
needle = "%" + needle + "%"
for i := range lookIn {
fmt.Println("lookIn", lookIn[i])
}
fmt.Println("needle", needle)
/* this is how I currently do
txt := `select needle,tagTitle,Epoch from ( select needle,tagTitle,Epoch where hayStackName = "hayStack.1" or hayStackName = "hayStack.2" or hayStackName = "hayStack.3" or hayStackName = "hayStack.3" or hayStackName = "hayStack.4" or hayStackName = "hayStack.5" ) where tagTitle like ? order by tagTitle COLLATE NOCASE ASC ;`
rows, err := sqliteDbPtr.Query(txt, needle)
if err != nil {
if err != sql.ErrNoRows {
checkErr(err)
panic(err)
}
return
}
defer rows.Close()
*/
}
非常感谢您的任何建议。
英文:
Please consider this question.
I need to parameterize a SQL (sub) query in golang. Please consider the pseudo-code below or at https://go.dev/play/p/F-jZGEiDnsd
The hayStack details come to me in an string slice lookIn and can vary. I need to search for %eedl% (needle) in all these haystacks.
The code in the comment is how I currently handle it - I only parameterize the needle I am looking for.
How do I parameterize the hayStacks as well?
package main
import "fmt"
func main() {
fmt.Println("Hello, 世界")
lookIn := []string{"hayStack.1", "hayStack.2", "hayStack.3", "hayStack.4", "hayStack.5"}
needle := "eedl"
needle = "%" + needle + "%"
for i := range lookIn {
fmt.Println("lookIn", lookIn[i])
}
fmt.Println("needle", needle)
/* this is how I currently do
txt := `select needle,tagTitle,Epoch from ( select needle,tagTitle,Epoch where hayStackName = "hayStack.1" or hayStackName = "hayStack.2" or hayStackName = "hayStack.3" or hayStackName = "hayStack.3" or hayStackName = "hayStack.4" or hayStackName = "hayStack.5" ) where tagTitle like ? order by tagTitle COLLATE NOCASE ASC ;`
rows, err := sqliteDbPtr.Query(txt, needle)
if err != nil {
if err != sql.ErrNoRows {
checkErr(err)
panic(err)
}
return
}
defer rows.Close()
*/
}
Any input will be much appreciated.
答案1
得分: 1
只使用标准库，你可以将 haystack 参数和 needle 参数收集到一个单独的切片中，然后将其传递给 Query 方法。
对于 SQL 本身，你可以使用 IN 运算符，并根据 haystack 的数量生成 ? 的列表作为其右操作数。
args := make([]interface{}, len(lookIn)+1) // 所有参数的切片
inRHS := "" // IN 运算符右操作数的字符串，形式为 ?,...
for i := range lookIn {
args[i] = lookIn[i]
inRHS += "?,"
}
args[len(args)-1] = needle // 添加最后一个参数
inRHS = inRHS[:len(inRHS)-1] // 移除最后一个逗号
query := `SELECT needle,tagTitle,Epoch
FROM some_table
WHERE hayStackName IN (` + inRHS + `)
AND tagTitle LIKE ?
ORDER BY tagTitle COLLATE NOCASE ASC`
rows, err := sqliteDbPtr.Query(query, args...)
if err != nil {
// 处理错误
}
defer rows.Close()
for rows.Next() {
//
}
if err := rows.Err(); err != nil {
//
}
注意，我已经删除了子查询，并添加了 FROM some_table 子句，这是你的问题中缺少的部分。
英文:
Using just the standard library you can collect the haystack arguments and the needle argument into a single slice and then pass that to the Query method.
For the SQL itself you can use the IN operator and generate its right hand side operand as a list of ? based on the number of haystacks.
args := make([]interface{}, len(lookIn)+1) // slice of all args
inRHS := "" // string of ?,... for the IN operator's right hand side
for i := range lookIn {
args[i] = lookIn[i]
inRHS += "?,"
}
args[len(args)-1] = needle // add the last arg
inRHS = inRHS[:len(inRHS)-1] // remove last ","
query := `SELECT needle,tagTitle,Epoch
FROM some_table
WHERE hayStackName IN (` + inRHS + `)
AND tagTitle LIKE ?
ORDER BY tagTitle COLLATE NOCASE ASC`
rows, err := sqliteDbPtr.Query(query, args...)
if err != nil {
// handle error
}
defer rows.Close()
for rows.Next() {
//
}
if err := rows.Err(); err != nil {
//
}
NOTE that I've removed the subquery and added the FROM some_table clause which your question was missing.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论