與MySQL起舞 · Golang原生Web開發入門到微服務

Golang 操作MySQL === #### 課前準備: - 安裝mysql引擎go-sql-driver ``` 普通安裝 go get github.com/go-sql-driver/mysql vgo 安裝 vgo get github.com/go-sql-driver/mysql ``` 相信大家go-sql-driver安裝好了開始golang和mysql玩游戲了數據庫操作在dbops文件夾里面寫 ***** 1.0版本用戶注冊我現在把用戶注冊的api提供出來,你們先不看我的代碼自己實現一遍 ``` package router import ( "GolangWebCourseware/dbops" "GolangWebCourseware/defs" "GolangWebCourseware/response" "encoding/json" "github.com/julienschmidt/httprouter" "io/ioutil" "net/http" ) func AddUser(w http.ResponseWriter,r *http.Request,p httprouter.Params) { bytes, _ := ioutil.ReadAll(r.Body) user := &defs.User{} err := json.Unmarshal(bytes, user) if err != nil { response.SendErrorResponse(w,defs.ErrorRequestBodyParseFailed) return } // 用戶注冊 err = dbops.RegisterUser(user) if err != nil { response.SendErrorResponse(w,defs.ErrorDBError) return } response.SendNormalResponse(w,"success",http.StatusCreated) } ``` 好了我們現在再來完成 mysql部分吧我們先把數據庫的表建立起來 ``` CREATE TABLE `user`( `id` INT UNSIGNED AUTO_INCREMENT, `user` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '用戶名', `password` CHAR(32) NOT NULL DEFAULT '' COMMENT '密碼', `salt` CHAR(32) NOT NULL DEFAULT '' COMMENT '鹽', PRIMARY KEY (`id`), UNIQUE KEY `user`(`user`) )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8; ``` 有些同學這里應該會有問題 password為什么會是定長32? 我們會對密碼md5加密,加密后的密碼就是32位 salt是來做什么的? 密碼 = md5(用戶輸入的密碼 + uuid生成的salt) 這樣就可以提交密碼的安全 ***** 我們現在就來寫md5函數和生成的uuid函數吧這個屬于工具,所以我們寫在utils里面 1.在utils包創建crypto.go 文件加密解密用 ``` package utils import ( "crypto/md5" "encoding/hex" ) func Md5String(str string) string { data := []byte(str) md5Ctx := md5.New() md5Ctx.Write(data) cipherStr := md5Ctx.Sum(nil) return hex.EncodeToString(cipherStr) } ``` 2.創建randoms.go文件隨機相關 ``` package utils import ( "fmt" "os/exec" ) func NewUUID() (string,error) { out, err := exec.Command("uuidgen").Output() oot := fmt.Sprintf("%s", out) return oot,err } // 沒有 - 的uuid func NewUUIDSimplicity() (string,error) { s, e := NewUUID() var u string for _,k :=range s { if k != '-' { u = fmt.Sprintf("%s%s",u,string(k)) } } return u,e } ``` 3.開始寫注冊的sql操作 ``` package dbops import ( "GolangWebCourseware/defs" "GolangWebCourseware/utils" "database/sql" _ "github.com/go-sql-driver/mysql" //注意啊這個要手動引入 (注:前面那個_就是只執行這個包的init方法) "log" ) func RegisterUser(user *defs.User) error { name := user.Name password := user.Password uuid, _ := utils.NewUUIDSimplicity() password = utils.Md5String(password + uuid) //建立conn driverName := "mysql" //選擇數據庫引擎這個我們用的是mysql dsn := "test1:dCKEtZbHdTFr7wdt@(127.0.0.1:3306)/test1" //這個是dns 用戶名:密碼@(ip:端口)/數據庫?charset=utf8 db, e := sql.Open(driverName, dsn) if e != nil{ panic(e.Error()) } //預編譯插入sql 防止sql注入 stmt, e := db.Prepare("INSERT INTO `user`(`user`,`password`,`salt`) VALUE (?,?,?)") defer stmt.Close() //延遲結束資源 if e != nil { log.Println(e.Error()) return e } _, e = stmt.Exec(name, password, uuid) //執行非查詢的sql語句 return e } ``` 同學們發現上面的問題沒有?每次寫sql都要寫數據庫鏈接這樣是不是非常浪費資源啊! 我們在dbops新建一個conn.go ``` package dbops import "database/sql" var ( ConnDb *sql.DB err error ) func init() { //建立conn driverName := "mysql" //選擇數據庫引擎這個我們用的是mysql dsn := "test1:dCKEtZbHdTFr7wdt@(127.0.0.1:3306)/test1" //這個是dns 用戶名:密碼@(ip:端口)/數據庫?charset=utf8 ConnDb, err = sql.Open(driverName, dsn) if err != nil{ panic(err.Error()) } } ``` init方法只會執行一次,每次調用就直接調用這個就行了 ***** 大家還發現沒有這個數據庫的配置是寫死的,我們把他寫成一個配置文件吧,這里把他寫成json配置文件 ``` package dbops import ( "database/sql" "encoding/json" "os" ) type dbConfig struct { DriverName string `json:"driverName"` Dsn string `json:"dsn"` } var ( ConnDb *sql.DB err error ) func init() { config := getDbConfig() //建立conn driverName := config.DriverName //選擇數據庫引擎這個我們用的是mysql dsn := config.Dsn //這個是dns 用戶名:密碼@(ip:端口)/數據庫?charset=utf8 ConnDb, err = sql.Open(driverName, dsn) if err != nil{ panic(err.Error()) } } // 獲取database配置 func getDbConfig() *dbConfig { filePath := "./dbconfig.json" file, e := os.Open(filePath) defer file.Close() if e != nil { panic(e.Error()) } config := &dbConfig{} decoder := json.NewDecoder(file) e = decoder.Decode(config) if e != nil { panic(e) } return config } ``` json文件這樣寫 ``` { "driverName":"mysql", "dsn":"test1:dCKEtZbHdTFr7wdt@(127.0.0.1:3306)/test1" } ``` 完美蛤 ***** # 重頭戲mysql操作相關 - 新增 ``` //預編譯插入sql 防止sql注入 stmt, e := ConnDb.Prepare("INSERT INTO `user`(`user`,`password`,`salt`) VALUE (?,?,?)") defer stmt.Close() //延遲結束資源 if e != nil { log.Println(e.Error()) return e } _, e = stmt.Exec(name, password, uuid) //執行非查詢的sql語句 ``` - 單條查詢 ``` stmt, e := dbConn.Prepare("SELECT `pwd` FROM `users` WHERE `login_name` = ?") defer stmt.Close() if e != nil { log.Printf("%s",e) return "",e } var pwd string e = stmt.QueryRow(loginName).Scan(&pwd) ``` - 多條查詢 ``` stmt, e := dbConn.Prepare(`SELECT comments.id,users.login_name,comments.content FROM comments INNER JOIN users ON comments.author_id = users.id WHERE comments.video_id = ? ORDER BY comments.create_time DESC `) if e != nil { fmt.Println(e.Error()) return nil,e } //多條查詢定義存儲容器 var res []*defs.Comment //rows, e := stmt.Query(vid, from, to) rows, e := stmt.Query(vid) if e != nil && e != sql.ErrNoRows { fmt.Println(e.Error()) return nil,e } //fmt.Printf("%v,%T",rows,rows) for rows.Next() { var id,name,content string if e := rows.Scan(&id, &name, &content);e != nil { return res,e } comment := &defs.Comment{Id: id, VideoId: vid, AuthorName: name, Content: content} res = append(res,comment) } ``` 本節課程代碼:[https://github.com/dollarkillerx/GolangWebCourseware/tree/%E4%B8%8EMySQL%E8%B5%B7%E8%88%9E](https://github.com/dollarkillerx/GolangWebCourseware/tree/%E4%B8%8EMySQL%E8%B5%B7%E8%88%9E) 注意:當今天查詢的時候,沒有數據也會報一個錯,這里就要進行錯誤判斷 ``` err == sql.ErrNoRows { if 相等了話就是沒有數據,反之就是錯誤 } ``` ``` package main import ( "GolangWebCourseware/dbops" "fmt" ) const ( prompt = ` Please enter number of operation: 1. Create new account 2. Show detail of account 3. Deposit 4. Withdraw 5. Make transfer 6. List account by Id 7. List account by balance 8. Delete account 9. Exit ` ) func main() { fmt.Println("Welcome bank of xorm!") forlook: for { fmt.Printf(prompt) var num int _, err := fmt.Scanf("%d\n", &num) if err !=nil { fmt.Printf("輸入錯誤!類型必須為int") continue } switch num { case 1: fmt.Println("Please enter <name> <balance>:") var name string var balance float64 i, err := fmt.Scanf("%s %f\n", &name, &balance) if err != nil{ fmt.Printf("輸錯錯入!") break } if err = dbops.NewAccount(name, balance);err != nil{ panic("插入數據時出錯") } fmt.Printf("%v\n",i) case 2: fmt.Println("Please enter <name> :") var name string _, err := fmt.Scanf("%s\n", &name) if err != nil{ fmt.Printf("輸錯錯入!") break } if account, err := dbops.GetUserByName(name);err != nil{ fmt.Printf("查詢數據不存在") break }else{ fmt.Printf("%#v",account) } case 3: case 9: break forlook } } defer func() { fmt.Println("Bay Bay ...") }() } ``` ### 關于批量查詢補充 ~~~ data,e := stmt.Query(us) ParseRows(data) 就可以轉換為map 寫了一個專門解析的方法 func ParseRows(rows *sql.Rows) []map[string]interface{} { columns, _ := rows.Columns() scanArgs := make([]interface{}, len(columns)) values := make([]interface{}, len(columns)) for j := range values { scanArgs[j] = &values[j] } record := make(map[string]interface{}) records := make([]map[string]interface{}, 0) for rows.Next() { //將行數據保存到record字典 err := rows.Scan(scanArgs...) checkErr(err) for i, col := range values { if col != nil { record[columns[i]] = col } } records = append(records, record) } return records } ~~~