Why Does My Go Database Query with an IN Clause Fail When Using a Slice of Integers?

The Enigma of Database IN Clauses in Go

Database queries with IN clauses are a common necessity, yet their implementation in Go has left many developers puzzled. When attempting to execute a query using a slice of integers as the IN clause parameter, some solutions may seem counterintuitive.

In the provided example, the query fails when using a string representation of the slice as the argument for the IN clause:

<code class="go">inq := "6,7" //strings.Join(artIds, ",")
rows, err = db.Query("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (?)", inq)</code>

However, this approach succeeds:

<code class="go">rows, err = db.Query("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (6,7)", inq)</code>

The reason for this discrepancy lies in the behavior of the database/sql package. It does not inspect the query and passes arguments directly to the database driver. Hence, the bindvar (?) in the query corresponds to a single argument, not a variable number of arguments as desired.

<code class="go">var levels = []int{4, 6, 7}
rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)</code>

Fortunately, the sqlx package provides a more graceful solution to this problem. Using sqlx.In, one can process the query before execution:

<code class="go">query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels)</code>

For more comprehensive guidance, refer to the Godoc documentation on InQueries.

The above is the detailed content of Why Does My Go Database Query with an IN Clause Fail When Using a Slice of Integers?. For more information, please follow other related articles on the PHP Chinese website!