【Golang】快速复习指南QuickReview（十一）——数据库访问(MySql为例)

对于业务层面的开发，大多数都离不开数据库的访问。

1.创建项目

golang

1.1 go mod 管理依赖

go mod init gitee.com/RandyField/sqltest

1.2 安装mysql驱动包

go get -u github.com/go-sql-driver/mysql

1.3 创建文件

cd sqltest
New-Item main.go  
New-Item service.go #数据库访问方法
New-Item models.go  #数据映射结构

2.连接数据库

main.go

package main

import (
 "context"
 "database/sql"
 "fmt"
 "log"

 _ "github.com/go-sql-driver/mysql"
)

const (
 user      = "root"
 password  = "111111"
 database  = "push-center"
 protocol  = "tcp"
 address   = "127.0.0.1"
 charset   = "utf8mb4"
 parseTime = "True"
)

var db *sql.DB

func main() {
 /*
  https://pkg.go.dev/github.com/go-sql-driver/mysql
  https://github.com/denisenkom/go-mssqldb
  gorm-sqlx
 */
 // Connstr := fmt.Sprintf("%s:%s@%s(%s)/%s?charset=%s&parseTime=%s",
 //  user, password, protocol, address, database, charset, parseTime)
 // fmt.Println(Connstr)
 // Connstr := "root:111111@tcp(127.0.0.1)/push-center?charset=utf8mb4&parseTime=True"

 //parseTime 是查询结果是否自动解析为时间
 //loc 是MySQL的时区设置
 Connstr := "root:111111@tcp(127.0.0.1)/push-center?charset=utf8mb4&parseTime=True&loc=Local"
 fmt.Println("start...")
 var err error
 db, err = sql.Open("mysql", Connstr)
 if err != nil {
  log.Fatalln(err.Error())
 }

 ctx := context.Background()
 //验证连接是否有效
 err = db.PingContext(ctx)
 if err != nil {
  log.Fatalf(err.Error())
 }
 fmt.Println("Connected")
}

import "database/sql"Golangdatabase/sqldatabase/sqlimport _ "github.com/go-sql-driver/mysql"Golangsql.driver_init

sqlRegister()

driver.Driver()structsql.Register("mysql",&drv{})Connstrusername/password@tcp(ipaddress)/database?parm1=&parm2=parseTimeloc.netEFCoregolangsql.Open()sql.DB*sql.DB

sqlgoroutine*sql.DB

ctx := context.Background()ContextcontextBackground()nilContextContextdb.PingContext(ctx)

3.访问数据库

structstruct

models.go

package main

//Notifypush 推送通知
type notifypush struct {
 Id          int    `json:"Id"`
 AppName     string `json:"APP"`
 Target      int    `json:"Platform"`
 TargetValue *string
 PushType    int
 DeviceType  int `json:"Device"`
 Title       *string
 Body        *string `json:"Content"`
 CreateTime  string
}

如果数据库字段有Null，可空类型， 结构体或者变量，都需要定义指针类型，否则会发生运行时错误。

3.1 查询单条

service.go

package main

import (
 "log"
 "time"
)

// GetById 根据ID获取单条数据
func GetById(id int) (notifypush, error) {
 n := notifypush{}
 err := db.QueryRow("select Id,AppName,Target,TargetValue,PushType,DeviceType,Title,Body,CreateTime From notifypush where Id=?",
  id).Scan(&n.Id, &n.AppName, &n.Target, &n.TargetValue, &n.PushType, &n.DeviceType, &n.Title, &n.Body, &n.CreateTime)
 // err := db.QueryRow("select Id,AppName,Target,TargetValue,PushType,DeviceType,Title,Body,CreateTime From notitypush where Id=@Id",
 //  sql.Named("Id", id)).Scan(&n.Id, &n.AppName, &n.Target, &n.TargetValue, &n.PushType, &n.DeviceType, &n.Title, &n.Body, &n.CreateTime)
 return n, err
}

