【Golang】SQL和Gendry的实践

导入对应的包

// 安装
$ 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)
}

数据库查询示例

golang本身的mysql库存在很多不便利的地方和一些坑，需要注意一下

// 假设日期和查询条件是从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

以我们上面的查询为例，golang本身的go-sql-driver/mysql本身编程和维护方便都有不少需要注意的问题，Gendry是一个用于辅助操作数据库的Go包，提供了一系列的方法来为你调用标准库database/sql中的方法准备参数。

主要包括三部分：manager、builder和scanner

详细的资料可以阅读各个库的README：

• manager：https://github.com/didi/gendry/tree/master/manager
• scanner：https://github.com/didi/gendry/tree/master/scanner
• builder：https://github.com/didi/gendry/tree/master/builder

1. manager

主要用于初始化连接池，即sql.DB对象，设置各种参数：

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)

manager本质做的事情即生成dataSourceName，一般它的格式如下：

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

2. Builder

Builder用于生成sql语句，手写sql简单直观但是可维护性差，并且硬编码容易出错，如果遇到大where in查询，且in的集合内容又是动态的就很麻烦了。

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中的零值可以使用builder.OmitEmpty：

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

同时，builder还提供一个便捷方法来进行聚合查询，比如：count,sum,max,min,avg：

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()

对于比较复杂的查询, NamedQuery将会派上用场:

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进行反射时会使用结构体的tag，如上所示，scanner会把结果集中的 m_age绑定到结构体的Age域上。默认使用的tagName是ddb:"xxx"，你也可以自定义：

scanner.SetTagName("json")

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

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

scaner.SetTagName是全局设置，为了避免歧义，只允许设置一次，一般在初始化DB阶段进行此项设置

4. ScanMap

ScanMap方法返回的是一个map，有时候你可能不太像定义一个结构体去存你的中间结果，那么ScanMap或许比较有帮助：

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"])
}

需要注意的点：

• 如果是使用Scan或者ScanMap的话，你必须在之后手动close rows
• 传给Scan的必须是引用
• ScanClose和ScanMapClose不需要手动close rows

5. CLI Tool

除了以上API，Gendry还提供了一个命令行工具来进行代码生成，既可以生成Gendry相关的golang结构体，也可以生成完整的数据层dao layer：

https://github.com/caibirdme/gforge

• 安装

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中的使用
[Go基础]理解 Go 标准库中的 atomic.Value 类型
[Golang实战]thread pool的go实现

Reference

[1] https://www.runoob.com/mysql/mysql-install.html
[2] https://www.jianshu.com/p/af27b7a2a239
[3] https://blog.csdn.net/a670531899/article/details/81226752
[4] https://github.com/didi/gendry
[5] https://gitee.com/mirrors/Gendry
[6] Gendry实战