21.go之sqlx操作MYSQL数据库

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()
}

推荐参考链接