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 } ~~~