Go操作Mysql数据库

Mysql

//下载mysql依赖文件
go get github.com/go-sql-driver/mysql
go get github.com/jmoiron/sqlx
//连接mysql

package main

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

var Db *sqlx.DB

type Users struct {
  Id       int64  `db:"id"`
  UserName string `db:"user_name"`
}

func init() {
  //连接数据库
  database, err := sqlx.Open("mysql", "root:ddmw2BjYwnIgO65N@tcp(47.97.253.216:3306)/go_test")
  if err != nil {
      fmt.Println(err)
      return
  }
  Db = database
}

func main() {
  //delUsersById(1)
  modifyUserNameById(2, "李四")
}

//修改数据
func modifyUserNameById(id int64, username string) {
  //开启事务
  conn, err := Db.Begin()
  if err != nil {
      fmt.Println(err)
      return
  }
  exec, err := conn.Exec("update users set user_name=? where id=?", username, id)
  if err != nil {
      fmt.Println(err)
      conn.Rollback()
      return
  }
  row, err := exec.RowsAffected()
  if err != nil {
      fmt.Println(err)
      conn.Rollback()
      return
  }
  fmt.Println("update row count", row)
  conn.Commit() //事务提交

}

//删除数据
func delUsersById(id int64) {
  conn, err := Db.Beginx() //开启事务
  if err != nil {
      fmt.Println("begin failed", err)
      return
  }
  exec, err := conn.Exec("delete from users where id=?", id)
  if err != nil {
      fmt.Println("delete user failed", err)
      conn.Rollback() //事务回滚
      return
  }
  //获取受影响行数
  row, err := exec.RowsAffected()
  if err != nil {
      fmt.Println("delete failed", err)
  }
  fmt.Println("delete row count", row)
  conn.Commit() //事务提交
}

//查询数据
func selectList() []Users {
  var users []Users
  err := Db.Select(&users, "select id,user_name from users")
  if err != nil {
      fmt.Println(err)
      return nil
  }
  return users
}

//插入数据
func insert() (id int64) {
  res, err := Db.Exec("insert into users (user_name) values(?)", "张三")
  if err != nil {
      fmt.Println(err)
      return
  }
  //获取插入的ID值
  id, err = res.LastInsertId()
  if err != nil {
      fmt.Println(err)
      return
  }
  fmt.Println("id=========>", id)
  return
}