如何使用Golang在SQL中执行IN查找

Question

Go希望此SQL查询中的第二个参数是什么。我正在尝试在postgres中使用IN查找。

stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id= $1 AND other_field IN $2")
rows, err := stmt.Query(10, ???)

我真正想要的：

SELECT * FROM awesome_table WHERE id=10 AND other_field IN (this, that);

Answer 1

看起来您可能正在使用pq驱动程序。pq最近通过pq添加了Postgres特定的Array支持。数组（请参阅拉取请求466）。你可以通过获得你想要的东西

stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id= $1 AND other_field = ANY($2)")
rows, err := stmt.Query(10, pq.Array([]string{'this','that'})

我认为这会生成SQL:

SELECT * FROM awesome_table WHERE id=10 AND other_field = ANY('{"this", "that"}');

请注意，这使用了准备好的语句，因此应该对输入进行净化。

Answer 2

Query只需要varargs来替换sql中的params所以，在你的例子中，你只需要做

rows, err := stmt.Query(10)

比如说，第二个例子的这个和那个是动态的，那么你就做

stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id=$1 AND other_field IN ($2, $3)")
rows, err := stmt.Query(10,"this","that")

如果你有"IN"部分的可变参数，你可以做（播放）

package main
import "fmt"
import "strings"
func main() {
    stuff := []interface{}{"this", "that", "otherthing"}
    sql := "select * from foo where id=? and name in (?" + strings.Repeat(",?", len(stuff)-1) + ")"
    fmt.Println("SQL:", sql)
    args := []interface{}{10}
    args = append(args, stuff...)
    fakeExec(args...)
    // This also works, but I think it's harder for folks to read
    //fakeExec(append([]interface{}{10},stuff...)...)
}
func fakeExec(args ...interface{}) {
    fmt.Println("Got:", args)
}

Answer 3

如果像我这样的人试图在查询中使用数组，这里有一个简单的解决方案。

得到https://github.com/jmoiron/sqlx

ids := []int{1, 2, 3}
q,args,err := sqlx.In("SELECT id,username FROM users WHERE id IN(?);", ids) //creates the query string and arguments
//you should check for errors of course
q = sqlx.Rebind(sqlx.DOLLAR,q) //only if postgres
rows, err := db.Query(q,args...) //use normal POSTGRES/ANY SQL driver important to include the '...' after the Slice(array)

Answer 4

至少在PostgreSQL中，您可以选择使用单个占位符将整个数组作为字符串传递：

db.Query("select 1 = any($1::integer[])", "{1,2,3}")

这样，就可以使用单个查询字符串，并且所有的字符串串联都局限于该参数。如果参数格式不正确，则不会得到SQL注入；您只会得到以下内容：错误：integer的无效输入语法："xyz"

https://groups.google.com/d/msg/golang-nuts/vHbg09g7s2I/RKU7XsO25SIJ

Answer 5

如果使用sqlx，可以按照以下方式操作：https://github.com/jmoiron/sqlx/issues/346

arr := []string{"this", "that"}
query, args, err := sqlx.In("SELECT * FROM awesome_table WHERE id=10 AND other_field IN (?)", arr)
 
query = db.Rebind(query) // sqlx.In returns queries with the `?` bindvar, rebind it here for matching the database in used (e.g. postgre, oracle etc, can skip it if you use mysql)
rows, err := db.Query(query, args...)

Answer 6

var awesome AwesomeStruct
var awesomes []*AwesomeStruct
ids := []int{1,2,3,4}
q, args, err := sqlx.In(`
  SELECT * FROM awesome_table WHERE id=(?) AND other_field IN (?)`, 10, ids)
// use .Select for multiple return
err = db.Select(&awesomes, db.SQL.Rebind(q), args...)
// use .Get for single return
err = db.Get(&awesome, db.SQL.Rebind(q), args...)

Answer 7

//I tried a different way. A simpler and easier way, maybe not too efficient.
stringedIDs := fmt.Sprintf("%v", ids)
stringedIDs = stringedIDs[1 : len(stringedIDs)-1]
stringedIDs = strings.ReplaceAll(stringedIDs, " ", ",")
query := "SELECT * FROM users WHERE id IN ("  + stringedIDs + ")"
//Then follow your standard database/sql Query
rows, err := db.Query(query)
//error checking
if err != nil {
    // Handle errors
} else {
    // Process rows
}

Answer 8

相当普通，仅在服务器生成时使用。其中UserID是字符串的切片（列表）：

sqlc := `select count(*) from test.Logins where UserID 
                in ("` + strings.Join(UserIDs,`","`) + `")`
errc := db.QueryRow(sqlc).Scan(&Logins)

Answer 9

您也可以使用这种直接转换。

awesome_id_list := []int{3,5,8}
var str string
for _, value := range awesome_id_list {
        str += strconv.Itoa(value) + ","
}
query := "SELECT * FROM awesome_table WHERE id IN (" + str[:len(str)-1] + ")"

警告
此方法易受SQL注入攻击。仅当awesome_id_list是服务器生成的时才使用此方法。