[Golang]golang使用mysql实例和第三方库Gendry

在数据库实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《[Golang]golang使用mysql实例和第三方库Gendry》，聊聊MySQL、go，希望可以帮助到正在努力赚钱的你。

导入对应的包

// 安装
$ go get github.com/go-sql-driver/mysql

// 导入
import (
    "database/sql"
    _ "thirdpkg/go-sql-driver/mysql"
)

初始化mysql客户端

// 打开数据库,格式是⽤户名:密码@协议/数据库名称？编码⽅式
db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8"
if err != nil {
    fmt.Println(err)
}
// 确保db正常关闭
defer db.Close()
// 使用前Ping, 确保db连接正常
err = db.Ping()
if err != nil {
    fmt.Println(err)
}

数据库查询示例

// 假设日期和查询条件是从http客户端发过来的参数
// start_day: "2020-05-02"
// end_day: "2020-05-10"
// city: "[1,2,3,4,5,6]"

// 1) 获取参数并校验参数有效性
var citys []int
r.FormValue("city")
startDay := r.FormValue("start_day")
endDay := r.FormValue("end_day")

err = json.Unmarshal([]byte(cityStr), &citys)
if err != nil {
    fmt.Println(err)
}
if ok, _ := regexp.MatchString(`^\d{4}-\d{2}-\d{2}$`, startDay); !ok {
    fmt.Printf("invalid param, start day:[%s]\n", startDay)
}
if ok, _ := regexp.MatchString(`^\d{4}-\d{2}-\d{2}$`, endDay); !ok {
    fmt.Printf("invalid param, end day:[%s]\n", endDay)
}

// 2) 构造sql语句
sqlText := `
select
    sum(sales)/(to_days('end_day') - to_days('start_day')) as daily_sum,
    sum(price)/(to_days('end_day') - to_days('start_day')) as daily_price
from sales_table
where dt between 'start_day' and 'end_day' 
    and city_id in %s 
`

// 获取城市对应的range字符串用于sql语句："[1,2,3,4,5,6]" ==> "(1,2,3,4,5,6)"
cityRange := genSQLRangeStrByIntArr(citys)

// 通过fmt.Sprintf拼接得到对应的字符串
sqlText = fmt.Sprintf(sqlText, cityRange)

// 对于多次出现的变量, 使用strings.Replace替换
sqlText = strings.Replace(sqlText, "start_day", startDay, -1)
sqlText = strings.Replace(sqlText, "endDay", endDay, -1)

// 3) 查询sql
rows, err := db.Query(sqlText)
defer rows.Close() // rows必须scan, 否则会导致链接无法关闭而一直占用链接, 直到超过设置的生命周期
if err != nil {
    fmt.Println(err)
}

// 存储结果的切片, 用于存储多行返回结果
var resInfoArr []*resInfo
for rows.Next() {
    var tempInfo resInfo
    // 注意rows.Scan的参数顺序和个数都很重要, 必须和sql查询语句的返回结果一一对应
    // 另外必须注意结构体的变量类型也必须和mysql一致
    rows.Scan(&resInfo.dailySum, &resInfo.dailyPrict)
    resInfoArr = append(resInfoArr, &tempInfo)
}


// 存储结果的结构体
type resInfo struct {
    dailySum   float64 `db:"daily_sum"`
    dailyPrict float63 `db:"daily_price"`
}

// 生成between...and...的范围字符串, 用于SQL语句
func genSQLRangeStrByIntArr(arr []int) (res string) {
    var tempStrArr = make([]string, len(arr))
    for k, v := range arr {
        tempStrArr[k] = fmt.Sprintf("%d", v)
    }
    res = "(" + strings.Join(tempStrArr, ",") + ")"
    return
}

其他操作示例

import (
    _"mysql"
    "database/sql"
    "fmt"
)
 
