Golang MySQL 操作

1 .   创建go_db 目录

           mkdir go_db

2. 

root@VirtualBox:/mnt/share/goframe/go_db# go mod init go_db
go: creating new go.mod: module go_db
root@VirtualBox:/mnt/share/goframe/go_db# go get -u github.com/go-sql-driver/mysql
go: added github.com/go-sql-driver/mysql v1.7.1
root@VirtualBox:/mnt/share/goframe/go_db#

3   

root@wally-VirtualBox:/mnt/share/goframe/go_db# go run mysql_go.go
db reachable, *sql.DB, *main.DB_INSTANCE
after fetch
id 1, username user2, password pass3, create_at 2023-05-08 15:47:01
after fetchall
id, username, password, create_at
1, user2, pass3, 2023-05-08 15:47:01
3, user3, pwd3, 2023-05-08 17:46:00
[]main.User{main.User{id:1, username:"user2", password:"pass3", createAt:1683532021}, main.User{id:3, username:"user3", password:"pwd3", createAt:1683539160}}
after fetchall
id, username, password, create_at
1, user2, pass3, 2023-05-08 15:47:01
3, user3, pwd3, 2023-05-08 17:46:00
[]main.User{main.User{id:1, username:"user2", password:"pass3", createAt:1683532021}, main.User{id:3, username:"user3", password:"pwd3", createAt:1683539160}}

   如下为 mysql_go.go ,其中，fetchall 获取的用户通过值传递。

package main

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

const DB_ADDR_PORT = "localhost:3307"
const DB_LOGIN_USER = "root"
const DB_LOGIN_PASS = "usbw"
const DB_TAB = "test"

type DB_Instance struct {
   db *sql.DB
}

type User struct {
   id       int
   username string
   password string
   createAt int
}

type User_list_array struct {
   users_list []User
}

func db_init() *DB_Instance {
   dsn := fmt.Sprintf("%s:%s@(%s)/%s", DB_LOGIN_USER, DB_LOGIN_PASS, DB_ADDR_PORT, DB_TAB)
   db, err := sql.Open("mysql", dsn)

   if err != nil {
      log.Fatalf("connect db failed %v", err)
      return nil
   }

   if err := db.Ping(); err != nil {
      log.Fatalf("ping db failed %v", err)
      return nil
   }

   db_inst := new(DB_Instance)
   db_inst.db = db

   //fmt.Printf("db reachable, %T, %T, %#v\n", db, db_inst, db)
   fmt.Printf("db reachable, %T, %T\n", db, db_inst)

   return db_inst
}

func (db_inst *DB_Instance) db_insert() bool {
   query := `
               CREATE TABLE IF NOT EXISTS users(
               id INT UNSIGNED AUTO_INCREMENT,
               username VARCHAR(255) NOT NULL,
               password VARCHAR(255) NOT NULL,
               create_at INT(10) NOT NULL,
               PRIMARY KEY (id)
            )ENGINE=INNODB DEFAULT CHARSET=UTF8;
   `

   _, err := db_inst.db.Exec(query)

   if err != nil {
      log.Fatalf("user create failed %v", err)
      return false
   }

   for i := 1; i < 5; i++ {

      username := fmt.Sprintf("user%d", i)
      password := fmt.Sprintf("pwd%d", i)
      createAt := time.Now().Unix()

      rv, err := db_inst.db.Exec(`INSERT INTO users (username, password, create_at) VALUES(?, ?, ?)`, username, password, createAt)
      if err != nil {
         log.Fatalf("insert failed %v", err)
         return false
      }

      uid, err := rv.LastInsertId()

      fmt.Printf("inserted %d\n", uid)
   }

   return true
}


func (db_inst *DB_Instance) db_fetch() (*User, bool) {
   user := new(User)

   query := `SELECT id, username, password, create_at FROM users WHERE id=?`
   err := db_inst.db.QueryRow(query, 1).Scan(&user.id, &user.username, &user.password, &user.createAt)

   if err != nil {
      log.Fatalf("queryrow failed %v", err)
      return &User{}, false
   }

   return user, true
}


func (db_inst *DB_Instance) db_fetchall() (*User_list_array, bool) {
   query := `SELECT id, username, password, create_at FROM users WHERE id < ? LIMIT ?`
   rows, err := db_inst.db.Query(query, 20, 10)
   if err != nil {
      return &User_list_array{}, false
   }

   defer rows.Close()

   var user_list User_list_array
   for rows.Next() {
      u := User{}
      err := rows.Scan(&u.id, &u.username, &u.password, &u.createAt)
      if err != nil {
         continue
      }

      user_list.users_list = append(user_list.users_list, u)
   }

   err = rows.Err()

   if err != nil {
      log.Fatalf("fetchall failed %v", err)
      return &User_list_array{}, false
   }

   return &user_list, true
}

func (db_inst *DB_Instance) db_delete() bool {
   query := `DELETE FROM users WHERE id=?`

   _, err := db_inst.db.Exec(query, 2)
   if err != nil {
      log.Fatalf("delete failed %v", err)
      return false
   }

   return true
}

