Recently, I encountered a small problem in my work, that is, how to use the native sql query where in statement, because I used to use gorm before, gorm has been packaged, and suddenly I am a bit unfamiliar with the native one, and at the same time, I have to consider whether the performance and code It is cumbersome, so write this note to record several solutions at that time.

The range I pass in to in is an array, and the type of the value inside is int64, for example as follows:

idSlice := []int{1, 2, 3, 4, 5, 6, 7}

The normal sql statement is written like this:

select * from table where id in (1, 2, 3, 4, 5, 6, 7);

So I took it for granted that I also wrote the code like this:

idSlice := []int{1, 2, 3, 4, 5, 6, 7}
query := fmt.Sprintf("select * from table where id in (?)", idSlice)
result, err := db.Query(query)

The above actually made two mistakes,

 That is to say, native sql can’t recognize (?), which may be because gorm has already packaged it, so after many trials and errors and thinking, I realized that the range of in should be written in the form of a string. After all, the entire SQL is regarded as a native query statement, so variables and the like cannot appear.
 idSlice is an array type. If you change (?) to (%v), it will become in [1, 2, 3, 4, 5, 6, 7], which is also not possible

So I summed up several ways to solve these problems:

idSlice := []int{1, 2, 3, 4, 5, 6, 7}
var s []string
for i := 0; i < len(idSlice); i++ {
  s = append(s, strconv.Itoa(idSlice[i])) //Turn each element into a string type
}
var ss string
ss = strings.Join(s, "','")	//The s filled in here must be an array of string type, so it needs to be converted to string type before
//The ss at this time is：1','2','3','4','5','6','7
query := fmt.Sprintf("select * from table where id in ('%s')", ss)
//after combination：('1','2','3','4','5','6','7')
result, err := db.Query(query)

The problem with this method is that the strings.Joins function is used, and the first value of the function must be a value of the string type, so a for loop is needed to perform a conversion. At the same time, the strings.Joins function itself also has a for loop inside, which adds up here There are two for loops, which will have an impact on the overall performance.

idSlice := []int{1, 2, 3, 4, 5, 6, 7}
var ss string
for i := 0; i < len(idSlice); i++ {
  if i == 0 {
    s := fmt.Sprintf("'%d'", idSlice[i])
		ss += s
	} else {
		s := fmt.Sprintf(",'%d'", idSlice[i])
		ss += s
	}
}
query := fmt.Sprintf("select * from table where id in (%s)", ss)
result, err := db.Query(query)