func check(err error){
    if err!=nil{
        fmt.Println(err)
    }
 
}
 
 
func main(){
    db,err:=sql.Open("mysql","root:123456@tcp(127.0.0.1:3306)/employee")
    check(err)
 
    //query
    type info struct {
        id      int     `db:"id"`
        name    string  `db:"name"`
        age     int     `db:"age"`
        sex     string  `db:"sex"`
        salary  int     `db:"salary"`
        work    string  `db:"work"`
        inparty string  `db:"inparty"`
    }
    rows,err:=db.Query("SELECT * FROM message")
    check(err)
    for rows.Next(){
        var s info
        err=rows.Scan(&s.id,&s.name,&s.age,&s.sex,&s.salary,&s.work,&s.inparty,)
        check(err)
        fmt.Println(s)
    }
    rows.Close()
    
    //insert
    db.Exec("INSERT INTO message(id,name,age,sex,salary,work,inparty)VALUES (?,?,?,?,?,?,?)",7,"李白",80,"男",1000,"中","是")
 
    //update
    results,err:=db.Exec("UPDATE message SET salary=? where id=?",8900,3)
    check(err)
    fmt.Println(results.RowsAffected())
 
 
    //delete
    results,err:=db.Exec("DELETE FROM message where id=?",2)
    check(err)
    fmt.Println(results.RowsAffected())

第三方库: gendry

以我们上面的查询为例，

var db *sql.DB
var err error
db, err = manager
        .New(dbName, user, password, host)
        .Set(
            manager.SetCharset("utf8"),
            manager.SetAllowCleartextPasswords(true),
            manager.SetInterpolateParams(true),
            manager.SetTimeout(1 * time.Second),
            manager.SetReadTimeout(1 * time.Second)
        ).Port(3302).Open(true)

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

2. Builder

where := map[string]interface{}{
    "city in": []string{"beijing", "shanghai"},
    "score": 5,
    "age >": 35,
    "address": builder.IsNotNull,
    "_orderby": "bonus desc",
    "_groupby": "department",
}
table := "some_table"
selectFields := []string{"name", "age", "sex"}
cond, values, err := builder.BuildSelect(table, where, selectFields)

//cond = SELECT name,age,sex FROM g_xxx WHERE (score=? AND city IN (?,?) AND age>? AND address IS NOT NULL) GROUP BY department ORDER BY bonus DESC
//values = []interface{}{"beijing", "shanghai", 5, 35}

rows,err := db.Query(cond, values...)

如果你想清除

where := map[string]interface{}{
        "score": 0,
        "age": 35,
    }
finalWhere := builder.OmitEmpty(where, []string{"score", "age"})
// finalWhere = map[string]interface{}{"age": 35}

// support: Bool, Array, String, Float32, Float64, Int, Int8, Int16, Int32, Int64, Uint, Uint8, Uint16, Uint32, Uint64, Uintptr, Map, Slice, Interface, Struct

同时，

where := map[string]interface{}{
    "score > ": 100,
    "city in": []interface{}{"Beijing", "Shijiazhuang",}
}
// AggregateSum,AggregateMax,AggregateMin,AggregateCount,AggregateAvg is supported
result, err := AggregateQuery(ctx, db, "tableName", where, AggregateSum("age"))
sumAge := result.Int64()

result,err = AggregateQuery(ctx, db, "tableName", where, AggregateCount("*")) 
numberOfRecords := result.Int64()

result,err = AggregateQuery(ctx, db, "tableName", where, AggregateAvg("score"))
averageScore := result.Float64()

对于比较复杂的查询,

cond, vals, err := builder.NamedQuery("select * from tb where name={{name}} and id in (select uid from anothertable where score in {{m_score}})", map[string]interface{}{
    "name": "caibirdme",
    "m_score": []float64{3.0, 5.8, 7.9},
})

assert.Equal("select * from tb where name=? and id in (select uid from anothertable where score in (?,?,?))", cond)
assert.Equal([]interface{}{"caibirdme", 3.0, 5.8, 7.9}, vals)

3. Scanner

执行了数据库操作之后，要把返回的结果集和自定义的struct进行映射。Scanner提供一个简单的接口通过反射来进行结果集和自定义类型的绑定：

type Person struct {
    Name string `ddb:"name"`
    Age int `ddb:"m_age"`
}

rows,err := db.Query("SELECT age as m_age,name from g_xxx where xxx")
defer rows.Close()

var students []Person

scanner.Scan(rows, &students)

for _,student := range students {
    fmt.Println(student)
}

scanner.SetTagName("json")

type Person struct {
    Name string `json:"name"`
    Age int `json:"m_age"`
}

// ...
var student Person
scaner.Scan(rows, &student)

rows,_ := db.Query("select name,m_age from person")
result,err := scanner.ScanMap(rows)
for _,record := range result {
    fmt.Println(record["name"], record["m_age"])
}

需要注意的点：

• 如果是使用

  go get -u github.com/caibirdme/gforge

  • 用法

  ##################################################################
  # 帮助文档
  ##################################################################
  > gforge -h
  A collection of tools to generate code for operating database supported by Gendry
  
  Options:
  
    -h, --help   display help information
    -v           version
  
  Commands:
  
    help    display help information
    table   schema could generate go struct code for given table
    dao     dao generates code of dao layer by given table name
  
  ##################################################################
  # 生成表格对应的结构体
  ##################################################################
  > gforge help table
  schema could generate go struct code for given table
  
  Options:
  
    -d               database name
    -t               table name
    -u               user name
    -p               password
    -h[=localhost]   host
    -P[=3306]        port
  
  > gforge table -uusername -ppassword -hip -dinformation_schema -tCOLUMNS
  
  // COLUMNS is a mapping object for COLUMNS
  type COLUMNS struct {
      TABLECATALOG string `json:"TABLE_CATALOG"
      TABLESCHEMA string `json:"TABLE_SCHEMA"
      TABLENAME string `json:"TABLE_NAME"
      COLUMNNAME string `json:"COLUMN_NAME"
      ORDINALPOSITION uint64 `json:"ORDINAL_POSITION"
      COLUMNDEFAULT string `json:"COLUMN_DEFAULT"
      ISNULLABLE string `json:"IS_NULLABLE"
      DATATYPE string `json:"DATA_TYPE"
      CHARACTERMAXIMUMLENGTH uint64 `json:"CHARACTER_MAXIMUM_LENGTH"
      CHARACTEROCTETLENGTH uint64 `json:"CHARACTER_OCTET_LENGTH"
      NUMERICPRECISION uint64 `json:"NUMERIC_PRECISION"
      NUMERICSCALE uint64 `json:"NUMERIC_SCALE"
      DATETIMEPRECISION uint64 `json:"DATETIME_PRECISION"
      CHARACTERSETNAME string `json:"CHARACTER_SET_NAME"
      COLLATIONNAME string `json:"COLLATION_NAME"
      COLUMNTYPE string `json:"COLUMN_TYPE"
      COLUMNKEY string `json:"COLUMN_KEY"
      EXTRA string `json:"EXTRA"
      PRIVILEGES string `json:"PRIVILEGES"
      COLUMNCOMMENT string `json:"COLUMN_COMMENT"
      GENERATIONEXPRESSION string `json:"GENERATION_EXPRESSION"
  }
  
  ##################################################################
  # 根据一张表生成对应的dao layer
  ##################################################################
  > gforge dao -uusername -ppassword -hip -dinformation_schema -tCOLUMNS | gofmt
  package COLUMNS
  
  import (
      "database/sql"
      "errors"
      "github.com/didichuxing/gendry/builder"
      "github.com/didichuxing/gendry/scanner"
  )
  
  /*
      This code is generated by ddtool
  */
  
  // COLUMNS is a mapping object for COLUMNS
  type COLUMNS struct {
      TABLECATALOG           string `json:"TABLE_CATALOG"`
      TABLESCHEMA            string `json:"TABLE_SCHEMA"`
      TABLENAME              string `json:"TABLE_NAME"`
      COLUMNNAME             string `json:"COLUMN_NAME"`
      ORDINALPOSITION        uint64 `json:"ORDINAL_POSITION"`
      COLUMNDEFAULT          string `json:"COLUMN_DEFAULT"`
      ISNULLABLE             string `json:"IS_NULLABLE"`
      DATATYPE               string `json:"DATA_TYPE"`
      CHARACTERMAXIMUMLENGTH uint64 `json:"CHARACTER_MAXIMUM_LENGTH"`
      CHARACTEROCTETLENGTH   uint64 `json:"CHARACTER_OCTET_LENGTH"`
      NUMERICPRECISION       uint64 `json:"NUMERIC_PRECISION"`
      NUMERICSCALE           uint64 `json:"NUMERIC_SCALE"`
      DATETIMEPRECISION      uint64 `json:"DATETIME_PRECISION"`
      CHARACTERSETNAME       string `json:"CHARACTER_SET_NAME"`
      COLLATIONNAME          string `json:"COLLATION_NAME"`
      COLUMNTYPE             string `json:"COLUMN_TYPE"`
      COLUMNKEY              string `json:"COLUMN_KEY"`
      EXTRA                  string `json:"EXTRA"`
      PRIVILEGES             string `json:"PRIVILEGES"`
      COLUMNCOMMENT          string `json:"COLUMN_COMMENT"`
      GENERATIONEXPRESSION   string `json:"GENERATION_EXPRESSION"`
  }
  
  //GetOne gets one record from table COLUMNS by condition "where"
  func GetOne(db *sql.DB, where map[string]interface{}) (*COLUMNS, error) {
      if nil == db {
          return nil, errors.New("sql.DB object couldn't be nil")
      }
      cond, vals, err := builder.BuildSelect("COLUMNS", where, nil)
      if nil != err {
          return nil, err
      }
      row, err := db.Query(cond, vals...)
      if nil != err || nil == row {
          return nil, err
      }
      defer row.Close()
      var res *COLUMNS
      err = scanner.Scan(row, &res)
      return res, err
  }
  
  //GetMulti gets multiple records from table COLUMNS by condition "where"
  func GetMulti(db *sql.DB, where map[string]interface{}) ([]*COLUMNS, error) {
      if nil == db {
          return nil, errors.New("sql.DB object couldn't be nil")
      }
      cond, vals, err := builder.BuildSelect("COLUMNS", where, nil)
      if nil != err {
          return nil, err
      }
      row, err := db.Query(cond, vals...)
      if nil != err || nil == row {
          return nil, err
      }
      defer row.Close()
      var res []*COLUMNS
      err = scanner.Scan(row, &res)
      return res, err
  }
  
  //Insert inserts an array of data into table COLUMNS
  func Insert(db *sql.DB, data []map[string]interface{}) (int64, error) {
      if nil == db {
          return nil, errors.New("sql.DB object couldn't be nil")
      }
      cond, vals, err := builder.BuildInsert("COLUMNS", data)
      if nil != err {
          return 0, err
      }
      result, err := db.Exec(cond, vals...)
      if nil != err || nil == result {
          return 0, err
      }
      return result.LastInsertId()
  }
  
  //Update updates the table COLUMNS
  func Update(db *sql.DB, where, data map[string]interface{}) (int64, error) {
      if nil == db {
          return 0, errors.New("sql.DB object couldn't be nil")
      }
      cond, vals, err := builder.BuildUpdate("COLUMNS", where, data)
      if nil != err {
          return 0, err
      }
      result, err := db.Exec(cond, vals...)
      if nil != err {
          return 0, err
      }
      return result.RowsAffected()
  }
  
  // Delete deletes matched records in COLUMNS
  func Delete(db *sql.DB, where,data map[string]interface{}) (int64, error) {
      if nil == db {
          return 0, errors.New("sql.DB object couldn't be nil")
      }
      cond,vals,err := builder.BuildDelete("{{.TableName}}", where)
      if nil != err {
          return 0, err
      }
      result,err := db.Exec(cond, vals...)
      if nil != err {
          return 0, err
      }
      return result.RowsAffected()
  }

  其他文章

  [[Go基础]Json在Go中的使用]([https://zhuanlan.zhihu.com/p/...])
  [[Go基础]理解 Go 标准库中的 atomic.Value 类型](https://zhuanlan.zhihu.com/p/...
  [[Golang实战]thread pool的go实现](https://zhuanlan.zhihu.com/p/...

  Reference

  [1] https://www.runoob.com/mysql/...
  [2] https://www.jianshu.com/p/af2...
  [3] https://blog.csdn.net/a670531...
  [4] https://github.com/didi/gendry

  今天关于《[Golang]golang使用mysql实例和第三方库Gendry》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于mysql的内容请关注golang学习网公众号！