package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"time"
)
var db *sqlx.DB
func initDB()(err error) {
dsn := "root:root@tcp(127.0.0.1:13306)/go_test?charset=utf8mb4&parseTime=True"
//也可以使用MustConnect连接不成功就panic
db,err = sqlx.Connect("mysql",dsn) // 这里可以校验是否连接上,本质调了一个Open和一个Ping
if err != nil {
fmt.Printf("connect DB failed,err:%v\n",err)
return err
}
db.SetConnMaxLifetime(time.Second * 10)
db.SetMaxOpenConns(20) // 设置与数据库建立连接的最大数目
db.SetMaxIdleConns(10) // 设置连接池中的最大闲置连接数
return
}
type user struct {
ID int `db:"id"`
Age int `db:"age"`
Name string `db:"name"`
}
// 查询单条数据示例
func queryRowDemo() {
sqlStr := "SELECT id,name,age FROM user WHERE id=?"
var u user
err := db.Get(&u,sqlStr,1)
if err != nil {
fmt.Printf("get 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 users []user
err := db.Select(&users,sqlStr,0)
if err != nil {
fmt.Printf("query failed,err: %v\n",err)
return
}
fmt.Printf("users:%#v\n",users)
}
//插入数据
func insertRowDemo() {
sqlStr := "INSERT INTO user(name,age) VALUES(?,?)"
ret,err := db.Exec(sqlStr,"沙河小王子",19)
if err != nil {
fmt.Printf("insert failed, err:%v\n",err)
return
}
theID,err := ret.LastInsertId() // LastInsertId() 当插入新行时,返回新数据的id
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,2)
if err != nil {
fmt.Printf("update failed, err:%v\n",err)
return
}
n, err := ret.RowsAffected()
if (err != nil) {
fmt.Printf("get RowsAffected failed,err:%v\n",err)
return
}
fmt.Printf("update success,affected rows:%d\n",n)
}
//删除数据
func deleteRowDemo() {
sqlStr := "DELETE FROM user WHERE id=?"
ret,err := db.Exec(sqlStr,6)
if err != nil {
fmt.Printf("delete failed,err:%v\n",err)
return
}
n,err := ret.RowsAffected() // 操作影响的行数
if err != nil {
fmt.Printf("get RowsAffected failed,err:%v\n",err)
return
}
fmt.Printf("delete success,affected rows is %v\n",n)
}
func insertUserDemo() {
sqlStr := "INSERT INTO user(name,age) VALUES(:name,:age)"
ret,err := db.NamedExec(sqlStr,map[string]interface{}{
"name": "七牛云",
"age": 28,
})
if err != nil {
fmt.Printf("inser user failed, err:%v\n",err)
return
}
n, err := ret.RowsAffected()
if err != nil {
fmt.Printf("get insert rowsAffected failed,err:%v\n",err)
return
}
fmt.Printf("inser user success,affected data is %d\n",n)
}
func namedQuery() {
sqlStr := "SELECT * FROM user WHERE name=:name"
//使用map做命名查询
rows, err := db.NamedQuery(sqlStr,map[string]interface{}{
"name": "七牛云",
})
if err != nil {
fmt.Printf("select failed, err:%v\n",err)
return
}
defer rows.Close()
for rows.Next() {
var u user
err := rows.StructScan(&u)
if err != nil {
fmt.Printf("scan failed,err:v\n",err)
continue
}
fmt.Printf("user:%#v\n",u)
}
u := user{
Name: "七牛云",
}
//使用结构体命名查询,根据结构体字段的db tag进行映射
rows, err = db.NamedQuery(sqlStr,u)
if err != nil {
fmt.Printf("db.NamedQuery failed,err:%v\n",err)
return
}
defer rows.Close()
for rows.Next() {
var u user
err := rows.StructScan(&u)
if err != nil {
fmt.Printf("scan failed,err:%v\n",err)
continue
}
fmt.Printf("user:%#v\n",u)
}
}
func main() {
if err := initDB();err != nil{
fmt.Printf("init DB failed,err:%v\n",err)
return
}
fmt.Println("init DB success")
//查询单条
//queryRowDemo()
//查询多条
//queryMultiRowDemo()
//插入数据
//insertRowDemo()
//更新数据
//updateRowDemo()
//删除数据
//deleteRowDemo()
//插入用户 namedExec()
//insertUserDemo()
//查询 namedQuery
//namedQuery()
}
21.go之sqlx操作MYSQL数据库
最新推荐文章于 2024-06-21 15:11:13 发布