How to execute an IN lookup in SQL using Golang?

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

How to execute an IN lookup in SQL using Golang?

问题

这个SQL查询中,Go语言对于第二个参数想要什么呢?我正在尝试在PostgreSQL中使用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);
英文:

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

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

What I really want:

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

答案1

得分: 62

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

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\"}');

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

英文:

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:

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'})

I think this generates the 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.

答案2

得分: 57

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

rows, err := stmt.Query(10)

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

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...)
    // 这种写法也可以,但我认为对于读者来说更难理解
    //fakeExec(append([]interface{}{10},stuff...)...)
}

func fakeExec(args ...interface{}) {
    fmt.Println("Got:", args)
}
英文:

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

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=$1 AND other_field IN ($2, $3)")
rows, err := stmt.Query(10,"this","that")

If you have variable args for the "IN" part, you can do (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)
}

答案3

得分: 25

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

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

然后,使用以下代码:

ids := []int{1, 2, 3}
q, args, err := sqlx.In("SELECT id, username FROM users WHERE id IN(?)", ids) // 创建查询字符串和参数
// 当然,你应该检查错误
q = sqlx.Rebind(sqlx.DOLLAR, q) // 仅适用于 PostgreSQL
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

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)

答案4

得分: 20

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

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:

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

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返回带有`?`绑定变量的查询,可以在此处重新绑定以匹配所使用的数据库(例如postgre、oracle等),如果使用mysql可以跳过此步骤
rows, err := db.Query(query, args...)
英文:

if you use sqlx, you can follow this way:
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...)

答案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...)

英文:
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...)

答案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 {
//处理行数据
}

英文:
//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
}

答案8

得分: -1

以下是翻译好的内容:

相当平凡,只有在服务器生成时才能使用。其中UserIDs是一个字符串切片(列表):

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

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

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

答案9

得分: -3

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

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 是由服务器生成的情况下才可以使用这种方法。

英文:

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_list is server generated.

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

发表评论

匿名网友

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

确定