golang 对mysql的增删改查

package main

import (
	"database/sql"
	"fmt"
	"time"

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

const (
	URL           = "127.0.0.1"
	PORT          = "3306"
	DATABASESNAME = "shiro_test"
	USER          = "root"
	PASSWORD      = "123456"
)

type Use struct {
	uid      int
	username string
	password string
	salt     string
}

func main() {
	fmt.Println("Hello World!")
	url := fmt.Sprintf("%v:%[email protected](%v:%v)/%v?charset=utf8", USER, PASSWORD, URL, PORT, DATABASESNAME)
	//	fmt.Println(url)
	DB := mySqlConnet(url)

	//	queryOne(DB, 2)
	//	queryMulti(DB, 1)
	use := new(Use)
	use.username = "你好呀"
	use.password = "helloword!!!"
	use.salt = use.username + use.password
	fmt.Println(use)
	use.uid = 5
	//	insertData(DB, use)
	//	updateData(DB, use)
	deleteData(DB, 5)
	defer DB.Close()
}

func mySqlConnet(url string) (db *sql.DB) {
	DB, _ := sql.Open("mysql", url)
	//最大连接周期
	DB.SetConnMaxLifetime(100 * time.Second)
	//最大连接数
	DB.SetMaxOpenConns(100)
	//闲置连接数
	DB.SetMaxIdleConns(10)

	if err := DB.Ping(); err != nil {
		fmt.Println(err)
	}
	fmt.Println("open database success")
	//defer DB.Close() //关闭连接
	return DB
}

//传入数据库连接，需要查询的行(查询一条数据)
func queryOne(DB *sql.DB, id int) {
	use := new(Use)
	row := DB.QueryRow("select * from user where uid=?", id)
	/*
		查询出来的row，只能用Scan函数获取值
		row必须Scan，否则会导致连接不能关闭，会一直占用连接不会释放资源，直到生命周期
		当 Scan() 方法调用完成后,连接将会被释放到连接池
	*/
	/*
		而Scan函数必须按照查询出来的列一一对应把参数塞进去，多了少了都不行
		当然也可以声明非结构体变量，但是需要一一对应
	*/
	//	var u, name, pass, salt string
	//	row.Scan(&u, &name, &pass, &salt) //一一对应
	//	fmt.Println(u, name, pass, salt)
	if err := row.Scan(&use.uid, &use.username, &use.password, &use.salt); err != nil {
		fmt.Println(err)
		return
	}
	//* 表示指针下的值
	fmt.Println(*use)
}

//查询多条数据
func queryMulti(DB *sql.DB, id int) {
	use := new(Use)
	//Query函数查询多行，返回rows,err
	rows, _ := DB.Query("select * from user where uid>?", id)
	//	fmt.Println("rows:", *rows)
	for rows.Next() { //遍历查询出来的多行
		err := rows.Scan(&use.uid, &use.username, &use.password, &use.salt)
		if err != nil {
			fmt.Println(err)
			return
		}
		fmt.Print(*use)
	}
}

//添加数据
func insertData(DB *sql.DB, use *Use) {
	//插入数据库 reslut为结果集
	result, err := DB.Exec("insert into user(username,password,salt) values(?,?,?)", use.username, use.password, use.salt)
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println("reslut:", result)
	lastInsertId, err := result.LastInsertId() //插入的数据的id，理论是最后一条数据
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println("lastInsertId:", lastInsertId)
	rowsaffected, err := result.RowsAffected() //呗影响到的行数
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println("rowsaffected:", rowsaffected)
}

//更新操作
func updateData(DB *sql.DB, use *Use) {
	//Exec()函数返回一个被影响的结果集
	result, err := DB.Exec("update user set username = ? , password = ? where uid = ?", use.username, use.password, use.uid)
	if err != nil {
		fmt.Println(err)
	}
	//更新操作时不返回LastInsertId，LastInsertId()函数返回值是0
	lastInsertId, err := result.LastInsertId()
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println("lastInsertId:", lastInsertId)
	rowsaffected, err := result.RowsAffected()
	if err != nil {
		fmt.Println(err)
	}
	fmt.Println("rowsaffected:", rowsaffected) //被更新的行数
}

//删除操作
func deleteData(DB *sql.DB, id int) {
	result, err := DB.Exec("delete from user where uid=?", id)
	if err != nil {
		fmt.Println(err)
		return
	}
	//没有新增，所以插入id为0
	lastInsertId, err := result.LastInsertId()
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println("lastInsertId:", lastInsertId)
	rowsaffected, err := result.RowsAffected()
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println("rowsaffected:", rowsaffected)

}