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:%v@tcp(%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)
}