英文:
golang slice in mysql query with where in clause
问题
我正在运行以下查询,但只得到第一个id的值:-
select * from table
where table.
idin ('1', '2', '3', '4', '5', '6', '7', '9', '11', '13', '14', '15', '17') and
table.
deleted_at` is null
我已经完成了以下操作:-
var aID = make([]string, 0)
var in India // india is struct
for rows.Next() {
cook := rows.Scan(&in.ID)
aID = append(aID, strconv.Itoa(in.ID))
}
asID = strings.Join(aID, ",")
anotherRow,err := db.Query("SELECT * from table2 where id in (?)", asID)
if err != nil { fmt.Printf("Error: ", err) }
// ... Other line follows up with "for anotherRow.Next() and fetching"
在获取数据时,它只返回值"1",并忽略了传递给它的所有其他ID,这些ID是'2','3','4','5','6','7','9','11','13','14','15','17'。
我应该如何正确传递它?
我正在使用go-sql-driver/mysql
。
常见问题:
-
aID
确实包含所有这些数字作为字符串,并且 -
表中有提供的上述
id
的所有行。 -
table
是从中获取id
并将其附加到aID
中的表,并且使用in
语句从table2
中获取存储在aID
中的id
的另一条记录。
谢谢
英文:
I am running the below query, but only get the first id value:-
select * from `table` where table`.`id` in ('1', '2', '3', '4', '5', '6', '7', '9', '11', '13', '14', '15', '17') and `table`.`deleted_at` is null
I have done the following:-
var aID = make([]string, 0)
var in India // india is struct
for rows.Next() {
cook := rows.Scan(&in.ID)
aID = append(aID, strconv.Itoa(in.ID))
}
asID = strings.Join(aID, ",")
anotherRow,err := db.Query("SELECT * from table2 where id in (?)", asID)
if err != nil { fmt.Printf("Error: ", err) }
// ... Other line follows up with "for anotherRow.Next() and fetching"
While fetching data, it only returns value of "1" and ignores all other ID passed to it, which are '2', '3', '4', '5', '6', '7', '9', '11', '13', '14', '15', '17'
.
How can I pass it correctly?
I am using go-sql-driver/mysql
.
FAQ :
-
aID
does contain all those numbers as string and -
table has all the rows available with provided above
id
. -
table
is from whereid
is fetched and appended toaID
and another record withid
stored inaID
are fetched within
statement fromtable2
.
Thanks
答案1
得分: 39
你可以这样做:
args := make([]interface{}, len(asID))
for i, id := range asID {
args[i] = id
}
stmt := `SELECT * from table2 where id in (?` + strings.Repeat(",?", len(args)-1) + `)`
anotherRow, err := db.Query(stmt, args...)
请注意,如果asID
的长度可能为0,你需要加入一个保护条件。
如果你有其他要传入的参数,你需要将它们添加到args
切片中。
另外需要注意的是,你应该明确指定你想要的列,这样你可以确保将正确的列扫描到正确的字段中。
英文:
You can do something like this:
args := make([]interface{}, len(asID))
for i, id := range asID {
args[i] = id
}
stmt := `SELECT * from table2 where id in (?` + strings.Repeat(",?", len(args)-1) + `)`
anotherRow, err := db.Query(stmt, args...)
Just note you will want to put in a guard if asID
can ever have len == 0.
If you have any other arguments to pass in, you'll have to add them to the args
slice.
Also to note, you should explicitly name the columns you want so you can guarantee you are scanning in the correct columns to the correct fields.
答案2
得分: 8
尝试一下:
q, args, err := sqlx.In("SELECT * FROM table2 WHERE id IN(?)", asID) // 创建查询字符串和参数
rows, err := db.Query(q, args...)
你也可以使用 Masterminds/squirrel 包:
import sq "github.com/Masterminds/squirrel"
...
users := sq.Select("*").From("table2")
active := users.Where(sq.Eq{"id": []string{"1", "2", "3"}})
sql, args, err := active.ToSql()
当使用带有切片的 sq.Eq 结构时,它会自动执行 in
子句。
英文:
Try
q,args,err := sqlx.In("SELECT * FROM table2 WHERE id IN(?);", asID) //creates the query string and arguments
rows, err := db.Query(q,args...)
You could also use the Masterminds/squirrel package:
import sq "github.com/Masterminds/squirrel"
...
users := sq.Select("*").From("table2")
active := users.Where(sq.Eq{"id":[]string{"1","2","3"}})
sql, args, err := active.ToSql()
Which will do the in clause
automatically when using sq.Eq struct with a slice.
答案3
得分: 1
对于使数组/切片直接与SQL查询一起工作的查询,最优雅的解决方案是使用SQL准备语句,这样也可以防止SQL注入攻击。
idAry := []string{"1", "2", "3"}
q := "SELECT * FROM table WHERE id = any($1);"
rows, err := db.Exec(q, pq.Array(idAry))
这段代码使用了SQL准备语句,将数组idAry作为参数传递给查询。这样可以确保查询的安全性,避免了字符串拼接导致的SQL注入问题。
英文:
The most elegant solution for queries to make array/slice work directly with sql queries. This also sql injection proof as you are not using string concatenation rather using sql prepared statement
idAry := []string{"1", "2", "3"}
q := "SELECT * FROM table WHERE id = any($1);"
rows, err := db.Exec(q, pq.Array(authors))
答案4
得分: 0
也许可以这样写:
func GetPlaceholders(values ...string) (placeholders string, parameters []interface{}) {
n := len(values)
p := make([]string, n)
parameters = make([]interface{}, n)
for i := 0; i < n; i++ {
p[i] = "?"
parameters[i] = values[i]
}
placeholders = strings.Join(p, ",")
return placeholders, parameters
}
然后像这样调用函数:
placeholders, params := GetPlaceholders("1", "2", "3")
rows, err := db.Query(`select language, textkey, text
from language where textkey in (`+placeholders+`)
order by language, textkey`, params...)
希望对你有所帮助!
英文:
maybe something like this.
func GetPlaceholders(values ...string) (placeholders string, parameters []interface{}) {
n := len(values)
p := make([]string, n)
parameters = make([]interface{}, n)
for i := 0; i < n; i++ {
p[i] = "?"
parameters[i] = values[i]
}
placeholders = strings.Join(p, ",")
return placeholders, parameters
}
and calling the function like this
placeholders, params := GetPlaceholders("1", "2", "3")
rows, err := db.Query(`select language, textkey, text
from language where textkey in (`+placeholders+`)
order by language, textkey`, params...)
答案5
得分: -1
由于你正在处理来自自己数据库的ID,并且如果你确定没有人能够在代码中注入恶意的“ids”,那么不要使用占位符?
,而是使用fmt
包。
fmt.Sprintf("SELECT * from table2 where id in (%s)", asID)
这将得到SELECT * from table2 where id in (1,2,3,4...)
,而不是SELECT * from table2 where id in ('1,2,3,4...')
。
英文:
Since you're dealing with ids from your own database and if you are certain there is no way someone could inject malicious "ids" into that code, don't use the placeholder ?
and just use the fmt
package.
fmt.Sprintf("SELECT * from table2 where id in (%s)", asID)
this will result in SELECT * from table2 where id in (1,2,3,4...)
as opposed to SELECT * from table2 where id in ('1,2,3,4...')
答案6
得分: -2
示例:
idAry := []string{"1", "2", "3";}
ids := strings.Join(idAry, "','");
sqlRaw := fmt.Sprintf(`SELECT * FROM table WHERE id IN ('%s')`, ids);
rows, err := db.Query(sqlRaw);
它正常工作。
英文:
example:
idAry := []string{"1", "2", "3"}
ids := strings.Join(idAry, "','")
sqlRaw := fmt.Sprintf(`SELECT * FROM table WHERE id IN ('%s')`, ids)
rows, err := db.Query(sqlRaw)
It works fine
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论