How to Correctly Query MySQL with a Slice of Values Using SQLx?

SQLx Querying MySQL with Values From a Slice

When querying a table in MySQL to retrieve records based on values contained within a slice, using SQLx may present an error. The following article provides a solution to this issue.

Problem

When attempting to query a table using SQLx in the following manner:

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

The error encountered is:

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

Solution

The solution lies in utilizing In() helper function provided by SQLx. By utilizing this function, the query can be prepared by taking arguments and using Rebind(). The corrected code would be:

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

Additional Resource

For further guidance and examples on this topic, refer to the SQLx documentation at: http://jmoiron.github.io/sqlx/

The above is the detailed content of How to Correctly Query MySQL with a Slice of Values Using SQLx?. For more information, please follow other related articles on the PHP Chinese website!