func (db_inst *DB_Instance) db_update() bool {
   query := `update users set username=?,password=? WHERE id=?`
   r, err := db_inst.db.Exec(query, "user2", "pass3", 1)

   if err != nil {
      log.Fatalf("update failed %v", err)
      return false
   }

   row, err := r.RowsAffected()
   if err != nil {
      log.Fatalf("update rows failed %v", err)
      return false
   }

   fmt.Println("RowsAffected ", row)

   return true
}

func (user *User) print() {

   timeObj := time.Unix(int64(user.createAt), 10)

   fmt.Printf("%d,  %s,  %s,  %s\n", user.id, user.username, user.password,
      timeObj.Format("2006-01-02 15:04:05"))
}

func (users *User_list_array) prints() {
   fmt.Printf("id, username, password, create_at \n")

   for _, user := range users.users_list {
      user.print()
   }

   //fmt.Printf("%#v\n", users.users_list)
}

func main() {

   db_inst := db_init()
   if db_inst == nil {
      fmt.Println("db init failed ")
      return
   }

   /*
      if rv := db_inst.db_insert(); !rv {

         fmt.Println("db insert failed")
      }
   */

   fmt.Println("after fetch")
   if user, rv := db_inst.db_fetch(); rv {

      user.print()
   }

   fmt.Println("after fetchall")
   if users, rv := db_inst.db_fetchall(); rv {

      users.prints()
   }

   fmt.Println("after delete")

   if rv := db_inst.db_delete(); !rv {

      fmt.Println("delete failed")
   }

   fmt.Println("after update")
   if rv := db_inst.db_update(); !rv {

      fmt.Println("update failed")
   }

   fmt.Println("after fetchall")
   if users, rv := db_inst.db_fetchall(); rv {

      users.prints()
   }

}

 

  如下fetchall中user为通过指针传递。

package main

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

const DB_ADDR_PORT = "localhost:3307"
const DB_LOGIN_USER = "root"
const DB_LOGIN_PASS = "usbw"
const DB_TAB = "test"

type DB_Instance struct {
   db *sql.DB
}

type User struct {
   id       int
   username string
   password string
   createAt int
}

type User_ptr_list []*User

func db_init() *DB_Instance {
   dsn := fmt.Sprintf("%s:%s@(%s)/%s", DB_LOGIN_USER, DB_LOGIN_PASS, DB_ADDR_PORT, DB_TAB)
   db, err := sql.Open("mysql", dsn)

   if err != nil {
      log.Fatalf("connect db failed %v", err)
      return nil
   }

   if err := db.Ping(); err != nil {
      log.Fatalf("ping db failed %v", err)
      return nil
   }

   db_inst := new(DB_Instance)
   db_inst.db = db

   //fmt.Printf("db reachable, %T, %T, %#v\n", db, db_inst, db)
   fmt.Printf("db reachable, %T, %T\n", db, db_inst)

   return db_inst
}


func (db_inst *DB_Instance) db_fetch() (*User, bool) {
   user := new(User)

   query := `SELECT id, username, password, create_at FROM users WHERE id=?`
   err := db_inst.db.QueryRow(query, 1).Scan(&user.id, &user.username, &user.password, &user.createAt)

   if err != nil {
      log.Fatalf("queryrow failed %v", err)
      return &User{}, false
   }

   return user, true
}

func (db_inst *DB_Instance) db_fetchall() (User_ptr_list, bool) {
   query := `SELECT id, username, password, create_at FROM users WHERE id < ? LIMIT ?`
   rows, err := db_inst.db.Query(query, 20, 2)
   if err != nil {
      return User_ptr_list{}, false
   }

   defer rows.Close()

   user_list := make([]*User, 0)

   for rows.Next() {
      u := User{}
      err := rows.Scan(&u.id, &u.username, &u.password, &u.createAt)
      if err != nil {
         continue
      }

      user_list = append(user_list, &u)
   }

   err = rows.Err()

   if err != nil {
      log.Fatalf("fetchall failed %v", err)
      return User_ptr_list{}, false
   }

   return user_list, true
}

func print(user *User) {

   timeObj := time.Unix(int64(user.createAt), 10)

   fmt.Printf("%d,  %s,  %s,  %s\n", user.id, user.username, user.password,
      timeObj.Format("2006-01-02 15:04:05"))
}

func (user_list User_ptr_list) iterate(f func(user *User)) {

   fmt.Println("iterate")
   fmt.Printf("id, username, password, create_at \n")

   for _, user := range user_list {
      f(user)
   }
}

func main() {

   db_inst := db_init()
   if db_inst == nil {
      fmt.Println("db init failed ")
      return
   }

   /*
      if rv := db_inst.db_insert(); !rv {

         fmt.Println("db insert failed")
      }
   */
   fmt.Println("after fetch")
   if user, rv := db_inst.db_fetch(); rv {

      print(user)
   }

   fmt.Println("after fetchall")
   if users, rv := db_inst.db_fetchall(); rv {

      users.iterate(print)
   }
}