Golang operating MySQL several principles and examples

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

Principles of Use

1. The library comes with a connection pool, and the consumer does not need to implement it. *sql. DB thread safe, out-of-the-box, the screen does harm the implementation of the underlying create connection
2. Open simply creates the class, calls once, and requires a Ping before using to make sure the connection is OK.
3. Be sure to set the two parameters of the connection pool maxidle, Maxopen, otherwise the DB connection will be full (not indexed, large transaction blocking) in extreme cases. Optional maxlifetime, need to consult DBA, general DB default 8 hours, do not need to set, if very short depends on the situation
4. Transactions consume a connection, minimizing transaction time and breaking large transactions, or the number of DB connections will be filled
5. Prepare will occupy a connection, must close after each use, otherwise will also fill the number of connections
6. DSN needs to specify the time zone and support for the time field, or it will occur 8 hours in advance of the issue
7. Query, Prepare, Exec does not need to retry the business layer, the underlying has been implemented

The next source day will explain the reasons in detail

Connection Creation Example

type MySQLClient struct {    Host    string    MaxIdle int    MaxOpen int    User    string    Pwd     string    DB      string    Port    int    pool    *sql.DB}func (mc *MySQLClient) Init() (err error) {    // 构建 DSN 时尤其注意 loc 和 parseTime 正确设置    // 东八区，允许解析时间字段    uri := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8&loc=%s&parseTime=true",        mc.User,        mc.Pwd,        mc.Host,        mc.Port,        mc.DB,        url.QueryEscape("Asia/Shanghai"))    // Open 全局一个实例只需调用一次    mc.pool, err = sql.Open("mysql", uri)    if err != nil {        return err    }    //使用前 Ping, 确保 DB 连接正常    err = mc.pool.Ping()    if err != nil {        return err    }    // 设置最大连接数，一定要设置 MaxOpen    mc.pool.SetMaxIdleConns(mc.MaxIdle)    mc.pool.SetMaxOpenConns(mc.MaxOpen)    return nil}

Database Query Example

func testQuery(beginTime, endTime, code string) ([]*OrderInfo, error) {    db := iowrapper.MySQLClient.GetMySQL()    err := db.Ping()    if err != nil {        return nil, err    }    var rows *sql.Rows    // sql 可以用占位符，涉及业务分表提前生成    rows, err = db.Query(getSqlByCode(code, beginTime, endTime))    if err != nil {        return nil, err    }    OrderInfos := make([]*OrderInfo, 0, 10)    for rows.Next() {        oi := &OrderInfo{}        var createTime time.Time        err := rows.Scan(&oi.OrderId, &createTime, &oi.StartingLng, &oi.StartingLat, &oi.DestLng, &oi.DestLat)        if err != nil {            continue        }        oi.CreateTime = createTime.Unix()        OrderInfos = append(OrderInfos, oi)    }    return OrderInfos, nil}

Database Update Example

func testExec() error {    sql := "update test.table1 set _create_time=now() where id=?"    res, err := db.Exec(sql, 7988161482)    if err != nil {        fmt.Println("exec error ", err.Error())        return err    }    fmt.Println(res.LastInsertId())    fmt.Println(res.RowsAffected())    return nil}

Database Prepare Example

func testStmt() error {    //占位符sql    sql := "update test.table1 set _create_time=now() where id=?"    stmt, err := db.Prepare(sql)    if err != nil {        fmt.Println("stmt error ", err.Error())        return err    }    // 一定要关闭，很重要    defer stmt.Close()    // 可以批量，示例只有一个    _, err = stmt.Exec(7988161474)    if err != nil {        fmt.Println("stmt exec error ", err.Error())        return err    }    return nil}