SQL + goLang-运行带有IN条件的select语句-并将其传递给ID数组？

Question

I want to perform the following logic -

SELECT some_column
FROM table_name
WHERE id IN (value1, value2, ...);

in my go code I have the following int array to represent my ids :

idsToGet := []int{1,2}

I fully understand that you might normally do a sprintf and pass in the one id you were looking for... BUT...

I feel like there is a sensible way to do this without iterating over the array, and doing this as a separate call for each ID... via sprintf - is there a way I can do this as just one call - and have my IN clause auto contain everything from my array?

Totally new to Go - been trying to solve this for an hour, I have it working for single id's - but not for multiples.

Answer 1

You don't want to use Sprintf to put raw values into SQL, that's a bad habit. What you want to do is build a bit of SQL with the right number of placeholders and then let the library take care of replacing the placeholders with their value.

If you have two ints in your array then you want to build this:

SELECT some_column
FROM table_name
WHERE id IN (?, ?)

If you have four then you want to build:

SELECT some_column
FROM table_name
WHERE id IN (?, ?, ?, ?)

and so on. All you need is a simple function that can produce n placeholders; there are lots of ways to do this:

func placeholders(n int) string {
    ps := make([]string, n)
    for i := 0; i < n; i++ {
        ps[i] = "?"
    }
    return strings.Join(ps, ",")
}

or maybe:

func placeholders(n int) string {
    var b strings.Builder
    for i := 0; i < n - 1; i++ {
        b.WriteString("?,")
    }
    if n > 0 {
        b.WriteString("?")
    }
    return b.String()
}

or whatever is clearest to you, the implementation is unlikely to have any measurable impact as long as it is correct.

Then you can say things like:

query := fmt.Sprintf("select some_column from table_name where id in (%s)", placeholders(len(idsToGet)))
rows, err := db.Query(query, idsToGet...)

and spin throw rows in the usual manner.