登录
首页 >  数据库 >  MySQL

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

来源:SegmentFault

时间:2023-02-25 10:11:46 166浏览 收藏

在数据库实战开发的过程中,我们经常会遇到一些这样那样的问题,然后要卡好半天,等问题解决了才发现原来一些细节知识点还是没有掌握好。今天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学习网公众号!

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>
评论列表