go-mysql

2020-06-17

golang连接mysql数据库简单demo

引入相关依赖包

import (
  "database/sql"
  "fmt"

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

初始化连接

var db *sql.DB

func initDB() (error) {
  dsn := "user:password@(127.0.0.1:3306)/dbname?charset=utf8"
  var err error
  //db,err := sql.Open("mysql",dsn)会出现作用域覆盖导致空指针
  // 创建新的指针会把全局的db指针给覆盖掉
  db, err = sql.Open("mysql", dsn)
  if err != nil {
    return err
  }
  err = db.Ping()
  if err != nil {
    return err
  }
  return nil
}

数据库表结构映射

type User struct {
  id   int64
  name string
  age  int
}

添加数据DEMO

func InsertRowDemo() {
  sqlStr := "insert into user(name,age) value (?,?)"
  ret, err := db.Exec(sqlStr, "王五", 23)
  if err != nil {
    fmt.Printf("insert failed ,err :%v\n", err)
    return
  }
  theID, err := ret.LastInsertId()
  if err != nil {
    fmt.Printf("get lastinsert ID failed ,err :%v \n", err)
    return
  }
  fmt.Printf("insert success,the id is %d.\n", theID)
}

删除数据DEMO

func DeleteRowDemo(){
  sqlStr := "Delete FROM user where id = ?"
  ret,err := db.Exec(sqlStr,3)
  if err != nil {
    fmt.Printf("Delete failed ,err :%v\n",err)
    return
  }
  n,err := ret.RowsAffected()
  if err != nil {
    fmt.Printf("get RowAffected failed ,err :%v\n",err)
    return
  }
  fmt.Printf("delete success ,affect rows : %d\n",n)
}

更改数据DEMO

func UpdateRowDemo(){
  sqlStr := "update user set age=? where id = ?"
  ret,err := db.Exec(sqlStr,33,2)
  if err != nil {
    fmt.Printf("update failed,err : %v\n",err)
    return
  }
  // 更新影响的行数
  n,err := ret.RowsAffected()
  if err != nil {
    fmt.Printf("get RowAffected failed,err:%v\n",err)
    return
  }
  fmt.Printf("update success,affected rows : %d\n",n)
}

查询数据DEMO

查询单条数据

func QueryRowDemo() {
  sqlStr := "select id,name,age from user where id = ?"
  var u User = User{}
  err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)
  if err != nil {
    fmt.Printf("scan failed,err:%v\n", err)
    return
  }
  fmt.Printf("user:%v", u)
}

查询多条数据

func QueryMultiDemo(){
  sqlStr := "select id,name,age from user where id > ?"
  rows,err := db.Query(sqlStr,1)
  if err != nil {
    fmt.Printf("query failed,err :%v\n",err)
    return
  }
  defer rows.Close()

  for rows.Next(){
    var u User
    err := rows.Scan(&u.id,&u.name,&u.age)
    if err != nil {
      fmt.Printf("Scan failed,err:\v\n",err)
      return
    }
    fmt.Printf("u:%v",u)
  }
}

带预处理的查询

func PrepareQueryDemo()  {
  sqlStr := "Select id,name,age from user where id > ?"
  stmt,err := db.Prepare(sqlStr)
  if err != nil {
    fmt.Printf("Prepare failed,err :%v \n",err)
    return
  }
  defer stmt.Close()
  // 放置参数
  rows,err := stmt.Query(0)
  if err != nil {
    fmt.Printf("prepare failed,err :%v\n",err)
    return
  }
  defer rows.Close()
  for rows.Next(){
    var u User
    err := rows.Scan(&u.id,&u.name,&u.age)
    if err != nil {
      fmt.Printf("Scan failed ,err: %v\n",err)
      return
    }
    fmt.Printf("user: %v\n",u)
  }

}

gorm使用

package main

import (
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
)
type UserTb struct {
    Account string `gorm:"column:account;primary_key"`
    Password string `gorm:"column:password"`
}
var (
    db *gorm.DB
)
func (u *UserTb) TableName() string  {
    // 返回的值是数据库中数据库表名
    return "usertb"
}

func InsertDemo() {
    insertUser := UserTb{
        Account:  "testUserAccount",
        Password: "testPassword",
    }
    // 判断主键是否为空
    // 只是对值进行非空(零值)判断,并不会涉及数据库查询
    //
    re := db.NewRecord(insertUser)
    if re {
        // 插入数据
        if err := db.Create(&insertUser).Error;err == nil{
            fmt.Println("insert success")
        }
    }
    
}

func SelectDemo(){
    user := UserTb{}
    db.Where("account=?","testUserAccount").Find(&user)
    fmt.Println(user)
}
func UpdateDemo(){
    user := UserTb{Account: "testUserAccount"}
    db.Where("account=?","testUserAccount").First(&user)
    fmt.Println("find : ",user)
    user.Password = "testUserPasswordUpdate"
    // db.Save(&user)
    db.Model(&user).Update("password","testUserPasswordUpdate2")
    //
    fmt.Println("update")
    fmt.Println(user)
    //
    fmt.Println("find : ")
    db.Where("account","testUserAccount").First(&user)
    fmt.Println(user)
}
func DeleteDemo() {
    user := UserTb{Account: "testUserAccount"}
    db.Delete(&user)
    fmt.Println("Delete success")
}
func main() {
    var err error
    db,err = gorm.Open("mysql","root:root1234@(localhost:3306)/gamedb?charset=utf8mb4&parseTime=True&loc=Local")
    if err!= nil{
        panic(err)
    }
    defer db.Close()
    usr := UserTb{}
    db.First(&usr)
    fmt.Println(usr)
    users := []UserTb{}
    db.Find(&users)
    fmt.Println(users)
    InsertDemo()
    SelectDemo()
    UpdateDemo()
    DeleteDemo()
}