20. golang之sql操作mysql

package main
import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"time"
)
var db *sql.DB

func initMySQL()(err error){
	dsn := "root:[email protected](127.0.0.1:13306)/go_test"
	//去初始化全局的db对象，而不是新声明一个变量
	db,err = sql.Open("mysql",dsn)
	if err != nil {
		panic(err)
	}
	//做完错误检查后，确保db不为nil

	//尝试与数据库建立连接（校验dsn是否正确）
	err = db.Ping()
	if err != nil {
		fmt.Printf("connect to db failed,err: %v\n",err)
		return err
	}
	//根据实际业务设置数值
	db.SetConnMaxLifetime(time.Second*10) // 设置连接可以重复使用的最长时间
	db.SetMaxOpenConns(500) // 最大连接数
	db.SetMaxIdleConns(200) // 最大空闲连接数
	return
}
type user struct {
	id int
	age int
	name string
}
//查询单条数据示例
func queryRowDemo() {
	sqlStr := "SELECT id,name,age FROM user WHERE id=?"
	var u user
	//非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	err := db.QueryRow(sqlStr,1).Scan(&u.id,&u.name,&u.age)
	if err != nil {
		fmt.Printf("scan failed， err: %v\n",err)
		return
	}
	fmt.Printf("id:%d,name:%s,age:%d\n",u.id,u.name,u.age)
}
// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "SELECT id,name,age FROM user WHERE id>?"
	var u user
	rows,err := db.Query(sqlStr,0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n",err)
		return
	}
	//非常重要，关闭rows释放持有的数据库链接
	defer rows.Close()
	for rows.Next() {
		err := rows.Scan(&u.id,&u.name,&u.age)
		if err != nil {
			fmt.Printf("scan failed, err: %v\n",err)
			return
		}
		fmt.Printf("id:%d,name:%s,age:%d\n",u.id,u.name,u.age)
	}
}
//插入数据
func insertRowDemo() {
	sqlStr := "INSERT INTO user(name,age) VALUES(?,?)"
	ret, err := db.Exec(sqlStr,"尹思俣",23)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n",err)
		return
	}
	var theID int64
	theID, err = ret.LastInsertId()
	if err != nil {
		fmt.Printf("get lastinsert ID failed,err:%v\n",err)
		return
	}
	fmt.Printf("insert success, the id is %d\n",theID)
}
//更新数据
func updateRowDemo() {
	sqlStr := "UPDATE user SET age=? WHERE id=?"
	ret,err := db.Exec(sqlStr,23,3)
	if err != nil {
		fmt.Printf("updated failed,err:%v\n",err)
		return
	}
	var n int64
	n,err = ret.RowsAffected()
	if err != nil {
		fmt.Printf("get rowsAffected failed,err:%v\b",err)
		return
	}
	fmt.Printf("updated success, the rows affected is %d\n",n)
}
var a int
//删除数据
func deleteRowDemo() {
	sqlStr := "DELETE FROM user WHERE id=?"
	ret,err := db.Exec(sqlStr,3)
	if err != nil {
		fmt.Printf("delete failed,err:%v\n",err)
		return
	}
	var n int64
	n,err = ret.RowsAffected()
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n",err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n",n)
}
func main() {
	if err := initMySQL();err != nil {
		fmt.Printf("connect to db failed,err:%v\n",err)
	} else {
		fmt.Println("db connect to db success")
	}
	// Close() 用来释放数据库连接的相关资源
	defer db.Close()
	//查询一条数据
	//queryRowDemo()
	//查询多条数据
	//queryMultiRowDemo()
	//插入一条数据
	//insertRowDemo()
	//更新数据
	//updateRowDemo()
	//删除数据
	deleteRowDemo()
}