go调用mysql接口_[Golang]golang使用mysql实例和第三方库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：

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：

安装

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

}

其他文章

Reference