Golang中间件——sqlx操作MySQL

go get github.com/go-sql-driver/mysql // mysql的驱动库
go get github.com/jmoiron/sqlx  // sqlx基于mysql驱动的封装库

-- 创建一个测试数据库
create database test;


-- 创建一张测试表
CREATE TABLE `user` (
  `user_id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR ( 260 ) DEFAULT NULL,
  `sex` VARCHAR ( 260 ) DEFAULT NULL,
  `email` VARCHAR ( 260 ) DEFAULT NULL,
PRIMARY KEY ( `user_id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

database, err := sqlx.Open("mysql", "username:password@tcp(ip:port)/database")

var Db *sqlx.DB


func init() {
  database, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test")
  if err != nil {
    fmt.Println("conn mysql failed,", err)
    return
  }
  
  if err := database.Ping(); err != nil {
    fmt.Println(err)
  }


  Db = database
  // defer Db.Close()  // 关闭
}

package main


import (
  "fmt"


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


// 定义一个结构体User
type User struct {
  UserId   int    `db:"user_id"`
  Username string `db:"username"`
  Sex      string `db:"sex"`
  Email    string `db:"email"`
}


var Db *sqlx.DB


func init() {
  database, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test")
  if err != nil {
    fmt.Println("conn mysql failed,", err)
    return
  }
  
  if err := database.Ping(); err != nil {
    fmt.Println(err)
  }


  Db = database
  // defer Db.Close()  // 关闭
}


func main() {
  defer Db.Close()


  r, err := Db.Exec("insert into user(username, sex, email)values(?, ?, ?)", "zhangsan", "male", "zhangsan@qq.com")
  if err != nil {
    fmt.Println("failed：", err)
    return
  }
  
  id, err := r.LastInsertId()  // 获取到id
  if err != nil {
    fmt.Println("failed：", err)
    return
  }
  
  fmt.Println("插入成功：", id)  // 插入成功：1
}

■ ■■■■

package main


import (
  "fmt"


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


type User struct {
  UserId   int    `db:"user_id"`
  Username string `db:"username"`
  Sex      string `db:"sex"`
  Email    string `db:"email"`
}


var Db *sqlx.DB


func init() {
  database, err := sqlx.Open("mysql", "root:1213456@tcp(127.0.0.1:3306)/test")
  if err != nil {
    fmt.Println("conn mysql failed,", err)
    return
  }
  
  if err := database.Ping(); err != nil {
    fmt.Println(err)
  }


  Db = database
  // defer Db.Close()  // 关闭
}


func main() {
  defer Db.Close()


  // 更新
  res, err := Db.Exec("update user set email=? where user_id=?", "zhangsan@163.com", 1)
  if err != nil {
    fmt.Println("failed：", err)
    return
  }
  
  row, err := res.RowsAffected()  // 受影响的行数
  if err != nil {
    fmt.Println("failed：",err)
  }
  
  fmt.Println("更新成功:", row)  // 更新成功: 1
}

■ ■■■■

package main


import (
  "encoding/json"
  "fmt"


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


type User struct {
  UserId   int    `db:"user_id" json:"user_id"`
  Username string `db:"username" json:"username"`
  Sex      string `db:"sex" json:"sex"`
  Email    string `db:"email" json:"email"`
}


var Db *sqlx.DB


func init() {
  database, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test")
  if err != nil {
    fmt.Println("conn mysql failed,", err)
    return
  }
  
  if err := database.Ping(); err != nil {
    fmt.Println(err)
  }


  Db = database
  // defer Db.Close()  // 关闭
}


func main() {
  defer Db.Close()


  var user []User
  
  // 查找
  err := Db.Select(&user, "select user_id, username, sex, email from user where user_id=?", 1)
  if err != nil {
    fmt.Println("failed：", err)
    return
  }
  fmt.Println(user)


  // 将查询结果转为JSON字符串
  var data []byte
  data, err = json.Marshal(user)
  if err != nil {
    fmt.Println("marshal failed, err:", err)
    return
  }
  fmt.Println(string(data))


  // 将JSON字符串转为struct
  var users []User
  json_str := `[{"user_id":1,"username":"lisi","sex":"male","email":"zhangsan@163.com"}]`
  err = json.Unmarshal([]byte(json_str), &users)
  if err != nil {
    fmt.Println("marshal failed, err:", err)
    return
  }
  fmt.Println(users)
}


/*
[{1 lisi male zhangsan@163.com}]
[{"user_id":1,"username":"lisi","sex":"male","email":"zhangsan@163.com"}]
[{1 lisi male zhangsan@163.com}]
*/