欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

20. golang之sql操作mysql

程序员文章站 2022-05-30 18:27:04
...
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()
}