23-Go更强大的库-sqlx

sqlx介绍

在项目中我们通常可能会使用database/sql连接mysql数据库。sqlx可以认为是go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。这些扩展中除了大家常用来查询的，还有其他强大的功能。

安装sqlx

go get github.com/jmoiron/sqlx

基本使用

连接数据库

package main

import (
	"database/sql"
	"fmt"

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

var db *sql.DB

func initDB() (err error) {
	dsn := "root:123456@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	//使用connect连接数据库
	db, err := sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed,err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(20)
	return

}

func main() {
	err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
		return
	}
}

查询

查询单行数据示例代码如下：

func queryRowDemo() {
	sqlStr := "select id,name,age from user where id=?"
	var u user
	err := db.Get(&u, sqlStr, 3)
	if err != nil {
		fmt.Printf("get failed,err:%v\n", err)
		return
	}
	fmt.Println(u)

}

查询多行数据示例代码如下：

//多行查询
func queryMultiRowDemo() {
	sqlStr := "select id,age,name 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.Println(users)

}

插入、更新、删除

sqlx中的exec方法与原生sql中的exec使用基本一致：

//插入数据
func insertDemo() {
	sqlStr := "insert into user(name,age) values (?,?)"
	ret, err := db.Exec(sqlStr, "天通苑小王子", 19)
	if err != nil {
		fmt.Printf("insert db failed,err:%v\n", err)
		return
	}

	Tid, err := ret.LastInsertId() //查插入数据的id
	if err != nil {
		fmt.Printf("get id failed,err:%v\n", err)
		return
	}
	fmt.Printf("insert success id is %v\n", Tid)
}

//更新数据
func updateDemo() {
	sqlStr := "update user set age=? where id=?"
	ret, err := db.Exec(sqlStr, 28, 6)
	if err != nil {
		fmt.Printf("update failed,err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() //操作影响的行数
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Printf("update success affect rows %v\n", n)
}

//删除数据
func deleteDemo() {
	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("affect failed, err:%v\n", err)
		return
	}
	fmt.Println(n)
}

NamedExec

DB.NmaedExec方法用来绑定SQL语句与结构体或map中的同名字段。

//NamedExec 示例
func insertDemo2() (err error) {
	sqlStr := "insert into user (name,age) values (:name,:age)"
	_, err = db.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "七米",
			"age":  28,
		})
	return
}

NamedQuery

与DB.NamedExec同理，这里是支持查询。

//nameQuery示例
func nameQuery() {
	sqlStr := "select * from user where name=:name"
	//使用map做命名查询
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "七米"})
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	for rows.Next() {
		var u user
		err := rows.StructScan(&u)
		if err != nil {
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}

	u := user{Name: "王磊"}
	//使用结构体命名查询，根据结构体字段的db tag进行映射
	rows2, err := db.NamedQuery(sqlStr, u)
	if err!=nil{
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows2.Next(){
		var u user
		err:=rows2.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}

}

事务操作

对于事务操作，我们可以使用sqlx中提供的db.Beginx()和tx.Exec()方法。示例代码如下：

func transactionDemo2()(err error) {
	tx, err := db.Beginx() // 开启事务
	if err != nil {
		fmt.Printf("begin trans failed, err:%v\n", err)
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p) // re-throw panic after Rollback
		} else if err != nil {
			fmt.Println("rollback")
			tx.Rollback() // err is non-nil; don't change it
		} else {
			err = tx.Commit() // err is nil; if Commit returns error update err
			fmt.Println("commit")
		}
	}()

	sqlStr1 := "Update user set age=20 where id=?"

	rs, err := tx.Exec(sqlStr1, 1)
	if err!= nil{
		return err
	}
	n, err := rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	sqlStr2 := "Update user set age=50 where i=?"
	rs, err = tx.Exec(sqlStr2, 5)
	if err!=nil{
		return err
	}
	n, err = rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	return err
}

Sqlx.In

sqlx.In是sqlx提供的一个非常方便的函数。

表结构

为了方便演示插入数据操作，这里创建一个user表，表结构如下：

CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

结构体

定义一个user结构体，字段通过tag与数据库中user表的列一致。

type User struct {
	Name string `db:"name"`
	Age  int    `db:"age"`
}

自己拼接语句实现批量插入

比较笨，但是很好理解。就是有多少个User就拼接多少个(?, ?)。

var DB *sqlx.DB

type User struct {
	Name string `db:"name"`
	Age  int    `db:"age"`
}