sqlserversql.Named()mysqlmysql: driver does not support the use of Named Parameters

3.2 查询多条

// GetMultiRow 获取多条数据
func GetMultiRow(id int) (ns []notifypush, err error) {
 rows, err := db.Query("SELECT Id,AppName,Target,TargetValue,PushType,DeviceType,Title,Body,CreateTime From notifypush WHERE Id>?",
  id)
 // 非常重要：关闭rows释放持有的数据库链接
 defer rows.Close()

 for rows.Next() {
  n := notifypush{}
  err = rows.Scan(&n.Id, &n.AppName, &n.Target, &n.TargetValue, &n.PushType, &n.DeviceType, &n.Title, &n.Body, &n.CreateTime)
  if err != nil {
   log.Fatalln(err.Error())
  }
  ns = append(ns, n)
 }
 return
}

3.2 修改、插入、删除

sqlExecdelete

Update

Update操作，需要定义方法（结构体为接收者）

//Update 更新
func (push *notifypush) Update() error {
 _, err := db.Exec("UPDATE notifypush SET AppName=?,Target=?,PushType=?,DeviceType=? WHERE Id=?",
  push.AppName, push.Target, push.PushType, push.DeviceType, push.Id)
 return err
}

Insert

//Insert 插入
func Insert() error {
 // _, err := db.Exec("INSERT INTO  notifypush(AppName,Target,PushType,DeviceType,CreateTime) VALUES(?,?,?,?,?)",
 //  "test-insert-app", 1, 2, 3, time.Now())

 // 改用 prepare
 sql := `INSERT INTO  notifypush(AppName,Target,PushType,DeviceType,CreateTime) VALUES(?,?,?,?,?)`
 stmt, err := db.Prepare(sql)
 if err != nil {
  log.Fatalln(err.Error())
 }
 defer stmt.Close()
 _, err = stmt.Exec("insert-app", 1, 2, 3, time.Now())
 return err
}

4.调用方法

4.1 编码

main.go

package main

import (
 "context"
 "database/sql"
 "fmt"
 "log"

 _ "github.com/go-sql-driver/mysql"
)

const (
 user      = "root"
 password  = "111111"
 database  = "push-center"
 protocol  = "tcp"
 address   = "127.0.0.1"
 charset   = "utf8mb4"
 parseTime = "True"
)

var db *sql.DB

func main() {
 //ommit connect code
    
    //查询
    notify, err := GetById(10)
 if err != nil {
  log.Fatalf(err.Error())
 }
 data, err := json.Marshal(notify)
 fmt.Printf("推送消息为:%sn", data)
    
    //更新
    notify.AppName = "smart"
 err = notify.Update()
 if err != nil {
  log.Fatalf(err.Error())
 }
    notify, err = GetById(10)
 if err != nil {
  log.Fatalf(err.Error())
 }
 data, err = json.Marshal(notify)
 fmt.Printf("更新后:%sn", data)
    
    //插入
    err = Insert()
 if err != nil {
  log.Fatalf(err.Error())
 }
    
    //查询多条
    ns, err := GetMultiRow(24)
 datas, err := json.Marshal(ns)
 fmt.Printf("推送消息为:%sn", datas)
}

4.2 运行

.gomodulego run main.go

go build #编译会生成sqltest.exe
.sqltest.exe #运行

5.ORM

5.1 GORM

GoLangORMMySQLPostgreSQLSqliteSQL ServerGolangEntityFramework

5.2 Sqlx

GoLangdatabase/sql

StructMapsSlicesPrepared Statements

golangdapper

参考连接

https://www.bilibili.com/video/BV1dZ4y1577v

https://qinzhiqiang.cn/2019/10/golang%E6%9C%8D%E5%8A%A1%E5%B8%B8%E7%94%A8%E7%BB%84%E4%BB%B6-gorm-sqlx-mysql-mongodb/