Golang Connection MySQL operation and dynamic connection pooling settings

This is a creation in Article, where the information may have evolved or changed.

The Golang itself does not provide a driver to connect to MySQL, but it defines a standard interface for third-party development drivers. Here to connect MySQL can use third-party libraries, third-party libraries recommend the use of Https://github.com/Go-SQL-Driver/MySQL this driver, update maintenance is better. The following shows the specific use, the complete code example can refer to the last.

Download driver

sudo go get github.com/go-sql-driver/mysql

Database connection

db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname?charset=utf8")

The connection parameters can be in the following ways: Usually we use the second one.

user@unix(/path/to/socket)/dbname?charset=utf8user:password@tcp(localhost:5555)/dbname?charset=utf8user:password@/dbnameuser:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname

Insert operation

stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)checkErr(err)res, err := stmt.Exec("tony", 20, 1)checkErr(err)id, err := res.LastInsertId()checkErr(err)fmt.Println(id)

Using structured operations here, it is not recommended to use a method that directly stitching SQL statements.

Query operations

rows, err := db.Query("SELECT * FROM user")checkErr(err)for rows.Next() {    var userId int    var userName string    var userAge int    var userSex int    rows.Columns()    err = rows.Scan(&userId, &userName, &userAge, &userSex)    checkErr(err)    fmt.Println(userId)    fmt.Println(userName)    fmt.Println(userAge)    fmt.Println(userSex)}

The query here uses the declaration of 4 independent variables userid, UserName, Userage, usersex to save the values of each row queried. The operation of the database is typically encapsulated in real-world development, and queries such as this typically take into account the return dictionary type.

//构造scanArgs、values两个数组，scanArgs的每个值指向values相应值的地址columns, _ := rows.Columns()scanArgs := make([]interface{}, len(columns))values := make([]interface{}, len(columns))for i := range values {    scanArgs[i] = &values[i]}for rows.Next() {    //将行数据保存到record字典    err = rows.Scan(scanArgs...)    record := make(map[string]string)    for i, col := range values {        if col != nil {            record[columns[i]] = string(col.([]byte))        }    }    fmt.Println(record)}

Modify Operation

stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)checkErr(err)res, err := stmt.Exec(21, 2, 1)checkErr(err)num, err := res.RowsAffected()checkErr(err)fmt.Println(num)

Delete operation

stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)checkErr(err)res, err := stmt.Exec(1)checkErr(err)num, err := res.RowsAffected()checkErr(err)fmt.Println(num)

Both the modify and delete operations are simple, similar to inserting data, using only rowsaffected to get the number of rows affected.

Full code

Package Mainimport ("Database/sql" "Fmt" _ "Github.com/go-sql-driver/mysql") func main () {insert ()}//Insert Demofu NC Insert () {db, err: = SQL. Open ("MySQL", "Root:@/test?charset=utf8") Checkerr (Err) stmt, err: = db. Prepare (' INSERT user (User_name,user_age,user_sex) VALUES (?,?,?) ') Checkerr (ERR) res, err: = stmt. Exec ("Tony", 1) checkerr (ERR) ID, err: = Res. Lastinsertid () Checkerr (err) fmt. Println (ID)}//queries demofunc query () {db, err: = SQL. Open ("MySQL", "Root:@/test?charset=utf8") Checkerr (Err) rows, err: = db. Query ("SELECT * from User") Checkerr (ERR)//Normal demo//for rows. Next () {//var userId int//var userName string//var userage int//var usersex int//R oWS. Columns ()//err = rows. Scan (&userid, &username, &userage, &usersex)//Checkerr (ERR)//FMT. PRINTLN (userId)//FMT. PRINTLN (UserName)//FMT. PRINTLN (userage)//FMT. PRINTLN (Usersex)//}   Dictionary type//construct Scanargs, values two arrays, each value of Scanargs points to the address of values corresponding columns, _: = Rows. Columns () Scanargs: = Make ([]interface{}, Len (Columns)) Values: = Do ([]interface{}, Len (Columns)) for I: = Rang E values {scanargs[i] = &values[i]} for rows. Next () {//saves row data to the record dictionary err = rows.        Scan (Scanargs ...) Record: = Make (map[string]string) for I, col: = range values {if col! = Nil {Record[col Umns[i]] = string (col. ([]byte))}} FMT. Println (record)}}//Update data func update () {db, err: = SQL. Open ("MySQL", "Root:@/test?charset=utf8") Checkerr (Err) stmt, err: = db. Prepare (' UPDATE user SET user_age=?,user_sex=? WHERE user_id=? ') Checkerr (ERR) res, err: = stmt. Exec (2, 1) checkerr (err) num, err: = Res. Rowsaffected () Checkerr (err) fmt. PRINTLN (num)}//Delete data func remove () {db, err: = SQL. Open ("MySQL", "Root:@/test?charset=utf8") Checkerr (Err) stmt, err: = db. PrEpare (' DELETE from user WHERE user_id=? ') Checkerr (ERR) res, err: = stmt. Exec (1) checkerr (err) num, err: = Res. Rowsaffected () Checkerr (err) fmt. PRINTLN (num)}func checkerr (err error) {if err! = Nil {panic (err)}}

