Go使用其他主流的库操作mysql对比

参考Github地址：https://github.com/didi/gendry

导包

import (
	"github.com/didi/gendry"
）

建立连接

db, err := manager
		.New(dbName, user, password, host)
		.Set(
			manager.SetCharset("utf8"),
			manager.SetTimeout(1 * time.Second),
			manager.SetReadTimeout(1 * time.Second)
		).Port(3302).Open(true)

原生MySQL：

db, err := sql.Open(“mysql”, “用户名:密码@tcp(ip:端口)/数据库名字?charset=utf8”)

查询

where := map[string]interface{}{
	"sex": ”男“,
	"age >": 35,
}
table := "tableName"
selectFields := []string{"name", "age", "sex"}
cond, values, err := builder.BuildSelect(table, where, selectFields)

//cond = select name, age, sex from tableName where sex="男" and age>35
//values = []interface{}{"男",  35
 

原生：

		rows, err := db.Query(sql语句,value)
		if err != nil {
			Logger.Println("err:", err)
			return
		}
		defer rows.Close()
		//遍历每一条数据
		for rows.Next() {
			......
			if err != nil {
				Logger.Println("err:", err)
				return
			}
		}

聚合查询

where := map[string]interface{}{
    "score > ": 100,
    "city in": []interface{}{"Beijing", "Shijiazhuang", }
}
// AggregateSum 求和
// AggregateMax 最大值
// AggregateMin 最小值
// AggregateCount 数量
// AggregateAvg 平均值
result, err := AggregateQuery(ctx, db, "tableName", where, AggregateSum("age"))

result, err = AggregateQuery(ctx, db, "tableName", where, AggregateCount("*")) 

result, err = AggregateQuery(ctx, db, "tableName", where, AggregateAvg("score"))

复杂条件查询

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)

Scanner

把返回的结果集和自定义的struct进行映射。

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

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

var students []Person

scanner.Scan(rows, &students)

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

ScanMap

ScanMap方法返回的是一个map

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