GoWeb开发：009.Gin连接mysql

创建数据库和表

$ mysql -u root -p
Enter password: 

//创建数据库
mysql -> create database mydb;

//查看所有数据库
mysql> show databases;

//切换数据库
mysql> use mydb;

//创建用户表
mysql> CREATE TABLE IF NOT EXISTS `user`(
    ->    `id` INT UNSIGNED AUTO_INCREMENT,
    ->    `name` VARCHAR(100) NOT NULL,
    ->    PRIMARY KEY ( `id` )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.02 sec)

CREATE TABLE IF NOT EXISTS `user`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

安装mysql

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

github.com/go-sql-driv…

连接数据库

package main

import (
	"database/sql"
	"fmt"

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

func main() {
	//用户名:密码@tcp(IP:端口)/数据库?charset=utf8
	db, err := sql.Open("mysql", "root:386mysql.@tcp(127.0.0.1:3306)/mydb?charset=utf8mb4")
	if err != nil {
		panic(err)
	}
	defer db.Close()
	if err := db.Ping(); err != nil {
		log.Println("数据库连接失败")
		panic(err)
	}
	log.Println("数据库连接成功")
}

插入数据

r.POST("/user", func(c *gin.Context) {
	name := c.PostForm("name")
	result, err := db.Exec("INSERT INTO user(name) VALUES (?)", name)
	if err != nil {
		log.Fatalln(err)
	}
	id, err := result.LastInsertId()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Println("insert user id {}", id)
	c.JSON(200, gin.H{
		"id": id,
	})
})

请求示例：

$ curl -X POST -d 'name=admin' http://localhost:8080/user
{"id":1}

$ curl -X POST -d 'name=peng' http://localhost:8080/user
{"id":2}

$ curl -X POST -d 'name=jie' http://localhost:8080/user
{"id":3}

查询所有数据

r.GET("/users", func(c *gin.Context) {
	rows, err := db.Query("SELECT * FROM user")
	if err != nil {
		log.Fatalln(err)
	}
	defer rows.Close()

	users := make([]User, 0)
	for rows.Next() {
		var user User
		rows.Scan(&user.Id, &user.Name)
		users = append(users, user)
	}
	if err = rows.Err(); err != nil {
		log.Fatalln(err)
	}

	c.JSON(200, gin.H{
		"data": users,
	})
})

请求示例：

$ curl http://localhost:8080/users
{"data":[{"Id":1,"Name":"admin"},{"Id":2,"Name":"peng"},{"Id":3,"Name":"jie"}]}

查询单条数据

r.GET("/user/profile", func(c *gin.Context) {
   id := c.Query("id")
   var user User
   err := db.QueryRow("select * from user where id= ?", id).Scan(&user.Id, &user.Name)
   if err != nil {
   	log.Println(err)
   }
   c.JSON(200, gin.H{
   	"data": user,
   })
})

请求示例：

$ curl 'http://localhost:8080/user/profile?id=1'
{"data":{"Id":1,"Name":"admin"}}

删除数据

r.DELETE("/user/:id", func(c *gin.Context) {
	cid := c.Param("id")
	id, err := strconv.Atoi(cid)
	if err != nil {
		log.Fatalln(err)
	}
	result, err := db.Exec("DELETE FROM user where id=?", id)
	if err != nil {
		log.Fatalln(err)
	}
	ra, err := result.RowsAffected()
	if err != nil {
		log.Fatalln(err)
	}
	msg := fmt.Sprintf("delete user %d successful %d", id, ra)
	c.JSON(200, gin.H{
		"msg": msg,
	})
})

请求示例：

$ curl -X DELETE  'http://localhost:8080/user/1'
{"msg":"delete user 1 successful 1"}

修改数据

r.PUT("/user/:id", func(c *gin.Context) {
	cid := c.Param("id")
	id, err := strconv.Atoi(cid)
	if err != nil {
		log.Fatalln(err)
	}
	user := User{Id: id}
	err = c.Bind(&user)
	if err != nil {
		log.Fatalln(err)
	}

	name := c.PostForm("name")
	result, err := db.Exec("UPDATE user SET name=? where id=?", name, id)
	if err != nil {
		log.Fatalln(err)
	}

	ra, err := result.RowsAffected()
	if err != nil {
		log.Fatalln(err)
	}
	msg := fmt.Sprintf("update user %d successful %d", user.Id, ra)
	c.JSON(200, gin.H{
		"msg": msg,
	})
})

请求示例：

$ curl -X PUT -d 'name=admin' 'http://localhost:8080/user/2'
{"msg":"update user 2 successful 1"}

$ curl -X PUT -d 'name=admin' 'http://localhost:8080/user/1'
{"msg":"update user 1 successful 0"}