Go使用Gin写一个对MySQL的增删改查的实现

首先用SQL创建一个包含id、name属性的users表

create table users
(
    id   int auto_incremenjst
        primary key,
    name varchar(255) null
);

查询所有用户信息：

func queryData(db *sql.DB, w http.ResponseWriter) {
	pythonrows, err := db.Query("SELECT * FROM users")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer rows.Close()
	var users []struct {
		ID   int    `json:"id"`
		Name string `json:"name"`
	}
	for rows.Next() {
		var id int
		var name string
		err := rows.Scan(&id, &name)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		users = append(users, struct {
			ID   int    `json:"id"`
			Name string `json:"name"`
		}{id, name})
	}
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(users)
}

根据姓名插入一个用户信息

func insertData(db *sql.DB, name string, w http.ResponseWriter) {
	stmt, err := db.Prepare("INSERT INTO users(name) VALUES(?)")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer stmt.Close()
	res, err := stmt.Exec(name)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	id, _ := res.LastInsertId()
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]int64{"id": id})
}

根据id删除一个用户信息

func deleteData(db *sql.DB, id int, w http.ResponseWriter) {
	stmt, err := db.Prepare("DELETE FROM users WHERE id=?")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer stmt.Close()
	res, err := stmt.Exec(id)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	affected, _ := res.RowsAffected()
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]int64{"deleted": affected})
}

三个方法整合一起放到main.go文件里

package main

import (
	"database/sql"
	"encoding/json"
	"log"
	"net/http"
	"strconv"

	_ "github.com/go-sql-driver/mysql"
	"github.com/gorilla/mux"
)

func main() {
	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test_go")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	r := mux.NewRouter()
	r.HandleFunc("/ujavascriptsers", func(w http.ResponseWriter, r *http.Request) {
		switch r.Method {
		case http.MethodGet:
			queryData(db, w)
		case http.MethodPost:
			var user struct{ Name string }
			if err := json.NewDecoder(r.Body).Decode(&user); err != nil {
				http.Error(w, err.Error(), http.StatusBadRequest)
				return
			}
			insertData(db, user.Name, w)
		}
	}).Methods("GET", "POST")

	r.HandleFunc("/insert", func(w http.ResponseWriter, r *http.Request) {
		if r.Method != htjavascripttp.MethodPost {
			http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
			return
		}
		var user struct{ Name string }
		if err := json.NewDecoder(r.Body).Decode(&user); err != nil {
			http.Error(w, err.Error(), http.StatusBadRequest)
			return
		}
		insertData(db, user.Name, w)
	}).Methods("POST")

	r.HandleFunc("/users/{id}", func(w http.ResponseWriter, r *http.Request) {
		vars := mux.Vars(r)
		idStr := vars["id"]
		id, err := strconv.Atoi(idStr)
		if err != nil {
			http.Error(w, "Invalid ID", http.StatusBadRequest)
			return
		}
		deleteData(db, id, w)
	}).Methods("DELETE")

	log.Println("Server running at http://localhost:8083")
	log.Fatal(http.ListenAndServe(":8083", r))
}

func queryData(db *sql.DB, w http.ResponseWriter) {
	rows, err := db.Query("SELECT * FROM users")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer rows.Close()
	var users []struct {
		ID   int    `json:"id"`
		Name string `json:"name"`
	}
	for rows.Next() {
		var id int
		var name string
		err := rows.Scan(&id, &name)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		users = append(users, struct {
			ID   int    `json:"id"`
			Name string `json:"name"`
		}{id, name})
	}
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(users)
}

func insertData(db *sql.DB, name string, w http.ResponseWriter) {
	stmt, err := db.Prepare("INSERT INTO users(name) VALUES(?)")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer stmt.Close()
	res, err := stmt.Exec(name)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	id, _ := res.LastInsertId()
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]int64{"id": id})
}

func deleteData(db *sql.DB, iandroidd int, w http.ResponseWriter) {
	stmt, err := db.Prepare("DELETE FROM users WHERE id=?")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer stmt.Close()
	res, err := stmt.Exec(id)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	affected, _ := res.RowsAffected()
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(map[string]int64{"deleted": affected})
}

1. 添加mysql的依赖

go get -u github.com/go-sql-driver/mysql

2. 添加gin的依赖

go get -u github.com/gin-gonic/gin

3. 添加对gorm的依赖

go get -u gorm.io/gorm

go.mod文件

module go-backend

go 1.24.0

require (
	github.com/go-sql-driver/mysql v1.9.2
	github.com/gorilla/mux v1.8.1
)

require filippo.io/edwards25519 v1.1.0 // indirect

然后启动

go run main.go

获取用户数据

http://localhost:8083/users

插入用户数据

 http://localhost:8083/users

删除用户数据 

http://localhost:8083/users/4

到此这篇关于Go使用Gin写一个对MySQL的增删改查的实现的文章就介绍到这了,更多相关Gin MySQL增删改查内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！