在MySQL查询中使用WHERE IN子句的Golang切片。

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

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') andtable.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

常见问题:

  1. aID确实包含所有这些数字作为字符串,并且

  2. 表中有提供的上述id的所有行。

  3. 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 :

  1. aID does contain all those numbers as string and

  2. table has all the rows available with provided above id.

  3. table is from where id is fetched and appended to aID and another record with id stored in aID are fetched with in statement from table2.

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 &lt; n; i++ {
		p[i] = &quot;?&quot;
		parameters[i] = values[i]
	}
	placeholders = strings.Join(p, &quot;,&quot;)
	return placeholders, parameters
}

and calling the function like this

placeholders, params := GetPlaceholders(&quot;1&quot;, &quot;2&quot;, &quot;3&quot;)

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(&quot;SELECT * from table2 where id in (%s)&quot;, asID)

this will result in SELECT * from table2 where id in (1,2,3,4...) as opposed to SELECT * from table2 where id in (&#39;1,2,3,4...&#39;)

答案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{&quot;1&quot;, &quot;2&quot;, &quot;3&quot;}
ids := strings.Join(idAry, &quot;&#39;,&#39;&quot;)
sqlRaw := fmt.Sprintf(`SELECT * FROM table WHERE id IN (&#39;%s&#39;)`, ids)
rows, err := db.Query(sqlRaw)

It works fine

huangapple
  • 本文由 发表于 2017年7月27日 21:03:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/45351644.html
匿名

发表评论

匿名网友

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

确定