如何使用 Golang 在 SQL 中执行 IN 查找？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20271123/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute an IN lookup in SQL using Golang?
提问by a.m.
What does Go want for the second param in this SQL query.
I am trying to use the INlookup in postgres.
这个 SQL 查询中的第二个参数 Go 想要什么。我正在尝试IN在 postgres 中使用查找。
stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id= AND other_field IN ")
rows, err := stmt.Query(10, ???)
What I really want:
我真正想要的是：
SELECT * FROM awesome_table WHERE id=10 AND other_field IN (this, that);
回答by David Budworth
Query just takes varargs to replace the params in your sql so, in your example, you would just do
查询只需要可变参数来替换您的 sql 中的参数，因此，在您的示例中，您只需执行
rows, err := stmt.Query(10)
say, this and that of your second example were dynamic, then you'd do
说，你的第二个例子的这个和那个是动态的，那么你会做
stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id= AND other_field IN (, )")
rows, err := stmt.Query(10,"this","that")
If you have variable args for the "IN" part, you can do (play)
如果“IN”部分有可变参数，则可以执行 ( play)
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)
}
回答by Pete
It looks like you may be using the pq driver. pqrecently added Postgres-specific Array support via pq.Array(see pull request 466). You can get what you want via:
看起来您可能正在使用pq 驱动程序。 pq最近通过pq.Array添加了 Postgres 特定的 Array 支持（请参阅pull request 466）。您可以通过以下方式获得您想要的：
stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id= AND other_field = ANY()")
rows, err := stmt.Query(10, pq.Array([]string{'this','that'})
I think this generates the SQL:
我认为这会生成 SQL：
SELECT * FROM awesome_table WHERE id=10 AND other_field = ANY('{"this", "that"}');
Note this utilizes prepared statements, so the inputs should be sanitized.
请注意，这使用了准备好的语句，因此应清理输入。
回答by Krtko
Incase anyone like me was trying to use an array with a query, here is an easy solution.
如果像我这样的人试图在查询中使用数组，这里有一个简单的解决方案。
get https://github.com/jmtheitroadon/sqlx
获取https://github.com/jmtheitroadon/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)
回答by Ivan Rave
With PostgreSQL, at least, you have the option of passing the entire array as a string, using a single placeholder:
至少在 PostgreSQL 中，您可以选择使用单个占位符将整个数组作为字符串传递：
db.Query("select 1 = any(::integer[])", "{1,2,3}")
That way, you can use a single query string, and all the string concatenation is confined to the parameter. And if the parameter is malformed, you don't get an SQL injection; you just get something like: ERROR: invalid input syntax for integer: "xyz"
这样，您可以使用单个查询字符串，并且所有字符串连接都仅限于参数。如果参数格式错误，则不会收到 SQL 注入；你会得到类似的东西：错误：整数的无效输入语法：“xyz”
https://groups.google.com/d/msg/golang-nuts/vHbg09g7s2I/RKU7XsO25SIJ
https://groups.google.com/d/msg/golang-nuts/vHbg09g7s2I/RKU7XsO25SIJ
回答by 7urkm3n
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...)
回答by Ravneet Singh
//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
}
回答by user2099484
Rather pedestrian and only to be used if server generated. Where UserIDs is a slice (list) of strings:
相当行人，仅在服务器生成时使用。其中 UserIDs 是一个字符串片段（列表）：
sqlc := `select count(*) from test.Logins where UserID
in ("` + strings.Join(UserIDs,`","`) + `")`
errc := db.QueryRow(sqlc).Scan(&Logins)
回答by Thellimist
You can also use this direct conversion.
您也可以使用这种直接转换。
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] + ")"
WARNING
This is method is vulnerable to SQL Injection. Use this method only if awesome_id_listis server generated.
警告
此方法易受 SQL 注入攻击。仅当awesome_id_list服务器生成时才使用此方法。
