golang MySQL实现对数据库表存储获取操作示例
目录
新建数据库config.gogameblog.gohttpSimplify server.gocomment.gogameblog.goserver.gopostman test apiAxiosgamelist.goHTTPgamelist.goserver.goAxios新建数据库
将部分数据存储至Mysql，使用axios通过golang搭建的http服务器获取数据。
(资料图片)
sql
DROP DATABASE VUE; create database if not exists vue; use vue;
JSON to MySQL (transform.tools)
sql
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","https://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","https://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.","https://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.","https://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.","https://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.","https://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.","https://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
config.go
为了方便mysql服务器的配置，写一个配置文件。
golang
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暂时不需要，后期路由跳转需要用到，可以先注释。
go
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
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
接口用于确保某个数据库对象实现了处理函数，否则编译不通过。
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
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请求访问。
js
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的过程可以从服务端迁移到客户端进行降低性能消耗。
go
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
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
js
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);
});以上就是golang MySQL实现对数据库表存储获取操作示例的详细内容，更多关于golang MySQL表存储获取的资料请关注脚本之家其它相关文章！
X 关闭
X 关闭
- 15G资费不大降！三大运营商谁提供的5G网速最快？中国信通院给出答案
- 2联想拯救者Y70发布最新预告：售价2970元起 迄今最便宜的骁龙8+旗舰
- 3亚马逊开始大规模推广掌纹支付技术 顾客可使用“挥手付”结账
- 4现代和起亚上半年出口20万辆新能源汽车同比增长30.6%
- 5如何让居民5分钟使用到各种设施？沙特“线性城市”来了
- 6AMD实现连续8个季度的增长 季度营收首次突破60亿美元利润更是翻倍
- 7转转集团发布2022年二季度手机行情报告：二手市场“飘香”
- 8充电宝100Wh等于多少毫安？铁路旅客禁止、限制携带和托运物品目录
- 9好消息！京东与腾讯续签三年战略合作协议 加强技术创新与供应链服务
- 10名创优品拟通过香港IPO全球发售4100万股 全球发售所得款项有什么用处？