我的Vue之旅 09 數據數據庫表的存儲與獲取實現 Mysql + Golang
第四期 · 將部分數據存儲至Mysql，使用axios通過golang搭建的http服務器獲取數據。
新建數據庫
DROP DATABASE VUE;
create database if not exists vue;
use vue;
JSON TO MYSQL
JSON to MySQL (transform.tools)
DROP DATABASE VUE;
create database if not exists vue;
use vue;
CREATE TABLE gameblog (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
text VARCHAR(255),
img VARCHAR(255)
);
insert into gameblog(title,text,img) values
("Games of the Month: surrealist solitaire puzzles","What』s that? You need more games? I hear you, anonymous hapi fan.We』ve reached the part of the year when games start coming out fast","//xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102184434_1.jpg"),
("Games of the Month: Puzzles!","Sometimes you need a good puzzle game, just something to throw all of your attention at and ignore anything else going on. Well if that sometime for you is right now, then you』re in luck because in this Games of the Month","//xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102184434_2.jpg"),
("The next hapi Creator Day is July 29th!","I don』t think I』m allowed to make the entire body of this post 「Thenext itch.io Creator Day is taking place on Friday July 29th.」 I mean it』s true, we are hosting the next itch.io Creator Day on Friday July 29th but I should probably write more here.","//xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102184434_3.jpg");
select * from gameblog;
CREATE TABLE game (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
text VARCHAR(255),
img VARCHAR(255),
price decimal(6,2) default 0,
web boolean default 0
# TODO 發佈時間
# TODO 瀏覽量
# TODO 評論量
# TODO 熱度綜合指標
);
CREATE TABLE tag (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255)
);
CREATE TABLE gametag (
gameid INT,
tagid INT
);
# TODO 外鍵
insert into game(id,title,text,img,price,web) values
(1,"Late Night Mop","A haunted house cleaning simulator.","//xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102193135_1.png",0,0),
(2,"an average day at the cat cafe","A haunted house cleaning simulator.","//xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102193135_2.png",0,1),
(3,"Corebreaker","A fast-paced action-platform shooter game with roguelike elements.","//xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102193135_3.png",19.99,0),
(4,"Atuel","Traverse a surrealist landscape inspired by the Atuel River in Argentina.","//xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/11/20221102193135_5.png",0,0);
insert into tag values
(1,"Difficult"),
(2,"Fast-Paced");
insert into gametag values
(3,1),
(3,2),
(4,1);
DELIMITER $$
CREATE PROCEDURE gamelist()
BEGIN
# TODO
END $$
DELIMITER ;
select a.title,a.text,img,price,web,if(group_concat(c.title separator "#") is null ,"", group_concat(c.title separator "#")) as tag from game a left join gametag b on a.id = b.gameid left join tag c on b.tagid = c.id group by a.id;
本地圖片上傳OSS圖床得到靜態資源的持久地址，我使用的是PicGo圖床工具。
SQL TO GOLANG STRUCT
在線sql轉golang struct – 球兒工具 (qetool.com)
config.go
為了方便mysql服務器的配置，寫一個配置文件。
package mysql_vue
import "database/sql"
func GetMySQLDB() (db *sql.DB, err error) {
dbDriver := "mysql"
dbUser := "root"
dbPass := "sql2008"
dbName := "vue"
db, err = sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
return
}
gameblog.go
id暫時不需要，後期路由跳轉需要用到，可以先注釋。
package mysql_vue
import (
"encoding/json"
_ "github.com/go-sql-driver/mysql"
)
type Gameblog struct {
// ID int64 `db:"id" json:"id"`
Title string `db:"title" json:"title"`
Text string `db:"text" json:"text"`
Img string `db:"img" json:"img"`
}
func (Gameblog) TableName() string {
return "gameblog"
}
func (Gameblog) QueryGameblog() (json_ []byte, err error) {
// db, err := sql.Open("mysql", "root:sql2008@tcp(127.0.0.1:3306)/vue")
db, err := GetMySQLDB()
checkError(err)
defer db.Close()
// ^ 必須按照順序選取,下面的Scan需要一一對應,如果多了或少了字段會導致Scan錯誤.
results, err := db.Query("SELECT title,text,img FROM gameblog order by id desc")
checkError(err)
var gameBlogs []Gameblog
for results.Next() {
var gameBlog Gameblog
err = results.Scan(&gameBlog.Title, &gameBlog.Text, &gameBlog.Img)
checkError(err)
gameBlogs = append(gameBlogs, gameBlog)
}
json_, err = json.Marshal(gameBlogs)
checkError(err)
return json_, nil
}
http
Simplify server.go
前面我們把評論相關的請求處理代碼寫在了 server.go，移出到 comment.go，並在init初始化中綁定各個請求路徑處理函數。
comment.go
package server
import (
"fmt"
"net/http"
"strconv"
)
type Comment interface {
QueryComment(pid int64) (json_ []byte, err error)
InsertComment(uid, pid int64, text string) (json_ []byte, err error)
DeleteComment(id int64) error
}
func init() {
http.HandleFunc("/insertComment", insertComment)
http.HandleFunc("/deleteComment", deleteComment)
http.HandleFunc("/queryComment", queryComment)
}
func insertComment(w http.ResponseWriter, r *http.Request) {
....
}
func deleteComment(w http.ResponseWriter, r *http.Request) {
....
}
func queryComment(w http.ResponseWriter, r *http.Request) {
....
}
gameblog.go
接口用於確保某個數據庫對象實現了處理函數，否則編譯不通過。
package server
import (
"fmt"
"net/http"
)
type Gameblog interface {
QueryGameblog() (json_ []byte, err error)
}
func init() {
http.HandleFunc("/queryGameblog", QueryGameblog)
}
func QueryGameblog(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
fmt.Fprintf(w, "Only GET Method")
return
}
json, err := gameblog.QueryGameblog()
if err != nil {
fmt.Fprintf(w, "Error Delete")
return
}
fmt.Fprint(w, string(json))
}
server.go
package server
import (
"log"
"net/http"
mysql_vue "wolflong.com/vue_http/lib/mysql"
sq3_vue "wolflong.com/vue_http/lib/sqlite"
)
var comment Comment = sq3_vue.Comment{}
var gameblog Gameblog = mysql_vue.Gameblog{}
func StartServer() {
err := http.ListenAndServe(":1314", nil)
if err != nil {
log.Fatal("ListenAndServe: ", err)
}
}
postman test api
使用 postman 測試當前接口。
Axios
修改 HomeView.vue 的選項卡api，在 created 鉤子函數添加axios請求訪問。
created() {
this.axios
.get("queryGameblog")
.then((response) => {
if (!response.data) {
this.gameBlog = [];
return;
}
this.gameBlog = response.data;
})
.catch((err) => {
console.log(err);
});
},
gamelist.go
查詢語句使用兩次左連接，並用 group_concat 聚合函數，聚合 tag，分解tag的過程可以從服務端遷移到客戶端進行降低性能消耗。
package mysql_vue
import (
"encoding/json"
"strings"
)
type Gamelist struct {
// ID int64 `db:"id" json:"id"`
Title string `db:"title" json:"title"`
Text string `db:"text" json:"text"`
Img string `db:"img" json:"img"`
Price float64 `db:"price" json:"price"`
Tag []string `db:"tag" json:"tag"` // 新添加
Web bool `db:"Web" json:"web"`
}
// type Tag struct {
// ID int64 `db:"id" json:"id"`
// Title string `db:"title" json:"title"`
// }
func (Gamelist) QueryGamelist() (json_ []byte, err error) {
db, err := GetMySQLDB()
checkError(err)
defer db.Close()
results, err := db.Query(`select a.title,a.text,img,price,web,if(group_concat(c.title separator "#") is null ,"", group_concat(c.title separator "#")) as tag from game a left join gametag b on a.id = b.gameid left join tag c on b.tagid = c.id group by a.id;`)
checkError(err)
var GameList []Gamelist
for results.Next() {
var g Gamelist
var tag string
err = results.Scan(&g.Title, &g.Text, &g.Img, &g.Price, &g.Web, &tag)
g.Tag = strings.Split(tag, "#") // 這裡暫且由服務端完成分解
checkError(err)
GameList = append(GameList, g)
}
json_, err = json.Marshal(GameList)
checkError(err)
return json_, nil
}
HTTP
gamelist.go
package server
import (
"fmt"
"net/http"
)
type Gamelist interface {
QueryGamelist() (json_ []byte, err error)
}
func init() {
http.HandleFunc("/queryGamelist", QueryGamelist)
}
func QueryGamelist(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
fmt.Fprintf(w, "Only GET Method")
return
}
json, err := gamelist.QueryGamelist()
if err != nil {
fmt.Fprintf(w, "Error Delete")
return
}
fmt.Fprint(w, string(json))
}
server.go
添加語句 var gamelist Gamelist = mysql_vue.Gamelist{}
Axios
this.axios
.get("queryGamelist")
.then((response) => {
if (!response.data) {
this.latestGames.games = [];
this.mostFeatureGames.games = [];
return;
}
this.latestGames.games = response.data;
this.mostFeatureGames.games = response.data;
})
.catch((err) => {
console.log(err);
});
