Go operation MySQL

Catalog

One 、 Use the bag database/sql

1、DB（ Database objects ）

2、Results and Result（ Result set ）

3、Statements（ sentence ）

Two 、 Additions and deletions Exec() Method

3、 ... and 、Query() Methods use

Four 、Get() Methods use

5、 ... and 、Select() Methods use

6、 ... and 、 Connection pool

1、 Connection pool description

2、 Connection pool configuration

3、 Number of database connection retries

---

One 、 Use the bag database/sql

database/sql yes Go One of the standard libraries for operating databases , It provides a series of interface methods , Used to access the database （mysql,sqllite,oralce,postgresql）, It doesn't provide database specific methods , Those special methods are implemented by the database driver

And usually at work , We use more  https://github.com/jmoiron/sqlx Package to operate the database ,sqlx Is based on the standard library sql An extension of , And we can sqlx Manipulate various types of data , Such as converting the queried data into a structure

github Address ：

• https://github.com/go-sql-driver/mysql
• GitHub - jmoiron/sqlx: general purpose extensions to golang's database/sql

install ：

go get "github.com/go-sql-driver/mysql"
go get "github.com/jmoiron/sqlx"

sqlx The library provides some types , It is very important to master the usage of these types

1、DB（ Database objects ）

        sql.DB The type represents the database , When operating databases in other languages , Need to create a connection , about Go In other words, you need to create a database type , It's not a database connection ,Go The connections in come from the connection pool implemented internally , The establishment of connections is inert , The connection will be in operation , Created and maintained by connection pools

         Use sql.Open Function to create a database type , The first is the database driver name , The second is the string of connection information

var Db *sqlx.DB
db, err := sqlx.Open("mysql","username:password@tcp(ip:port)/database?charset=utf8")
Db = db

2、Results and Result（ Result set ）

newly added 、 to update 、 Delete ; Different from the method used for query , All types are also different

• Result yes newly added 、 to update 、 The result set returned when deleting
• Results Is the result set when querying the database ,sql.Rows Type represents the result set of multiple rows of data returned by the query ,sql.Row Represents the result set of a single line query

3、Statements（ sentence ）

sql.Stmt Type said sql sentence , for example DDL,DML And so on sql sentence , Can be taken as prepare Statement constructs a query , It can also be used directly sql.DB The function of

Two 、 Additions and deletions Exec() Method

func (db *DB) Exec(query string, args ...interface{}) (Result, error)

        Exec and MustExec Get a connection from the connection pool and point to the corresponding query operation , For not supporting ad-hoc query execution The driver , Behind the execution of the operation, a prepared statement, Before the results return , This connection Will return to the connection pool .

It should be noted that , Different databases , Use different placeholders ,mysql use ？ As placeholder

• Mysql Use ?
• PostgreSQL Use 1,1,2 wait
• SQLLite Use ? or $1
• Oracle Use :name        （ Pay attention to the colons ）

package main
 
import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)
 
var (
    userName  string = "chenkai"
    password  string = "chenkai"
    ipAddrees string = "192.168.0.115"
    port      int    = 3306
    dbName    string = "test"
    charset   string = "utf8"
)
 
func connectMysql() (*sqlx.DB) {
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
    Db, err := sqlx.Open("mysql", dsn)
    if err != nil {
        fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
    }
    return Db
}
 
func addRecord(Db *sqlx.DB) {
    for i:=0; i<2; i++ {
        result, err := Db.Exec("insert into userinfo  values(?,?,?,?,?,?)",0, "2019-07-06 11:45:20", "johny", "123456", " Technology Department ", "[email protected]")
        if err != nil {
            fmt.Printf("data insert faied, error:[%v]", err.Error())
            return
        }
        id, _ := result.LastInsertId()
        fmt.Printf("insert success, last id:[%d]\n", id)
    }
}
 
func updateRecord(Db *sqlx.DB){
    // to update uid=1 Of username
    result, err := Db.Exec("update userinfo set username = 'anson' where uid = 1")
    if err != nil {
        fmt.Printf("update faied, error:[%v]", err.Error())
        return
    }
    num, _ := result.RowsAffected()
    fmt.Printf("update success, affected rows:[%d]\n", num)
}
 
func deleteRecord(Db *sqlx.DB){
    // Delete uid=2 The data of 
    result, err := Db.Exec("delete from userinfo where uid = 2")
    if err != nil {
        fmt.Printf("delete faied, error:[%v]", err.Error())
        return
    }
    num, _ := result.RowsAffected()
    fmt.Printf("delete success, affected rows:[%d]\n", num)
}
 
 
func main() {
    var Db *sqlx.DB = connectMysql()
    defer Db.Close()
 
    addRecord(Db)
    updateRecord(Db)
    deleteRecord(Db)
}
 
 Running results ：
