英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论