Go增删改查 ——mysql

scan函数讲解
我们怎样读取用户从键盘（控制台）输入的数据？输入指从键盘或其它标准输入（os.Stdin）读取数据。最简单的方法是使用fmt包里的Scan-或Sscan-系列函数

scan函数会识别空格左右的内容，哪怕换行符号存在也不会影响scan对内容的获取
scanln函数会识别空格左右的内容，但是一旦遇到换行符就会立即结束，不论后续还是否存在需要带输入的内容。

操作数据库

1.导包并连接

import (
"database/sql"
"fmt"
_ "github.com/Go-SQL-Driver/MySQL"

)

db,err := sql.Open("mysql","root:123456@tcp(127.0.0.1:3306)/test?charset=utf8")
if err != nil{
	fmt.Printf("connect mysql fail ! [%s]",err)
}else{
	fmt.Println("connect to mysql success")
}

2.增加

result,err := db.Exec("INSERT INTO person(height,address,head)VALUES (?,?,?)","8","8","8")
if err != nil{
	fmt.Println("insert failed,",err)
}
userId,err:= result.LastInsertId()
rowCount,err:=result.RowsAffected()  //影响行
fmt.Println("user_id:",userId)
fmt.Println("rowCount:",rowCount)

3.查询
// Scan 方法会从输入端读取数据并将处理结果存入接收端，接收端必须是有效的指针。
// Scanln 和 Scan 类似，scan遇到换行当做空格，scanln遇到换行符就停止扫描。

rowsQuery,err := db.Query("SELECT * FROM person WHERE height=7")
if err != nil{
	fmt.Println("select db failed,err:",err)
	return
}
for rowsQuery.Next() {
	var height int
	var head  string
	var address  string
	err = rowsQuery.Scan(&head,&height,&address)// Scan 方法会从输入端读取数据并将处理结果存入接收端，接收端必须是有效的指针。
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println(height,address,head)
}
rowsQuery.Close()
}

4删除

rowsDelete,err:=db.Exec("DELETE FROM person WHERE height=3")

5改

rowsUpdate,err:=db.Exec("UPDATE person SET address=0 WHERE height=7")

代码附录

	package main

	import (
	"database/sql"
	"fmt"
	_ "github.com/Go-SQL-Driver/MySQL"
)
		func main()  {
	/*	db,err :=sql.Open("mysql","root:123456@tcp(127.0.0.1:3306)/person?charset=utf8")*/
		db,err := sql.Open("mysql","root:123456@tcp(127.0.0.1:3306)/test?charset=utf8")
		if err != nil{
			fmt.Printf("connect mysql fail ! [%s]",err)
		}else{
			fmt.Println("connect to mysql success")
		}
		/*result,err := db.Exec("INSERT INTO person(height,address,head)VALUES (?,?,?)","8","8","8")
		if err != nil{
			fmt.Println("insert failed,",err)
		}
		userId,err:= result.LastInsertId()
		rowCount,err:=result.RowsAffected()
		fmt.Println("user_id:",userId)
		fmt.Println("rowCount:",rowCount)*/
	
	
	
		/*rowsQuery,err := db.Query("SELECT * FROM person WHERE height=7")
		if err != nil{
			fmt.Println("select db failed,err:",err)
			return
		}
		for rowsQuery.Next() {
			var height int
			var head  string
			var address  string
			err = rowsQuery.Scan(&head,&height,&address)
			if err != nil {
				fmt.Println(err)
				return
			}
			fmt.Println(height,address,head)
		}
		rowsQuery.Close()*/
	
	/*	rowsDelete,err:=db.Exec("DELETE FROM person WHERE height=3")
	
		fmt.Println(rowsDelete.LastInsertId())*/
	
		rowsUpdate,err:=db.Exec("UPDATE person SET address=0 WHERE height=7")
		fmt.Print(
			rowsUpdate.LastInsertId())
	}

postman发送ＰＯＳＴ请求往数据库增加数据
ｍｙｓｑｌ.go

	import (
"database/sql"
"log"
_ "github.com/Go-SQL-Driver/MySQL"
)

type User struct {
	Id       int    `json:"id" form:"id"`
	Username string `json:"username" form:"username"`
	Password string `json:"password" form:"password"`
}

func DBConn()(db *sql.DB) {
	dbDriver := "mysql"
	dbUser := "root"
	dbPass := "123456"
	dbName := "test"
	db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
	if err != nil { panic(err.Error()) }
	return db }
		func add(user User) (Id int, err error) {

	//1.操作数据库
	db:=DBConn()
	//推迟数据库连接的关闭
	defer db.Close()
	stmt, err := db.Prepare("INSERT INTO person(id,username, password) VALUES (?,?, ?)")
	if err
//将id类型转换
		Id = int(id)
		defer stmt.Close()
		return

main.go

router.POST("/add", func(c *gin.Context) {
	var  u User
	err := c.Bind(&u)
	if err != nil {
		log.Fatal(err)
	}
	Id, err := add(u)
	fmt.Print("id=", Id)
	c.JSON(http.StatusOK, gin.H{
		"message": fmt.Sprintf("%s 插入成功", u.Username),
	})
})