Golang go-sql-drive MySQL Connection pool implementation

Golang internal with the connection pool function, just start contact Golang When do not know this, also made a SQL. Open Object Management pool, really very embarrassing ah.

Sql. The Open function actually returns a connection pool object, not a single connection. When the open is not connected to the database, only when the query, Exce method to actually connect to the database. In one application, the same library connection requires only one SQL to be saved. The DB object after open is ready and does not require multiple open.

The resource is freed after the normal program executes, so try using a Web service to demonstrate it.

Turn on Web Services

First start a Web service to listen to 9090 port, relatively simple to do not more instructions.

func startHttpServer() {    http.HandleFunc("/pool", pool)    err := http.ListenAndServe(":9090", nil)    if err != nil {        log.Fatal("ListenAndServe: ", err)    }}

DB Object Initialization

Declares a global DB object and initializes it.

var db *sql.DBfunc init() {    db, _ = sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?charset=utf8")    db.SetMaxOpenConns(2000)    db.SetMaxIdleConns(1000)    db.Ping()}

The key to the implementation of connection pooling is Setmaxopenconns and Setmaxidleconns, where:
The Setmaxopenconns is used to set the maximum number of open connections, and the default value of 0 means no limit.
The Setmaxidleconns is used to set the number of idle connections.

By setting the maximum number of connections, you can avoid too many connections errors that occur when you connect to MySQL with too high concurrency. Set the number of idle connections when a connection is opened, it can be placed in the pool for the next use.

Request method

The HTTP request above sets the execution method of the request/pool address.

func pool(w http.ResponseWriter, r *http.Request) {    rows, err := db.Query("SELECT * FROM user limit 1")    defer rows.Close()    checkErr(err)    columns, _ := rows.Columns()    scanArgs := make([]interface{}, len(columns))    values := make([]interface{}, len(columns))    for j := range values {        scanArgs[j] = &values[j]    }    record := make(map[string]string)    for rows.Next() {        //将行数据保存到record字典        err = rows.Scan(scanArgs...)        for i, col := range values {            if col != nil {                record[columns[i]] = string(col.([]byte))            }        }    }    fmt.Println(record)    fmt.Fprintln(w, "finish")}func checkErr(err error) {    if err != nil {        fmt.Println(err)        panic(err)    }}

The pool method is to find a record from the user table and store it in the map, and finally output the finish. The code is over here. Very simple, test it below. Start the HTTP service first, and then use AB for concurrent test access:

ab -c 100 -n 1000 'http://localhost:9090/pool'

View the connection process through show processlist in the database:

Golang Database Connection Pool

You can see that there are 100 or so processes.

The use of connection pooling can significantly improve performance because it avoids duplicate creation of the connection. Interested child boots can remove the connection pool code and test it yourself. The complete code is as follows:

Database connection Pool Test package mainimport ("Database/sql" "Fmt" _ "Github.com/go-sql-driver/mysql" "Log" "Net/http") var DB *sql. Dbfunc init () {db, _ = sql. Open ("MySQL", "Root: @tcp (127.0.0.1:3306)/test?charset=utf8") db. Setmaxopenconns (+) db. Setmaxidleconns (+) db. Ping ()}func main () {starthttpserver ()}func starthttpserver () {http. Handlefunc ("/pool", pool) Err: = http. Listenandserve (": 9090", nil) if err! = Nil {log. Fatal ("Listenandserve:", err)}}func Pool (w http. Responsewriter, R *http. Request) {rows, err: = db. Query ("SELECT * from User Limit 1") defer rows. Close () Checkerr (Err) columns, _: = Rows. Columns () Scanargs: = Make ([]interface{}, Len (Columns)) Values: = Make ([]interface{], Len (Columns)) for j: = Rang E values {scanargs[j] = &values[j]} record: = Make (map[string]string) for rows. Next () {//saves row data to the record dictionary err = rows.        Scan (Scanargs ...)        For I, col: = Range Values {    If col! = nil {Record[columns[i]] = string (col. ([]byte))}}} fmt. Println (record) fmt. Fprintln (W, "Finish")}func Checkerr (err error) {if err! = Nil {fmt. PRINTLN (ERR) Panic (ERR)}}

Summary

Golang the connection pool implemented here provides only the Setmaxopenconns and Setmaxidleconns methods for connection pooling configuration. One problem with the process is that the database itself has a time-out setting for the connection, and if the time-out is reached, the database will unilaterally disconnect, and then access with the connection in the connection pool will be an error.

packets.go:32: unexpected EOFpackets.go:118: write tcp 192.168.3.90:3306: broken pipe

All errors above are the output of the go-sql-drive itself, and sometimes bad connection errors occur. After several requests, the connection pool will reopen the new connection. This is no problem.