golang MySQL實現對資料庫表儲存獲取操作範例

DROP DATABASE VUE;
create database if not exists vue;
use vue;

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;

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
}

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
}

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) {
	....
}

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

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

created() {
    this.axios
      .get("queryGameblog")
      .then((response) => {
        if (!response.data) {
          this.gameBlog = [];
          return;
        }
        this.gameBlog = response.data;
      })
      .catch((err) => {
        console.log(err);
      });
  },

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
}

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

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);
  });