How to execute an IN lookup in SQL using Golang?

huangapple
go评论192阅读模式
英文:

How to execute an IN lookup in SQL using Golang?

问题

这个SQL查询中，Go语言对于第二个参数想要什么呢？我正在尝试在PostgreSQL中使用IN查询。

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

我真正想要的是：

  1. SELECT * FROM awesome_table WHERE id=10 AND other_field IN (this, that);
英文:

What does Go want for the second param in this SQL query.
I am trying to use the IN lookup in postgres.

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

What I really want:

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

答案1

得分: 62

看起来你可能正在使用pq驱动程序。pq最近通过pq.Array（参见pull request 466）添加了对Postgres特定数组的支持。你可以通过以下方式获得你想要的结果：

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

我认为这会生成以下SQL语句：

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

请注意，这利用了预处理语句，因此输入应该经过过滤。

英文:

It looks like you may be using the pq driver. pq recently added Postgres-specific Array support via pq.Array (see pull request 466). You can get what you want via:

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

I think this generates the SQL:

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

Note this utilizes prepared statements, so the inputs should be sanitized.

答案2

得分: 57

查询只需要使用可变参数来替换SQL中的参数。所以，在你的例子中，你只需要这样做：

  1. rows, err := stmt.Query(10)

假设你的第二个例子中的this和that是动态的，那么你可以这样做：

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

如果你对于"IN"部分有可变参数，你可以这样做：

  1. package main
  3. import "fmt"
  4. import "strings"
  6. func main() {
  7.     stuff := []interface{}{"this", "that", "otherthing"}
  8.     sql := "select * from foo where id=? and name in (?"+ strings.Repeat(",?", len(stuff)-1) + ")"
  9.     fmt.Println("SQL:", sql)
  10.     args := []interface{}{10}
  11.     args = append(args, stuff...)
  12.     fakeExec(args...)
  13.     // 这种写法也可以，但我认为对于读者来说更难理解
  14.     //fakeExec(append([]interface{}{10},stuff...)...)
  15. }
  17. func fakeExec(args ...interface{}) {
  18.     fmt.Println("Got:", args)
  19. }
英文:

Query just takes varargs to replace the params in your sql
so, in your example, you would just do

  1. rows, err := stmt.Query(10)

say, this and that of your second example were dynamic, then you'd do

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

If you have variable args for the "IN" part, you can do (play)

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

答案3

得分: 25

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

首先，获取 https://github.com/jmoiron/sqlx

然后，使用以下代码：

  1. ids := []int{1, 2, 3}
  2. q, args, err := sqlx.In("SELECT id, username FROM users WHERE id IN(?)", ids) // 创建查询字符串和参数
  3. // 当然，你应该检查错误
  4. q = sqlx.Rebind(sqlx.DOLLAR, q) // 仅适用于 PostgreSQL
  5. rows, err := db.Query(q, args...) // 使用普通的 PostgreSQL/任何 SQL 驱动程序，重要的是在切片（数组）后面加上 '...'

希望对你有帮助！

英文:

Incase anyone like me was trying to use an array with a query, here is an easy solution.

get https://github.com/jmoiron/sqlx

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

答案4

得分: 20

使用PostgreSQL，至少你有一个选项可以将整个数组作为字符串传递，使用一个占位符：

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

这样，你可以使用一个查询字符串，所有的字符串拼接都限制在参数中。如果参数格式不正确，你不会得到SQL注入，只会得到类似这样的错误：ERROR: invalid input syntax for integer: "xyz"

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

英文:

> With PostgreSQL, at least, you have the option of passing the entire array as a string, using a single placeholder:

  1. db.Query("select 1 = any($1::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"

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

答案5

得分: 6

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

  1. arr := []string{"this", "that"}
  2. query, args, err := sqlx.In("SELECT * FROM awesome_table WHERE id=10 AND other_field IN (?)", arr)
  4. query = db.Rebind(query) // sqlx.In返回带有`?`绑定变量的查询，可以在此处重新绑定以匹配所使用的数据库（例如postgre、oracle等），如果使用mysql可以跳过此步骤
  5. rows, err := db.Query(query, args...)
英文:

if you use sqlx, you can follow this way:
https://github.com/jmoiron/sqlx/issues/346

  1. arr := []string{"this", "that"}
  2. query, args, err := sqlx.In("SELECT * FROM awesome_table WHERE id=10 AND other_field IN (?)", arr)
  3.  
  4. 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)
  5. rows, err := db.Query(query, args...)

答案6

得分: 1

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)

// 使用 .Select 进行多个结果返回
err = db.Select(&awesomes, db.SQL.Rebind(q), args...)

// 使用 .Get 进行单个结果返回
err = db.Get(&awesome, db.SQL.Rebind(q), args...)

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

答案7

得分: 0

我尝试了一种不同的方法。一种更简单、更容易的方法，可能不太高效。

stringedIDs := fmt.Sprintf("%v", ids)
stringedIDs = stringedIDs[1 : len(stringedIDs)-1]
stringedIDs = strings.ReplaceAll(stringedIDs, " ", ",")
query := "SELECT * FROM users WHERE id IN (" + stringedIDs + ")"
//然后按照标准的database/sql查询进行操作
rows, err := db.Query(query)
//错误检查
if err != nil {
//处理错误
} else {
//处理行数据
}

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

答案8

得分: -1

以下是翻译好的内容：

相当平凡，只有在服务器生成时才能使用。其中UserIDs是一个字符串切片（列表）：

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

Rather pedestrian and only to be used if server generated. Where UserIDs is a slice (list) of strings:

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

答案9

得分: -3

你也可以使用这种直接转换的方法。

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

警告
这种方法容易受到 SQL 注入攻击。只有在 awesome_id_list 是由服务器生成的情况下才可以使用这种方法。

英文:

You can also use this direct conversion.

  1. awesome_id_list := []int{3,5,8}
  3. var str string
  4. for _, value := range awesome_id_list {
  5. 		str += strconv.Itoa(value) + ","
  6. }
  8. 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_list is server generated.

huangapple
  • 本文由 发表于 2013年11月29日 00:43:20
  • 转载请务必保留本文链接：https://go.coder-hub.com/20271123.html
  • go
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定