// BatchInsertUsers 自行构造批量插入的语句
func BatchInsertUsers(users []*User) error {
	//存放(?,?)的切片
	valueStrings := make([]string, 0, len(users))
	//存放values的切片
	valueArgs := make([]interface{}, 0, len(users)*2)
	//遍历users准备相关数据
	for _, u := range users {
		//此处占位符要与插入值的个数对应
		valueStrings = append(valueStrings, "(?,?)")
		valueArgs = append(valueArgs, u.Name)
		valueArgs = append(valueArgs, u.Age)
	}
	//自行拼接要执行具体的语句
	fmt.Println(valueStrings) // [(?,?) (?,?) (?,?)]
	fmt.Println(valueArgs...) // 果果 2 娜娜 17 倩倩 13
	stmt := fmt.Sprintf("INSERT INTO user (name,age) VALUES %s", strings.Join(valueStrings, ","))
	fmt.Println(stmt) // INSERT INTO user (name,age) VALUES (?,?),(?,?),(?,?)
	_, err := DB.Exec(stmt, valueArgs...)
	return err
}


func main(){
  err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
		return
	}
	
	defer DB.Close()
	u1 := User{Name: "果果", Age: 2}
	u2 := User{Name: "娜娜", Age: 17}
	u3 := User{Name: "倩倩", Age: 13}

	//方法一
	users := []*User{&u1, &u2, &u3}
	err = BatchInsertUsers(users)
	if err != nil {
		panic(err)
	}
}

使用sqlx.In实现批量插入

前提是需要我们的结构体实现driver.Valuer接口：

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

使用sqlx.In实现批量插入代码如下：

var DB *sqlx.DB

type User struct {
	Name string `db:"name"`
	Age  int    `db:"age"`
}

// batchInsertUser2 使用sqlx.In 帮我们拼接语句和参数，注意传入的参数是[]interface{}
func BatchInsertUsers2(users []interface{}) error {
	fmt.Println(users...)
	query, args, _ := sqlx.In(
		"INSERT INTO user (name,age) VALUES (?),(?),(?)",
		users..., //如果arg实现了driver.Value, sqlx.In会通过调用Value()来展开他
	)
	fmt.Println(query)   //查看生成的querystring  INSERT INTO user (name,age) VALUES (?, ?),(?, ?),(?, ?)
	fmt.Println(args...) //查看生成的args  果 21 娜 27 倩 43
	_, err := DB.Exec(query, args...)
	return err
}


func main(){
  err := initDB()
	if err != nil {
		fmt.Printf("init DB failed,err:%v\n", err)
		return
	}
	
	defer DB.Close()
	u1 := User{Name: "果果", Age: 2}
	u2 := User{Name: "娜娜", Age: 17}
	u3 := User{Name: "倩倩", Age: 13}
  
  users := []interface{}{u1, u2, u3}
	err = BatchInsertUsers2(users)
	if err != nil {
		fmt.Println(err)
	}
}

使用NamedExec实现批量插入

注意 ：该功能需1.3.1版本以上，并且1.3.1版本目前还有点问题，sql语句最后不能有空格和;，详见issues/690。

使用NamedExec实现批量插入的代码如下：

// BatchInsertUsers3 使用NamedExec实现批量插入
func BatchInsertUsers3(users []*User) error {
	_, err := DB.NamedExec("INSERT INTO user (name, age) VALUES (:name, :age)", users)
	return err
}

sqlx.In查询示例

关于sqlx.In这里再补充一个用法，在sqlx查询语句中实现In查询和FIND_IN_SET函数。即实现SELECT * FROM user WHERE id in (3, 2, 1);和SELECT * FROM user WHERE id in (3, 2, 1) ORDER BY FIND_IN_SET(id, '3,2,1');。

in查询

select id, name, age from user where id in (?,?,?), (101,103,105);

insert into user (name, age) values((?,?),(?,?)) , ((jade,18), (lishuo,28))

查询id在给定id集合中的数据。

//QueryByIDs 根据给定ID查询

func QueryByIDs(ids []int) (users []User, err error) {
	//动态填充id
	query, args, _ := sqlx.In("SELECT name,age FROM user WHERE id IN (?)", ids)
	// if err != nil {
	// 	return
	// }
	fmt.Println(query)
	// sqlx.In 返回带`?` 的查询语句,我们使用rebind()重新绑定他
	query = DB.Rebind(query)
	fmt.Println(query)

	err = DB.Select(&users, query, args...)
	return
}

in查询和FIND_IN_SET函数

查询id在给定id集合的数据并维持给定id集合的顺序。

// QueryAndOrderByIDs 按照指定id查询并维护顺序
func QueryAndOrderByIDs(ids []int)(users []User, err error){
	// 动态填充id
	strIDs := make([]string, 0, len(ids))
	for _, id := range ids {
		strIDs = append(strIDs, fmt.Sprintf("%d", id))
	}
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	if err != nil {
		return
	}

	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = DB.Rebind(query)

	err = DB.Select(&users, query, args...)
	return
}