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_ST 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() (bool, *User) { 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 false, &User{} } return true, user } func (db_inst *DB_INSTANCE) db_fetchall() (bool, *USER_LIST_ST) { 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 false, &USER_LIST_ST{} } defer rows.Close() var user_list USER_LIST_ST 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 false, &USER_LIST_ST{} } return true, &user_list } 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("id %d, username %s, password %s, create_at %s\n", user.id, user.username, user.password, timeObj.Format("2006-01-02 15:04:05")) } func (users *USER_LIST_ST) prints() { fmt.Printf("id, username, password, create_at \n") for _, user := range users.users_list { 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")) } fmt.Printf("%#v\n", users.users_list) } func main() { db_inst := db_init() if db_inst == nil { fmt.Println("db init failed ") return } /* rv := db_inst.db_insert() if !rv { fmt.Println("db insert failed") } */ fmt.Println("after fetch") rv, user := db_inst.db_fetch() if !rv { fmt.Println("db fetch failed") return } user.print() fmt.Println("after fetchall") rv, users := db_inst.db_fetchall() if !rv { fmt.Println("db fetchall failed") return } users.prints() /* fmt.Println("after delete") rv = db_inst.db_delete() if !rv { fmt.Println("delete failed") return } */ /* fmt.Println("after update") rv = db_inst.db_update() if !rv { fmt.Println("update failed") } */ fmt.Println("after fetchall") rv, users = db_inst.db_fetchall() if 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" const MAX_BUF_SIZE = 100 type DB_INSTANCE struct { db *sql.DB } type User struct { id int username string password string createAt int } type USER_LIST struct { users_ptr [MAX_BUF_SIZE]*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_fetchall() (bool, *USER_LIST, int) { 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 false, &USER_LIST{}, 0 } defer rows.Close() count := 0 var user_list USER_LIST for rows.Next() { u := User{} err := rows.Scan(&u.id, &u.username, &u.password, &u.createAt) if err != nil { continue } user_list.users_ptr[count] = &u count++ } err = rows.Err() if err != nil { log.Fatalf("fetchall failed %v", err) return false, &USER_LIST{}, 0 } return true, &user_list, count } func (user_list *USER_LIST) prints(count int) { fmt.Printf("id, username, password, create_at \n") for j := 0; j < count; j++ { user := user_list.users_ptr[j] 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")) } }
标签:err,DB,fmt,db,Golang,inst,user,MySQL,操作 From: https://www.cnblogs.com/wallywl/p/17383191.html