API server listening at: 127.0.0.1:59899
insert success, last id:[1]
insert success, last id:[2]
update success, affected rows:[1]
delete success, affected rows:[1]

3、 ... and 、Query() Methods use

 

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

Query() Method returns a sql.Rows Result set of type , It can also be used to query the data of multiple fields , However, you need to define variables with multiple fields to receive , Iteration of the latter Next() Method , And then use Scan() Method to assign a value to a variable of the corresponding type , In order to take out the results , Finally, close the result set （ Release the connection ）

package main
import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)
 
 
var (
    userName  string = "chenkai"
    password  string = "chenkai"
    ipAddrees string = "192.168.0.115"
    port      int    = 3306
    dbName    string = "test"
    charset   string = "utf8"
)
 
func connectMysql() (*sqlx.DB) {
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
    Db, err := sqlx.Open("mysql", dsn)
    if err != nil {
        fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
    }
    return Db
}
 
func queryData(Db *sqlx.DB) {
    rows, err := Db.Query("select * from userinfo")
    if err != nil {
        fmt.Printf("query faied, error:[%v]", err.Error())
        return
    }
    for rows.Next() {
        // Define variables to receive query data 
        var uid int
        var create_time, username, password, department, email string
 
        err := rows.Scan(&uid, &create_time, &username, &password, &department, &email)
        if err != nil {
            fmt.Println("get data failed, error:[%v]", err.Error())
        }
        fmt.Println(uid, create_time, username, password, department, email)
    }
     
    // Close result set （ Release the connection ）
    rows.Close()
}
 
func main() {
    var Db *sqlx.DB = connectMysql()
    defer Db.Close()
 
    queryData(Db)
}
 
 Running results ：
1 2019-07-06 11:45:20 anson 123456  Technology Department  [email protected]
3 2019-07-06 11:45:20 johny 123456  Technology Department  [email protected]
4 2019-07-06 11:45:20 johny 123456  Technology Department  [email protected]

Four 、Get() Methods use

func (db *DB) Get(dest interface{}, query string, args ...interface{}) error

A record to be queried , Save to structure

explain ： The field name of the structure must be capitalized , Otherwise you can't address

package main
import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

var (
    userName  string = "chenkai"
    password  string = "chenkai"
    ipAddrees string = "192.168.0.115"
    port      int    = 3306
    dbName    string = "test"
    charset   string = "utf8"
)

func connectMysql() (*sqlx.DB) {
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
    Db, err := sqlx.Open("mysql", dsn)
    if err != nil {
        fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
    }
    return Db
}

func getData(Db *sqlx.DB) {
    
    type userInfo struct {
        Uid int `db:"uid"`
        UserName string `db:"username"`
        CreateTime string `db:"create_time"`
        Password string `db:"password"`
        Department string `db:"department"`
        Email string `db:"email"`
    }

    // Initialize the definition structure , Used to store query data 
    var userData *userInfo = new(userInfo)
    err := Db.Get(userData,"select *from userinfo where uid = 1")
    if err != nil {
        fmt.Printf("query faied, error:[%v]", err.Error())
        return
    }
    
    // Print structure content 
    fmt.Println(userData.Uid, userData.CreateTime, userData.UserName,
        userData.Password, userData.Department, userData.Email)
}

func main() {
    var Db *sqlx.DB = connectMysql()
    defer Db.Close()

    getData(Db)
}

 Running results ：
1 2019-07-06 11:45:20 anson 123456  Technology Department  [email protected]

5、 ... and 、Select() Methods use

 

func (db *DB) Select(dest interface{}, query string, args ...interface{}) error

Multiple records to be queried , Save to slice of structure

explain ： The field name of the structure must be capitalized , Otherwise you can't address

package main
import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

var (
    userName  string = "chenkai"
    password  string = "chenkai"
    ipAddrees string = "192.168.0.115"
    port      int    = 3306
    dbName    string = "test"
    charset   string = "utf8"
)

func connectMysql() (*sqlx.DB) {
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
    Db, err := sqlx.Open("mysql", dsn)
    if err != nil {
        fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
    }
    return Db
}

func selectData(Db *sqlx.DB) {
    type userInfo struct {
        Uid int `db:"uid"`
        UserName string `db:"username"`
        CreateTime string `db:"create_time"`
        Password string `db:"password"`
        Department string `db:"department"`
        Email string `db:"email"`
    }

    // Define the structure slice , Used to store multiple query records 
    var userInfoSlice []userInfo
    err := Db.Select(&userInfoSlice,"select * from userinfo")
    if err != nil {
        fmt.Printf("query faied, error:[%v]", err.Error())
        return
    }

    // Traverse the structure slice 
    for _, userData := range userInfoSlice {
        fmt.Println(userData.Uid, userData.CreateTime, userData.UserName,
        userData.Password, userData.Department, userData.Email)
    }

}

