dongnaoben4456
dongnaoben4456
2018-07-26 12:18
浏览 103

使用slice IN子句的Golang数据库查询

Can someone explain to me why this does not work?

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)

And this does

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)

I'm trying to do a simple IN clause with a slice of ints, and every solution suggested doesn't seem very idiomatic

Tried to do this, but the problem appears to be the string substitution.

inq := strings.Join(artIds, ",")

I'm a bit surprised that go doesn't seem to have a graceful way to handle this query.

图片转代码服务由CSDN问答提供 功能建议

有人可以向我解释为什么这行不通吗?

  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)
   
 
 

这确实

 行,err = db.Query(” 从tags_for_articles的LEFT JOIN标签中选择DISTINCT标题,放在tags.id = tags_for_articles.tag_id WHERE article_id IN(6,7)“,inq)
   
 
 

我正在尝试 用一个整数切片做一个简单的IN子句,建议的每个解决方案似乎都不是惯用语

试图这样做,但是问题似乎出在字符串替换上。

  inq:= strings.Join(artIds,“,”)
   
 
 

我对go不会感到惊讶 似乎没有一种优雅的方式来处理此查询。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongyuhui0418
    dongyuhui0418 2018-07-26 19:38
    已采纳

    If you have been careful to build your inq string from real ints (to avoid injection), you can just build the string yourself and avoid using ?:

    inq := "6,7" 
    sql := fmt.Sprintf("SELECT DISTINCT title FROM tags_for_articles LEFT JOIN tags ON tags.id = tags_for_articles.tag_id WHERE article_id IN (%s)",inq)
    rows, err := db.Query(sql)
    

    If you do it a lot, better to have a WhereIn function that does this for you, or use an orm. Be careful which args you accept though, as if you accept arbitrary strings anything could be injected.

    点赞 评论
  • dongnan4571
    dongnan4571 2018-07-26 12:35

    Because database/sql does not inspect your query and it passes your arguments directly to the driver, it makes dealing with queries with IN clauses difficult:

    SELECT * FROM users WHERE level IN (?);
    

    When this gets prepared as a statement on the backend, the bindvar ? will only correspond to a single argument, but what is often desired is for that to be a variable number of arguments depending on the length of some slice

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

    There is a way to handle these types of queries using sqlx package which provide more control over database queries.

    This pattern is possible by first processing the query with sqlx.In:

    var levels = []int{4, 6, 7}
    query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels)
    

    For more information Go through Godoc for InQueries

    点赞 评论

相关推荐