如何在HTTP goroutine之间共享MySQL连接？

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

How to share mysql connection between http goroutines?

问题

我是一个Go新手，找不到任何完整的示例来打开Go中的mysql连接，然后在http处理程序之间共享它。这是我目前的代码，我应该如何在main()中使用我打开的db连接在HomeHandler中？

  1. package main
  3. import (
  4. 	"database/sql"
  5. 	"fmt"
  6. 	_ "github.com/go-sql-driver/mysql"
  7. 	"github.com/gorilla/mux"
  8. 	"log"
  9. 	"net/http"
  10. )
  12. var db *sql.DB
  14. func main() {
  15. 	fmt.Println("starting up")
  17. 	var err error
  18. 	db, err = sql.Open("mysql", "root:@/mydb?charset=utf8")
  19. 	if err != nil {
  20. 		log.Fatalf("Error opening database: %v", err)
  21. 	}
  23. 	db.SetMaxIdleConns(100)
  25. 	r := mux.NewRouter()
  26. 	r.HandleFunc("/", HomeHandler)
  28. 	http.Handle("/", r)
  29. 	http.ListenAndServe(":8080", nil)
  30. }
  32. func HomeHandler(w http.ResponseWriter, r *http.Request) {
  33. 	fmt.Fprintf(w, "home")
  34. }

请注意，我在main函数之前声明了一个全局变量db，并在main函数中将其赋值为打开的数据库连接。然后，您可以在HomeHandler函数中使用db变量来执行数据库操作。

英文:

I'm a Go noob and can't find any complete examples of opening a mysql connection in Go and then sharing it among http handlers. Here is my code so far, how would I use the db connection that I opened in main() in my HomeHandler?

  1. package main
  3. import (
  4.   "database/sql"
  5.   "fmt"
  6.   _ "github.com/go-sql-driver/mysql"
  7.   "github.com/gorilla/mux"
  8.   "log"
  9.   "net/http"
  10. )
  12. func main() {
  14.   fmt.Println("starting up")
  16.   db, err := sql.Open("mysql", "root:@/mydb?charset=utf8")
  17.   if err != nil {
  18.   	log.Fatalf("Error opening database: %v", err)
  19.   }
  21.   db.SetMaxIdleConns(100)
  23.   r := mux.NewRouter()
  24.   r.HandleFunc("/", HomeHandler)
  26.   http.Handle("/", r)
  27.   http.ListenAndServe(":8080", nil)
  29. }
  31. func HomeHandler(w http.ResponseWriter, r *http.Request) {
  33.   fmt.Fprintf(w, "home")
  35. }

答案1

得分: 69

数据库/sql包会自动为您管理连接池。

sql.Open(..)返回一个句柄，表示一个连接池，而不是单个连接。如果连接池中的所有连接都忙碌，数据库/sql包会自动打开一个新连接。

应用到您的代码中，这意味着您只需要共享db句柄并在HTTP处理程序中使用它：

  1. package main
  3. import (
  4.     "database/sql"
  5.     "fmt"
  6.     "github.com/gorilla/mux"
  7.     _ "github.com/go-sql-driver/mysql"
  8.     "log"
  9.     "net/http"
  10. )
  12. var db *sql.DB // 全局变量，用于在main函数和HTTP处理程序之间共享
  14. func main() {
  15.     fmt.Println("starting up")
  17.     var err error
  18.     db, err = sql.Open("mysql", "root@unix(/tmp/mysql.sock)/mydb") // 这并不真正打开一个新连接
  19.     if err != nil {
  20.         log.Fatalf("初始化数据库连接时出错：%s", err.Error())
  21.     }
  23.     db.SetMaxIdleConns(100)
  25.     err = db.Ping() // 如果需要，这将打开一个连接。这确保数据库可访问
  26.     if err != nil {
  27.         log.Fatalf("打开数据库连接时出错：%s", err.Error())
  28.     }
  30.     r := mux.NewRouter()
  31.     r.HandleFunc("/", HomeHandler)
  33.     http.Handle("/", r)
  34.     http.ListenAndServe(":8080", nil)
  35. }
  37. func HomeHandler(w http.ResponseWriter, r *http.Request) {
  38.     var msg string
  39.     err := db.QueryRow("SELECT msg FROM hello WHERE page=?", "home").Scan(&msg)
  40.     if err != nil {
  41.         fmt.Fprintf(w, "数据库错误！")
  42.     } else {
  43.         fmt.Fprintf(w, msg)
  44.     }
  45. }
英文:

The database/sql package manages the connection pooling automatically for you.

sql.Open(..) returns a handle which represents a connection pool, not a single connection. The database/sql package automatically opens a new connection if all connections in the pool are busy.

Applied to your code this means, that you just need to share the db-handle and use it in the HTTP handlers:

  1. package main
  3. import (
  4. 	"database/sql"
  5. 	"fmt"
  6. 	"github.com/gorilla/mux"
  7. 	_ "github.com/go-sql-driver/mysql"
  8. 	"log"
  9. 	"net/http"
  10. )
  12. var db *sql.DB // global variable to share it between main and the HTTP handler
  14. func main() {
  15. 	fmt.Println("starting up")
  17. 	var err error
  18. 	db, err = sql.Open("mysql", "root@unix(/tmp/mysql.sock)/mydb") // this does not really open a new connection
  19. 	if err != nil {
  20. 		log.Fatalf("Error on initializing database connection: %s", err.Error())
  21. 	}
  23. 	db.SetMaxIdleConns(100)
  25. 	err = db.Ping() // This DOES open a connection if necessary. This makes sure the database is accessible
  26. 	if err != nil {
  27. 		log.Fatalf("Error on opening database connection: %s", err.Error())
  28. 	}
  30. 	r := mux.NewRouter()
  31. 	r.HandleFunc("/", HomeHandler)
  33. 	http.Handle("/", r)
  34. 	http.ListenAndServe(":8080", nil)
  35. }
  37. func HomeHandler(w http.ResponseWriter, r *http.Request) {
  38. 	var msg string
  39. 	err := db.QueryRow("SELECT msg FROM hello WHERE page=?", "home").Scan(&msg)
  40. 	if err != nil {
  41. 		fmt.Fprintf(w, "Database Error!")
  42. 	} else {
  43. 		fmt.Fprintf(w, msg)
  44. 	}
  45. }

huangapple
  • 本文由 发表于 2013年6月29日 10:47:38
  • 转载请务必保留本文链接：https://go.coder-hub.com/17376207.html
  • go
匿名

发表评论

匿名网友

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

确定