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

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

How to share mysql connection between http goroutines?

问题

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

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/gorilla/mux"
	"log"
	"net/http"
)

var db *sql.DB

func main() {
	fmt.Println("starting up")

	var err error
	db, err = sql.Open("mysql", "root:@/mydb?charset=utf8")
	if err != nil {
		log.Fatalf("Error opening database: %v", err)
	}

	db.SetMaxIdleConns(100)

	r := mux.NewRouter()
	r.HandleFunc("/", HomeHandler)

	http.Handle("/", r)
	http.ListenAndServe(":8080", nil)
}

func HomeHandler(w http.ResponseWriter, r *http.Request) {
	fmt.Fprintf(w, "home")
}

请注意,我在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?

package main

import (
  "database/sql"
  "fmt"
  _ "github.com/go-sql-driver/mysql"
  "github.com/gorilla/mux"
  "log"
  "net/http"
)

func main() {

  fmt.Println("starting up")

  db, err := sql.Open("mysql", "root:@/mydb?charset=utf8")
  if err != nil {
  	log.Fatalf("Error opening database: %v", err)
  }

  db.SetMaxIdleConns(100)

  r := mux.NewRouter()
  r.HandleFunc("/", HomeHandler)

  http.Handle("/", r)
  http.ListenAndServe(":8080", nil)

}

func HomeHandler(w http.ResponseWriter, r *http.Request) {

  fmt.Fprintf(w, "home")

}

答案1

得分: 69

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

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

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

package main

import (
    "database/sql"
    "fmt"
    "github.com/gorilla/mux"
    _ "github.com/go-sql-driver/mysql"
    "log"
    "net/http"
)

var db *sql.DB // 全局变量,用于在main函数和HTTP处理程序之间共享

func main() {
    fmt.Println("starting up")

    var err error
    db, err = sql.Open("mysql", "root@unix(/tmp/mysql.sock)/mydb") // 这并不真正打开一个新连接
    if err != nil {
        log.Fatalf("初始化数据库连接时出错:%s", err.Error())
    }

    db.SetMaxIdleConns(100)

    err = db.Ping() // 如果需要,这将打开一个连接。这确保数据库可访问
    if err != nil {
        log.Fatalf("打开数据库连接时出错:%s", err.Error())
    }

    r := mux.NewRouter()
    r.HandleFunc("/", HomeHandler)

    http.Handle("/", r)
    http.ListenAndServe(":8080", nil)
}

func HomeHandler(w http.ResponseWriter, r *http.Request) {
    var msg string
    err := db.QueryRow("SELECT msg FROM hello WHERE page=?", "home").Scan(&msg)
    if err != nil {
        fmt.Fprintf(w, "数据库错误!")
    } else {
        fmt.Fprintf(w, msg)
    }
}
英文:

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:

package main

import (
	"database/sql"
	"fmt"
	"github.com/gorilla/mux"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"net/http"
)

var db *sql.DB // global variable to share it between main and the HTTP handler

func main() {
	fmt.Println("starting up")

	var err error
	db, err = sql.Open("mysql", "root@unix(/tmp/mysql.sock)/mydb") // this does not really open a new connection
	if err != nil {
		log.Fatalf("Error on initializing database connection: %s", err.Error())
	}

	db.SetMaxIdleConns(100)

	err = db.Ping() // This DOES open a connection if necessary. This makes sure the database is accessible
	if err != nil {
		log.Fatalf("Error on opening database connection: %s", err.Error())
	}

	r := mux.NewRouter()
	r.HandleFunc("/", HomeHandler)

	http.Handle("/", r)
	http.ListenAndServe(":8080", nil)
}

func HomeHandler(w http.ResponseWriter, r *http.Request) {
	var msg string
	err := db.QueryRow("SELECT msg FROM hello WHERE page=?", "home").Scan(&msg)
	if err != nil {
		fmt.Fprintf(w, "Database Error!")
	} else {
		fmt.Fprintf(w, msg)
	}
}

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

发表评论

匿名网友

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

确定