Golang操作sqlx库处理MySQL数据库

增删改查

package main

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

func initDB() (err error) {
	//dsn := "root:gree123@tcp(127.0.0.1:13306)/sql_demo?charset=utf8mb4&parseTime=True"
	dsn := "root:123456@tcp(127.0.0.1:3306)/sql_demo?charset=utf8mb4&parseTime=True&loc=Local"
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

type user struct {
	Id   int    `db:"id"`
	Age  int    `db:"age"`
	Name string `db:"name"`
}

// 查询示例
func query() {
	sqlStr := "select id,name,age from user where id>?"
	var users []user
	err := db.Select(&users, sqlStr, 1)
	if err != nil {
		fmt.Printf("get failed err:%v\n", err)
		return
	}
	//fmt.Printf("users:%#v\n", users)
	for i := 0; i < len(users); i++ {
		fmt.Printf("name:%v  age:%d id:%d\n", users[i].Name, users[i].Age, users[i].Id)
	}
}

// 插入数据
func insert(name string, age int) {
	sqlStr := "insert into user(name, id) values(?,?)"
	ret, err := db.Exec(sqlStr, name, age)
	if err != nil {
		fmt.Printf("insert failed err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() //新插入的ID
	if err != nil {
		fmt.Printf("get LastInsertID failed failed, error:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

//更新数据
func update(age int, name string, id int) {
	sqlStr := "update user set age=?, name=? where id=?"
	ret, err := db.Exec(sqlStr, age, name, id)
	if err != nil {
		fmt.Printf("update failed, error:", err)
		return
	}
	n, err := ret.RowsAffected() //操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed,  error:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

//删除数据
func delete(id int) {
	sqlStr := "delete from user where id=?"
	ret, err := db.Exec(sqlStr, 40)
	if err != nil {
		fmt.Printf("delete failed, error:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() //操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed,  error:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

func main() {
	if err := initDB(); err != nil {
		panic(err)
	}
	fmt.Println("connect mysql database success!")
	query()
	insert("福尔摩斯", 40)
	query()
	update(37, "王凯", 40)
	query()
	delete(40)
	query()
}

NamedExec和NamedQuery操作

package main

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

func initDB() (err error) {
	//dsn := "root:gree123@tcp(127.0.0.1:13306)/sql_demo?charset=utf8mb4&parseTime=True"
	dsn := "root:123456@tcp(127.0.0.1:3306)/sql_demo?charset=utf8mb4&parseTime=True&loc=Local"
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

type user struct {
	Id   int    `db:"id"`
	Age  int    `db:"age"`
	Name string `db:"name"`
}

// NamedQuery 查询示例
func queryUser() {
	sqlStr := "SELECT * FROM user WHERE name=:name"
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "钢铁侠"})
	if err != nil {
		fmt.Printf("db.NameQuery failed, error:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var u user
		rows.StructScan(&u)
		fmt.Printf("user:%#v\n", u)
	}

}

// NamedExec  插入数据
func insertUser() (err error) {
	_, err = db.NamedExec(`INSERT INTO user (name,age) VALUES (:name,:age)`,
		map[string]interface{}{
			"name": "钢铁侠",
			"age":  35,
		})
	return
}



func main() {
	if err := initDB(); err != nil {
		panic(err)
	}
	fmt.Println("connect mysql database success!")
	queryUser()
	insertUser()
	queryUser()

}

sqlx.In批量插入

package main

import (
	"database/sql/driver"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

func initDB() (err error) {
	//dsn := "root:gree123@tcp(127.0.0.1:13306)/sql_demo?charset=utf8mb4&parseTime=True"
	dsn := "root:123456@tcp(127.0.0.1:3306)/sql_demo?charset=utf8mb4&parseTime=True&loc=Local"
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

type user struct {
	Id   int    `db:"id"`
	Age  int    `db:"age"`
	Name string `db:"name"`
}

func (u user) Value() (driver.Value, error) {
	return []interface{}{u.Name, u.Age}, nil
}

// BatchInsertUser2 使用sqlx.In帮我们拼接语句和参数，注意传入的参数是[]interface{}
func BatchInsertUser2(users []interface{}) error {
	query, args, _ := sqlx.In(
		"INSERT INTO user (name, age) VALUES (?), (?), (?)",
		users..., //如果args实现了args driver.Valuer, sqlx，In 会通过Value()来展开它
	)
	fmt.Println(query)
	fmt.Println(args) //查看生成的args
	_, err := db.Exec(query, args...)
	if err != nil {
		fmt.Printf("insert error:%v", err)
	}
	return err
}

//批量插入的另一种方式
func BatchInsertUser3(users []*user) error {
	sqlStr := "INSERT INTO user (name, age) VALUES (:name, :age)"
	_, err := db.NamedExec(sqlStr, users)
	return err
}

// NamedQuery 查询示例
func queryUser() {
	sqlStr := "SELECT * FROM user WHERE name=:name"
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "钢铁侠"})
	if err != nil {
		fmt.Printf("db.NameQuery failed, error:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var u user
		rows.StructScan(&u)
		fmt.Printf("user:%#v\n", u)
	}

}

func main() {
	if err := initDB(); err != nil {
		panic(err)
	}
	fmt.Println("connect mysql database success!")
	queryUser()
	u1 := user{Name: "姚明", Age: 41}
	u2 := user{Name: "谢娜", Age: 37}
	u3 := user{Name: "何炅", Age: 50}
	users := []interface{}{u1, u2, u3}
	BatchInsertUser2(users)
	queryUser()
	u4 := user{Name: "汪涵", Age: 42}
	u5 := user{Name: "杜海涛", Age: 32}
	users2 := []*user{&u4, &u5}
	BatchInsertUser3(users2)

}