Go: how to retrieve multiple results from mysql with sql/db package

huangapple
go评论127阅读模式
英文:

Go: how to retrieve multiple results from mysql with sql/db package

问题

我正在尝试使用Go语言中的sql/db包从表中获取数据。我的数据库表中包含一个用户名为"shaw"的用户。该用户名在post列中有几行不同的帖子。我有以下代码：

GO：

  1. func ReadData() string {
  2.     db, err := sql.Open("mysql", "user1@/my_db")
  3.     if err != nil {
  4.         fmt.Println(err)
  5.     }
  6.     defer db.Close()
  8.     var tweet string
  10.     rows, err := db.Query("select tweet from posts where username = ?", "shaw")
  11.     if err != nil {
  12.         fmt.Println(err)
  13.     }
  14.     defer rows.Close()
  16.     for rows.Next() {
  17.         err := rows.Scan(&tweet)
  18.         if err != nil {
  19.             fmt.Println(err)
  20.         }
  21.         fmt.Printf("this %s", tweet)
  22.         return tweet
  23.     }
  24.     return ""
  25. }

显示的结果只给我返回了表中的一个值，而不是与"Shaw"相关的其他值。如何获取多个结果？

根据这里的文档，似乎没有什么问题... https://code.google.com/p/go-wiki/wiki/SQLInterface

英文:

I'm trying to fetch data from a table using the sql/db package in Go. My database table contains a username "shaw". There are a few rows with the username shaw with different posts in the post column attached to it. I have the following code:

GO:

  1. func ReadData() string {
  2. 	db, err := sql.Open("mysql", "user1@/my_db")
  3. 	if err != nil {
  4. 		fmt.Println(err)
  5. 	}
  6. 	defer db.Close()
  8. 	var tweet string
  10. 	rows, err := db.Query("select tweet from posts where username = ?", "shaw")
  11. 	if err != nil {
  12. 		fmt.Println(err)
  13. 	}
  14. 	defer rows.Close()
  16. 	for rows.Next() {
  17. 		err := rows.Scan(&tweet)
  18. 		if err != nil {
  19. 			fmt.Println(err)
  20. 		}
  21. 		fmt.Printf("this %s", tweet)
  22. 		return tweet
  23. 	}
  24. 	return ""
  25. }

The result shown only gives me 1 value from the table and not the other values associate with "Shaw". How do I fetch more than 1 result?

According to the documentation here there doesn't seem to be anything wrong.. https://code.google.com/p/go-wiki/wiki/SQLInterface

答案1

得分: 4

因为你只返回了第一个推文并退出函数，要返回多个推文，你需要返回一个切片，例如：

  1. func ReadData() (res []string) {
  2.     db, err := sql.Open("mysql", "user1@/my_db")
  3.     if err != nil {
  4.         fmt.Println(err)
  5.     }
  6.     defer db.Close()
  8.     rows, err := db.Query("select tweet from posts where username = ?", "shaw")
  9.     if err != nil {
  10.         fmt.Println(err)
  11.         return
  12.     }
  13.     defer rows.Close()
  15.     var tweet string
  16.     for rows.Next() {
  17.         err := rows.Scan(&tweet)
  18.         if err != nil {
  19.             fmt.Println(err)
  20.             return
  21.         }
  22.         fmt.Printf("this %s", tweet)
  23.         res = append(res, tweet)
  24.     }
  25.     return
  26. }
英文:

Because you return the first tweet and exit the function, to return multiple tweets you have to return a slice, for example:

  1. func ReadData() (res []string) {
  2. 	db, err := sql.Open("mysql", "user1@/my_db")
  3. 	if err != nil {
  4. 		fmt.Println(err)
  5. 	}
  6. 	defer db.Close()
  8. 	rows, err := db.Query("select tweet from posts where username = ?", "shaw")
  9. 	if err != nil {
  10. 		fmt.Println(err)
  11. 		return
  12. 	}
  13. 	defer rows.Close()
  15. 	var tweet string
  16. 	for rows.Next() {
  17. 		err := rows.Scan(&tweet)
  18. 		if err != nil {
  19. 			fmt.Println(err)
  20. 			return
  21. 		}
  22. 		fmt.Printf("this %s", tweet)
  23. 		res = append(res, tweet)
  24. 	}
  25. 	return
  26. }

答案2

得分: 1

OneOfOne的答案很好，但你可能想要更改函数，使其不返回切片，而是将推文发送到一个通道中。这样，你可以在所有推文都发送完之前开始消费结果。在这种情况下，你可能需要像这样的代码：

  1. func ReadData() (res chan<- string) {
  2.     db, err := sql.Open("mysql", "user1@/my_db")
  3.     if err != nil {
  4.         fmt.Println(err)
  5.     }
  7.     rows, err := db.Query("select tweet from posts where username = ?", "shaw")
  8.     if err != nil {
  9.         fmt.Println(err)
  10.         db.Close()
  11.         close(res)
  12.         return
  13.     }
  14.     go func(res chan<- string) {
  15.         defer db.Close()
  16.         defer rows.Close()
  17.         defer close(res)
  18.         var tweet string
  19.         for rows.Next() {
  20.             err := rows.Scan(&tweet)
  21.             if err != nil {
  22.                 fmt.Println(err)
  23.                 return
  24.             }
  25.             fmt.Printf("this %s", tweet)
  26.             res <- tweet
  27.         }
  28.         return
  29.     }(res)
  30. }

然后，你可以通过更改输入res chan<- string的缓冲区来控制数据库缓冲。根据你的应用程序，你可能不想关闭结果 - 这取决于你。

英文:

OneOfOne's answer is good, but you might want to change the function so that instead of returning a slice, it sends the tweets on a channel. That way, you can start consuming results before they are all done being sent. In that case, you'd want something like:

  1. func ReadData() (res chan&lt;- string) {
  2.     db, err := sql.Open(&quot;mysql&quot;, &quot;user1@/my_db&quot;)
  3.     if err != nil {
  4.         fmt.Println(err)
  5.     }
  7.     rows, err := db.Query(&quot;select tweet from posts where username = ?&quot;, &quot;shaw&quot;)
  8.     if err != nil {
  9.         fmt.Println(err)
  10.         db.Close()
  11.         close(res)
  12.         return
  13.     }
  14.     go func(res chan&lt;- string) {
  15.         defer db.Close()
  16.         defer rows.Close()
  17.         defer close(res)
  18.         var tweet string
  19.         for rows.Next() {
  20.             err := rows.Scan(&amp;tweet)
  21.             if err != nil {
  22.                 fmt.Println(err)
  23.                 return
  24.             }
  25.             fmt.Printf(&quot;this %s&quot;, tweet)
  26.             res &lt;- tweet
  27.         }
  28.         return
  29.     }(res)
  30. }

Then, you can control the database buffering by changing the buffer of the input res chan&lt;- string. Depending on your application you might not want to close the result - that's up to you.

huangapple
  • 本文由 发表于 2014年8月14日 22:53:25
  • 转载请务必保留本文链接：https://go.coder-hub.com/25311162.html
  • go
  • mysql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定