Golang MySQL使用IN运算符查询未定义的参数数量 [英] Golang MySQL querying undefined amount of args using IN operator
问题描述
我试图使用IN运算符的MySQL查询并将未定义数量的参数放入我的Golang项目中。
我正在使用包 github.com/go-sql-driver/mysql
并尝试在此Stackoverflow答案上构建我的解决方案:
我已经阅读了一些类似的帖子,提供了一些关于要走的路径的建议,但是我被困在查询的执行部分,因为它不允许直接使用切片参数。
$ $ p $ $ $ $ $ $ $ $ $ $ $ $ $ $ var $ args [] int
for _,v:= range r.Form [type] {
t,_:= strconv.Atoi(v)
args = append(args,t)
}
sql:=SELECT id,name FROM资源WHERE id IN(SELECT resource_id FROM resources_types WHERE type_id IN(?+ strings.Repea t(,?,len(args)-1)+))
fmt.Println(Query:,sql)
stmt,_:= db.Prepare(sql)
行,err:= stmt.Query(args)
推迟stmt.Close()
查询:SELECT id,name FROM资源WHERE id IN(SELECT resource_id
FROM resources_types WHERE type_id IN(?,?))
sql:语句需要2个输入;
当我尝试使用
rows,err:= stmt.Query(args [0],args [1])$ b $ b
但是,因为我需要一个未定义数量的参数,所以它不是一个解决方案。至少可以让它与MySQL一起工作吗? 方案
Stmt.Query()
有一个可变参数:
func(s * Stmt)Query(args。 ..表示可以使用省略号 ...
传递一个切片值作为可变参数的值,但该切片的类型必须是 [] interface {}
,例如:
var args [] interface {}
for _,v:= range r.Form [type] {
t,_:= strconv.Atoi(v)
args = append(args,t)
}
// ...
行,err:= stmt.Qu ery(args ...)
另外,您可以预先生成SQL查询并执行而不传递查询参数,例如,请参阅 Postgres中的Go和IN子句。
I'm trying to use a MySQL query using the IN operator with undefined amount of arguments into my Golang project.
I'm using the package github.com/go-sql-driver/mysql
and tried to build my solution on this Stackoverflow answer : How to execute an IN lookup in SQL using Golang?
I've read some similar posts giving me some advices about the way to go, but I'm stuck on the execution part of the query, because it does not allow the direct use of a slice as argument.
//converting my form args []string into []int
var args []int
for _, v := range r.Form["type"] {
t, _ := strconv.Atoi(v)
args = append(args, t)
}
sql := "SELECT id, name FROM resources WHERE id IN (SELECT resource_id FROM resources_types WHERE type_id IN (?" + strings.Repeat(",?", len(args)-1) + "))"
fmt.Println("Query : ", sql)
stmt, _ := db.Prepare(sql)
rows, err := stmt.Query(args)
defer stmt.Close()
Golang returns me an error at execution :
Query : SELECT id, name FROM resources WHERE id IN (SELECT resource_id
FROM resources_types WHERE type_id IN (?,?))
"sql: statement expects 2 inputs; got 1"
It works when I try with
rows, err := stmt.Query(args[0], args[1])
But as I need an undefined number of arguments, it isn't a solution. Is it at least possible to get it working with MySQL ?
解决方案 Stmt.Query()
has a variadic parameter:
func (s *Stmt) Query(args ...interface{}) (*Rows, error)
This means you can use the ellipsis ...
to pass a slice value as the value of the variadic parameter, but that slice must be of type []interface{}
, e.g.:
var args []interface{}
for _, v := range r.Form["type"] {
t, _ := strconv.Atoi(v)
args = append(args, t)
}
// ...
rows, err := stmt.Query(args...)
As an alternative, you could pre-build the SQL query and execute without passing query arguments, for an example see Go and IN clause in Postgres.
这篇关于Golang MySQL使用IN运算符查询未定义的参数数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!