How to Execute a Go Database Query with a Slice in the IN Clause?

Golang Database Query Using Slice IN Clause: A Comprehensive Understanding

Question:

Why does the following Golang database query using a slice of ints in the IN clause fail:

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

Answer:

The issue arises because the database/sql package used for querying does not inspect the query and passes the arguments directly to the database driver. This makes handling queries with IN clauses that contain a variable number of arguments challenging.

In the query provided, the ? bind variable corresponds to a single argument, while the intended use is to bind multiple arguments based on the length of the slice artIds. However, the driver is unable to handle this correctly.

To address this issue, it is recommended to use the sqlx package, which provides greater control over database queries.

Solution Using the sqlx Package:

<code class="go">var artIds = []int{6, 7}
query, args, err := sqlx.In("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (?);", artIds)</code>

The sqlx.In function processes the query and generates the appropriate bind variables for the slice of ints, enabling the query to be executed successfully.

For further information on this topic, refer to the Godoc documentation for InQueries.

The above is the detailed content of How to Execute a Go Database Query with a Slice in the IN Clause?. For more information, please follow other related articles on the PHP Chinese website!