package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"time"
)
var db *sql.DB
func initMySQL()(err error){
dsn := "root:root@tcp(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()
}
20. golang之sql操作mysql
最新推荐文章于 2024-05-17 05:54:10 发布