How to use sqlx to query mysql IN a slice?

Question

I want to query a table in mysql database for values IN a slice:

var qids []int
//fill qids dynamically
err = database.SQL.Select(&quotes,
    "SELECT * FROM quote WHERE qid IN $1", qids)
if err != nil {
    log.Println(err)
}

But I get this error:

sql: converting Exec argument #0's type: unsupported type []int, a slice
quotes []

How can I fix this?

Possible duplicate of [Go and IN clause in Postgres](http://stackoverflow.com/questions/38036752/go-and-in-clause-in-postgres); and [Golang MySQL querying undefined amount of args using IN operator](http://stackoverflow.com/questions/39223856/golang-mysql-querying-undefined-amount-of-args-using-in-operator). — icza, Nov 12 '16 at 18:10
@icza I don't think so. This about a feature that is only available in `sqlx`. — TheHippo, Nov 12 '16 at 20:46

Yandry Pozo · Accepted Answer · 2022-05-13T16:24:06.327

sqlx has a great helper for that: In() we just have to prepare the query by taking the args and Rebind, like this:

var qids []int

// fills qids on query dynamically
query, args, err := sqlx.In("SELECT * FROM quote WHERE qid IN (?)", qids)
if err != nil {
    log.Fatal(err)
}

// sqlx.In returns queries with the `?` bindvar, we can rebind it for our backend
//
query = database.SQL.Rebind(query)  // database.SQL should be a *sqlx.DB

err = database.SQL.Select(&quotes, query, args...)
if err != nil {
    log.Fatal(err)
}

// or just in one line:

err = database.SQL.Select(&quotes, database.SQL.Rebind(query), args...)

Also I recommend you take a look here: http://jmoiron.github.io/sqlx/ there're a lot of examples including IN

It is much more convoluted that I wished to be, but works nonetheless. Thanks. — Karlom, Nov 13 '16 at 01:13

score -2 · Answer 2 · answered Nov 12 '16 at 20:50

-2

The tags suggest that you are using sqlx. It has support for IN in queries.

So you can do

var qids []int
//fill qids dynamically
rows, err = db.Query(&quotes, "SELECT * FROM quote WHERE qid IN ($1)", qids)
if err != nil {
    log.Println(err)
}
// scan the rows

answered Nov 12 '16 at 20:50

TheHippo

61,720
15
75
100

Yes I use `sqlx` but not sure why I still get the same error. – Karlom Nov 12 '16 at 21:00
this one worked for me, just replace `$1` with `?` if you are using mysql. – Kokizzu Dec 04 '21 at 21:29

score -3 · Answer 3 · edited Oct 27 '19 at 22:54

Hey its because of []int
try this

type Int64Array []int64

// Value returns the driver compatible value
func (a Int64Array) Value() (driver.Value, error) {
var strs []string
for _, i := range a {
    strs = append(strs, strconv.FormatInt(i, 10))
}
return "{" + strings.Join(strs, ",") + "}", nil
}

and then pass int64 to query

db.Queryx("SELECT * FROM quote WHERE qid IN $1", int64IDs)

for more detail check here

How to use sqlx to query mysql IN a slice?

3 Answers3

Linked