go语言操作MYSQL数据库

以下介绍两种方式 ，go内置函数，和 sqlx

定义结构体

type Goods struct {

   Id int  `db:"id"`
   GmtCreateSys string `db:"gmt_create_sys"`
   GmtUpdateSys string `db:"gmt_update_sys"`
   GoodsName string  `db:"goods_name"`
   GoodsPrice string `db:"goods_price"`
}

go内置函数database/sql

导入包

import (
   "database/sql"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "time"
)

初始化连接

var Db *sql.DB

func init() {
   database, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/study")
   if err!=nil {
      fmt.Println("；连接数据库失败",err)
      return
   }
   db=database
}

执行新增

func insertGoods()  {

       stmt, _ := db.Prepare("insert into goods(goods_name, goods_price)values(?, ?)")
      res, _ := stmt.Exec("testGoods2", 200)
      fmt.Println(res)
}

执行查询

func queryGoods()  {
   result, _ := db.Query("select id,goods_name,goods_price from goods where goods_price>?", 1)
defer result.Close()

   var id int
   var goodsName string
   var goodsPrice string
   for result.Next() {
      _ = result.Scan(&id,&goodsName,&goodsPrice)
      fmt.Println(id,goodsName,goodsPrice)
   }

   goods := &Goods{
      Id:           id,
      GmtCreateSys: "",
      GmtUpdateSys: "",
      GoodsName:    goodsName,
      GoodsPrice:   goodsPrice,
   }

   fmt.Println(goods.GoodsName)
   fmt.Println(goods.GoodsPrice)

}

执行修改

func updateGoods()  {

   stmt, _ := db.Prepare("update goods set goods_name=? where id=?")
   res, _ := stmt.Exec("test", 1)
   num, _ := res.RowsAffected()
   fmt.Println(num)

}

执行删除

func deleteGoods()  {

   stmt, _ := db.Prepare("delete from goods where id=?")
   res, _ := stmt.Exec(13)
   num, _ := res.RowsAffected()
   fmt.Println(num)
}

sqlx 方式连接mysql

导包

import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
   "time"
)

初始化链接

var Db *sqlx.DB
func init() {
   database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/study")
   if err != nil {
      fmt.Println("open mysql failed,", err)
      return
   }
   Db = database
}

执行新增

func sqlXInsertGoods()  {
   now := time.Now().Format("2006-01-02 15:04:05")
   r, err := Db.Exec("insert into goods(gmt_create_sys,gmt_update_sys,goods_name, goods_price)values(?,?,?,?)", now, now, "testGoods1", 100)
   if err != nil {
      fmt.Println("exec failed, ", err)
      return
   }
   id, err := r.LastInsertId()
   if err != nil {
      fmt.Println("exec failed, ", err)
      return
   }
   fmt.Println("insert succ:", id)
}

执行查询

func sqlXQueryGoods()  {

   var goods []Goods
   err := Db.Select(&goods,"select id,gmt_create_sys,gmt_update_sys,goods_name,goods_price from goods where goods_price>?", 1)
   if err!=nil {
      fmt.Println(err)
   }
   fmt.Println(goods)
}

执行修改和执行删除与 go内置函数一致