func main() {
    var Db *sqlx.DB = connectMysql()
    defer Db.Close()

    selectData(Db)
}

 Running results ：
1 2019-07-06 11:45:20 anson 123456  Technology Department  [email protected]
3 2019-07-06 11:45:20 johny 123456  Technology Department  [email protected]
4 2019-07-06 11:45:20 johny 123456  Technology Department  [email protected]

6、 ... and 、 Connection pool

1、 Connection pool description

Only sqlx.Open() Function to create a connection pool , At this time, only the connection pool is initialized , No connection to database , Connections are inert , Only a call sqlx.DB Method time , At this time, the connection is really used , The connection pool will create connections , Connection pooling is important , It directly affects your program behavior

The working principle of connection pool is also very simple , When calling sqlx.DB Method time , Will first go to the connection pool to request a database connection , If the connection pool has free connections , Return to the method used in , Otherwise, the connection pool will create a new connection to be used in the method ; Once the database is connected to the method , The connection belongs to the method . After method execution , Or return the connection ownership to the connection pool , Or pass it to the next method that needs a database connection , Finally, release the connection back to the connection pool after using it

There are several ways to request a database connection , After execution, the connection is processed in different ways ：

1. DB.Ping() The connection will be returned to the connection pool immediately after use
2. DB.Exec() The connection will be returned to the connection pool immediately after use , But it returned Result Objects also retain connected references , When the later code needs to process the result set , The connection will be re enabled
3. DB.Query() After the call, pass the connection to sql.Rows type , When the latter iteration is completed or the displayed call Close() After the method , The connection will be released to the connection pool
4. DB.QueryRow() After the call, pass the connection to sql.Row type , When Scan() When the method call is complete , The connection will be released to the connection pool
5. DB.Begin() After the call, pass the connection to sql.Tx Type object , When Commit() or Rollback() Release the connection after method call

Each connection is inert , If you verify sqlx.Open() After call ,sqlx.DB Type objects are available ？ adopt DB.Ping() Method to initialize

func (db *DB) Ping() error

explain ： Need to know , When calling the Ping() After the method , The connection pool must initialize a database connection

package main
import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)
 
var (
    userName  string = "chenkai"
    password  string = "chenkai"
    ipAddrees string = "192.168.0.115"
    port      int    = 3306
    dbName    string = "test"
    charset   string = "utf8"
)
 
func connectMysql() (*sqlx.DB) {
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
    Db, err := sqlx.Open("mysql", dsn)
    if err != nil {
        fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
    }
    return Db
}
 
func ping(Db *sqlx.DB) {
    err := Db.Ping()
    if err != nil {
        fmt.Println("ping failed")
    } else {
        fmt.Println("ping success")
    }
}
 
func main() {
    var Db *sqlx.DB = connectMysql()
    defer Db.Close()
 
    ping(Db)
}
 
 Running results ：
ping success

2、 Connection pool configuration

DB.SetMaxIdleConns(n int) Set the maximum number of connections maintained in the connection pool . The default is 0, Indicates that the connection pool will not maintain the state of database connection ： That is, when the connection is released back to the connection pool , The connection will be closed . This leads to frequent shutdown and creation of connections in the connection pool , We can set a reasonable value .

DB.SetMaxOpenConns(n int) Set the maximum number of connections to open the database . Contains the connections in use and the connection pool . If your method calls You need a connection , And the connection pool has no connections or the number of connections has reached the maximum number of connections . At this point, the method call will be block, Not until a connection is available . Setting this value can avoid high concurrency resulting in connection mysql appear too many connections Error of . The default setting of this function is 0, Means unlimited

DB.SetConnMaxLifetime(d time.Duration) Set the maximum time that the connection can be used , If expired , Connection will be rejected

3、 Number of database connection retries

sqlx The method in has helped us do a lot of things , We don't have to consider connection failure , When calling methods for database operations , If the connection fails ,sqlx The method in will help us deal with , It will automatically connect 2 Time , If we look at the source code, we can see the following code ：

This treatment is also available in other methods , Variables in code maxBadConnRetries Hours the number of attempts if the connection fails , The default is 2

// ExecContext executes a query without returning any rows.
// The args are for any placeholder parameters in the query.
func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error) {
    var res Result
    var err error
    for i := 0; i < maxBadConnRetries; i++ {
        res, err = db.exec(ctx, query, args, cachedOrNewConn)
        if err != driver.ErrBadConn {
            break
        }
    }
    if err == driver.ErrBadConn {
        return db.exec(ctx, query, args, alwaysNewConn)
    }
    return res, err
}