优秀的第三方操作sql的库-sqlx

参考链接

• 李文周的博客
• http://jmoiron.github.io/sqlx/

介绍

在项目中我们通常可能会使用database/sql连接MySQL数据库。

sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。

这些扩展中除了大家常用来查询的Get(dest interface{}, ...) error和Select(dest interface{}, ...) error外还有很多其他强大的功能。

安装&连接数据库

安装：

go get github.com/jmoiron/sqlx

连接 同样不要忘记了驱动 _ "github.com/go-sql-driver/mysql"

package initialization

import (
	"fmt"

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

func InitSqlxDB() (*sqlx.DB, error) {
	dbUser := "root"
	dbPassword := "testgomysqldriver"
	dbName := "tgo"
	dbURL := "127.0.0.1:3306"
	sqlDSN := fmt.Sprintf("%s:%s@tcp(%s)/%s", dbUser, dbPassword, dbURL, dbName)
	db, err := sqlx.Connect("mysql", sqlDSN)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return db, nil
}

Connect 方法内部实现其实也是 databse/sql 标准库的Open和Ping方法！进行连接和验证！

// Connect to a database and verify with a ping.
func Connect(driverName, dataSourceName string) (*DB, error) {
	db, err := Open(driverName, dataSourceName)
	if err != nil {
		return nil, err
	}
	err = db.Ping()
	if err != nil {
		db.Close()
		return nil, err
	}
	return db, nil
}

然后就是在 main 中初始化了

func main() {
	sqlxDB, err := initialization.InitSqlxDB()
	if err != nil {
		panic(err.Error())
	}
	defer sqlxDB.Close()
}

增删改查！

因为 sqlx 是对database/sql 的超集，所以完全兼容 标准库sql的写法！比如下面的查询操作！

兼容 sql 标准库查询

package main

import (
	"fmt"
	"github.com/jmoiron/sqlx"
	"go-web-0211/initialization"
)

var sqlxDB *sqlx.DB
var err error

type word struct {
	id        int
	word      string
	wordMeans string
}

// 查询单条数据示例
func queryRowDemo(uid int64) (word, error) {
	sqlStr := "select id, word,word_means  from cws_wordbook where id= ?"
	var w word
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	return w, sqlxDB.QueryRow(sqlStr, uid).Scan(&w.id, &w.word, &w.wordMeans)
}

func main() {
	sqlxDB, err = initialization.InitSqlxDB()
	if err != nil {
		panic(any(err.Error()))
	}
	defer sqlxDB.Close()
	w, err := queryRowDemo(1)
	if err != nil {
		panic(any(err.Error()))
	}
	fmt.Println(w.word)
	fmt.Println(w.wordMeans)
}

Get查询！

语法

func (db *DB) Get(dest interface{}, query string, args ...interface{}) error

• 第一个参数是空接口,一般放置的是定义的查询结果对应的定义结构体类型！

• 第二个参数是查询语句

• 第三个参数是查询参数

type word struct {
	id        int
	word      string
	wordMeans string
}

// GetQueryRowDemo 查询单条数据示例
func GetQueryRowDemo(uid int64) (word, error) {
	sqlStr := "select id, word,word_means  from cws_wordbook where id= ?"
	var w word
	return w, sqlxDB.Get(&w, sqlStr, uid)
}

其实内部是通过反射完成的！

所以我们定义结构体的时候，需要这个结构体是对外导出的！

type word struct {
	Id        int   
	Word      string
	WordMeans string
}

仅仅这样是不行的！执行还是会报错 (在结构体实例*main.word 中缺少destination name )

╰─ go run main.go                                                                                                                                                                                                                   ─╯
data base connect successfully!
panic: missing destination name word_means in *main.word

goroutine 1 [running]:
main.main()
        /Users/codehope/GolandProjects/go-web-0211/main.go:41 +0x1f7
exit status 2

首先看到源码的Get方法

func Get(q Queryer, dest interface{}, query string, args ...interface{}) error {
	r := q.QueryRowx(query, args...)
	return r.scanAny(dest, false)
}

首先这里内部执行了一个 QueryRowx 这里其实就能拿到我们查询的信息了！(注意这里的信息，字段对应的数据库字段！)

然后下一步 scanAny 内部的 操作其实就是如何把查询结果反射到我们传入的结构体实例上！

//sqlx.go 精简后的代码

func (r *Row) scanAny(dest interface{}, structOnly bool) error {
  
  // 通过反射获取结构体值类型！
	v := reflect.ValueOf(dest)
	base := reflectx.Deref(v.Type())
	scannable := isScannable(base)
  
  // 这里的 columns 就是查询到数据库中columns的字段！
	columns, err := r.Columns()
	if scannable {
		return r.Scan(dest)
	}
	m := r.Mapper
  
	fields := m.TraversalsByName(v.Type(), columns)
	// if we are not unsafe and are missing fields, return an error
	if f, err := missingFields(fields); err != nil && !r.unsafe {
		return fmt.Errorf("missing destination name %s in %T", columns[f], dest)
	}
	values := make([]interface{}, len(columns))

	err = fieldsByTraversal(v, fields, values, true)
	if err != nil {
		return err
	}
	// scan into the struct field pointers and append to our results
	return r.Scan(values...)
}

大致的流程就是：

1.内部先通过Query查询到数据库的元信息

2.然后通过反射获取查询结果对应的定义结构体类型，

3.所以需要一个定义一个 db tag 让数据库的元信息和定义结构体对应上！

所以我们写的结构体应该是对外导出的且有一个 tag 对应着数据库的原字段。其他的一些方法也是一样的！

type word struct {
	Id        int    `db:"id"`
	Word      string `db:"word"`
	WordMeans string `db:"word_means"`
}

执行测试，这次就ok了

╰─ go run main.go                                                                                                                                                                                                                   ─╯
data base connect successfully!
记忆
memory

Select 多行查询

// SelectRowsDemo 查询多条数据示例
func SelectRowsDemo(uid1 int64, uid2 int64) ([]word, error) {
	sqlStr := "select id, word,word_means  from cws_wordbook where id>=? and id <= ?"
	var w []word
	return w, sqlxDB.Select(&w, sqlStr, uid1, uid2)
}

w, err := SelectRowsDemo(1, 3)
if err != nil {
	panic("查询失败" + err.Error())
}
for i := range w {
	current := w[i]
	fmt.Println(current.Id, current.Word, current.WordMeans)
}

执行结果：

❯ go run main.go
data base connect successfully! 
1 记忆 memory
2 摄像头 camera
3 河粉 Rice noodles

插入、更新和删除

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

插入

func InsertDemo(word_id int64, word string, word_mean string) (sql.Result, error) {
	sqlStr := "insert into cws_wordbook (word_id,word, word_means) values(?,?,?)"
	return sqlxDB.Exec(sqlStr, word_id, word, word_mean)
}

r, err := InsertDemo(123123, "男人", "man")
if err != nil {
	panic(err)
}
w, err := GetQueryRowDemo(40)
if err != nil {
	panic("查询失败" + err.Error())
}
fmt.Println(w)

执行

❯ go run main.go
data base connect successfully! 
{40 男人 man}

更新

func UpdateDemo(word_id int64, word string, word_mean string) (sql.Result, error) {
	sqlStr := "update cws_wordbook set word = ?, word_means = ?  where word_id = ?"
	return sqlxDB.Exec(sqlStr, word, word_mean, word_id)
}

r, err := UpdateDemo(123123, "女人", "woman")
if err != nil {
	panic(err)
}
fmt.Print(r.RowsAffected())
w, err := GetQueryRowDemo(40)
if err != nil {
	panic("查询失败" + err.Error())
}
fmt.Println(w)

执行

❯ go run main.go
data base connect successfully! 
1 <nil>{40 女人 woman}

删除

func DeleteDemo(word_id int64) (sql.Result, error) {
	sqlStr := "delete from  cws_wordbook where word_id = ?"
	return sqlxDB.Exec(sqlStr, word_id)
}

r, err := DeleteDemo(123123)
if err != nil {
	panic(err)
}
fmt.Print(r.RowsAffected())
w, err := GetQueryRowDemo(40)
if err != nil {
	panic("查询失败" + err.Error())
}
fmt.Println(w)

执行

❯ go run main.go
data base connect successfully! 
1 <nil>panic: 查询失败sql: no rows in result set  # 代表删除失败了，已经查不到这个数据了！

goroutine 1 [running]:
main.main()
        /Users/hope/sdy/temp_go_test/main.go:59 +0x2f4
exit status 2

NamedExec

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

我们可以把 SQL语句 的查询占位符 ？ 换成 :字段名

例如：下面的 name 和 age

sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"

Map 映射 NamedExec

NamedExec 我们可以传入一个 map结构，包含name 和 age字段！那么map中对应的字段值会映射到 sql 语句中！

func NamedExecDemo(word_id int64, word string, word_mean string) (sql.Result, error) {
	sqlStr := "insert into cws_wordbook (word_id, word, word_means) values( :word_id, :word, :word_means )"
	return sqlxDB.NamedExec(sqlStr, map[string]interface{}{
		"word_id":    word_id,
		"word":       word,
		"word_means": word_mean,
	})
}

先执行一下插入

r, err := NamedExecDemo(123456789, "cup", "被子")
if err != nil {
	fmt.Println(err.Error())
}
fmt.Println(r.RowsAffected())

然后去查

w, err := GetQueryRowDemo(42)
if err != nil {
	panic(err.Error())
}
fmt.Print(w.Word, w.WordMeans)

执行

❯ go run main.go
data base connect successfully! 
cup被子

结构体 映射 NamedExec (结构体映射，要保证结构体 tag 正确！)

func NamedExecDemo(word_id int64, _word string, word_mean string) (sql.Result, error) {
	sqlStr := "insert into cws_wordbook (word_id,word, word_means) values(:word_id,:word,:word_means)"
	return sqlxDB.NamedExec(sqlStr, &word{
		Id:        word_id,
		Word:      _word,
		WordMeans: word_mean,
	})
}

NamedQuery

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

Map 映射 NamedQuery

func NamedQuery(word string) (*sqlx.Rows, error) {
	sqlStr := "select id, word ,word_means  from cws_wordbook where word=:word"
	return sqlxDB.NamedQuery(sqlStr, map[string]interface{}{"word": word})
}

r, err := NamedQuery("cup")
if err != nil {
	panic(err.Error())
}
for r.Next() {
	var w word
	err := r.StructScan(&w)
	if err != nil {
		panic(err.Error())
	}
	fmt.Print(w.Word, w.WordMeans) // cup被子
}

执行

❯ go run main.go
data base connect successfully! 
cup被子% 

结构体 映射 NamedQuery

func NamedQuery(_word string) (*sqlx.Rows, error) {
	sqlStr := "select id, word ,word_means  from cws_wordbook where word=:word"
	return sqlxDB.NamedQuery(sqlStr, &word{
		Word: _word,
	})
}

事务操作

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

方便演示，这里创建一个账户表

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

然后创建两个账户，分别有100块钱在里面！完成的操作是在一个事务中，ch给zh转账50元！

id	username	money
1	   ch	    100
2	   zh	    100

sqlxDB.Begin() 开启事务

tx.Commit() 提交事务

tx.Rollback() 回滚事务

事务中，任何异常我们都会panic掉！我们使用 recover 函数来监听 panic 如果存在 panic 我们就把事务回滚，反之则commit

来看下面的代码，我们在事务中，自己抛出了一个 panic

func txDemo() {
	// 开启事务
	tx, err := sqlxDB.Begin()

	// panic 恢复
	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			fmt.Println("回滚！")
		} else {
			tx.Commit()
		}
	}()
	if err != nil {
		panic("事务开启失败" + err.Error())
	}
	
	queryStr := "select username,money from account where username = ?"
	sqlstr := "update account set money = ? where username = ?"
	_, err = tx.Exec(sqlstr, 50, "ch")
	if err != nil {
		panic(err)
	}
	fmt.Println("转账成功！")
	var a Account
	_ = tx.QueryRow(queryStr, "ch").Scan(&a.Username, &a.Money)
	fmt.Println(a.Username, a.Money)
  
	panic("自定义error")
  
	_, err = tx.Exec(sqlstr, 150, "zh")
	fmt.Println("收账成功！")
	if err != nil {
		panic(err)
	}
}

执行

❯ go run main.go
data base connect successfully! 
转账成功！
ch 50
回滚！

可以看到，在事务中！ ch 扣钱成功的！而且扣完的钱也是 正确的 50 ！但是因为有panic，所以回滚了，我们去数据库中看一下数据！

可以看到数据库没有被改动！

那么我们把自定义的panic去掉

-	panic("自定义error")

看一下成功的情况！

❯ go run main.go
data base connect successfully! 
转账成功！
ch 50
收账成功！

sqlx.In

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

bindvars（查询占位符)

查询占位符?在内部称为bindvars（查询占位符）,它非常重要。你应该始终使用它们向数据库发送值，因为它们可以防止SQL注入攻击。

• MySQL中使用?
• PostgreSQL使用枚举的$1、$2等bindvar语法
• SQLite中?和$1的语法都支持
• Oracle中使用:name的语法

bindvars的一个常见误解是，它们用来在sql语句中插入值。

它们其实仅用于参数化，不允许更改SQL语句的结构。

例如，使用bindvars尝试参数化 列字段 或 表名 将不起作用：

// ？不能用来插入表名（做SQL语句中表名的占位符）
db.Query("SELECT * FROM ?", "mytable")
 
// ？也不能用来插入列名（做SQL语句中列名的占位符）
db.Query("SELECT ?, ? FROM people", "name", "location")

使用sqlx.In实现批量插入

向account表批量插入数据

type Account struct {
	Id       int    `db:"id"`
	Username string `db:"username"`
	Money    string `db:"money"`
}

需要Account结构体实现 driver.Valuer 接口：

func (a Account) Value() (driver.Value, error) {
	return []interface{}{a.Username, a.Money}, nil
}

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

// BatchInsertAccounts 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertAccounts(accounts []interface{}) error {
	query, args, _ := sqlx.In(
     //  (?), (?), (?) 这里和插入的条数有关系的，这里三个就代表要批量插入三条 这里一般都去动态生成！
		"INSERT INTO account (username, money) VALUES  (?), (?), (?)",
    
		accounts..., // **如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它**
	)
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err := sqlxDB.Exec(query, args...)
	return err
}

动态生成其实也很简单

// BatchInsertAccounts 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertAccounts(accounts []interface{}) error {
  
  // 插入的条数 accountsLength
	accountsLength := len(accounts)
  
	if accountsLength == 0 {
		return fmt.Errorf("nothing to insert")
	}
  // 创建动态 （?）的切片
	values := make([]string, accountsLength)
	for i := range accounts {
		values[i] = "(?)"
	}
  
  // 组合sql
	sqlStr := fmt.Sprintf("INSERT INTO account (username, money) VALUES  %s", strings.Join(values, ","))
	query, args, _ := sqlx.In(
		sqlStr,
		accounts..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
	)
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err := sqlxDB.Exec(query, args...)
	return err
}

测试

u1 := Account{Username: "c1", Money: "181"}
u2 := Account{Username: "c2", Money: "182"}
u3 := Account{Username: "c3", Money: "183"}
accounts := []interface{}{&u1, &u2, &u3}

BatchInsertAccounts(accounts)

❯ go run main.go
data base connect successfully! 
INSERT INTO account (username, money) VALUES  (?, ?),(?, ?),(?, ?)
[c1 181 c2 182 c3 183]

OK的！

使用NamedExec实现批量插入

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

这个写起来就很舒服！ :xxx 和 db tag 对应就可以了！或者结构体字段对应！

// BatchNamedExecInserAccount 使用NamedExec实现批量插入
func BatchNamedExecInserAccount(accounts []*Account) (sql.Result, error) {
	return sqlxDB.NamedExec("INSERT INTO account (username, money) VALUES (:username, :money)", accounts)
}

u1 := Account{Username: "c1", Money: "181"}
u2 := Account{Username: "c2", Money: "182"}
u3 := Account{Username: "c3", Money: "183"}
accounts := []*Account{&u1, &u2, &u3}
r, err := BatchNamedExecInserAccount(accounts)
if err != nil {
	panic(err)
}
fmt.Println(r.RowsAffected())

执行

❯ go run main.go
data base connect successfully! 
3 <nil>

sqlx.In的查询

在sqlx查询语句中实现 In 查询

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

// QueryByIDs 根据给定ID查询
func QueryByIDs(ids []int) (accounts []Account, err error) {
	// 动态填充id
	query, args, err := sqlx.In("SELECT id,username, money FROM account WHERE id IN (?)", ids)
	if err != nil {
		return
	}
	fmt.Println(query)
	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = sqlxDB.Rebind(query)
	err = sqlxDB.Select(&accounts, query, args...)
	return
}

accounts, err := QueryByIDs([]int{12, 13, 14})
if err != nil {
	panic(err.Error())
}
fmt.Print(accounts)

执行

❯ go run main.go
data base connect successfully! 
SELECT id,username, money FROM account WHERE id IN (?, ?, ?)
[{12 c3 183} {13 c1 181} {14 c2 182}]%      

可以看到 sqlxDB.Rebind(query) Rebind 是帮我们完成了动态填充 id 的操作的！

SELECT id,username, money FROM account WHERE id IN (?)=> SELECT id,username, money FROM account WHERE id IN (?, ?, ?)

in查询和FIND_IN_SET函数

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

这里 先了解一下 FIND_IN_SET 函数的用法！

首先他可以当作和 in 一样的用法

SELECT * FROM account WHERE FIND_IN_SET(id,'4,2');

其实语义很容易理解 FIND id IN SET ('4,2')

所以这个查询的结果就是

func QueryAndOrderByIDsInSet(ids []int) (users []Account, 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 * FROM account WHERE FIND_IN_SET(id,?);", strings.Join(strIDs, ","))
	if err != nil {
		return
	}
	err = sqlxDB.Select(&users, query, args...)
	return
}

❯ go run main.go
data base connect successfully! 
[{2 zh 150} {4 c2 182}]%   

根据传入id的顺序进行排序！

SELECT * FROM account WHERE id in (1,2,4) ORDER BY FIND_IN_SET(id,'4,2,1');

那这里 FIND_IN_SET(id,'4,2,1'); 为什么可以排序呢？ FIND_IN_SET(id,'4,2,1'); 返回的是什么呢？

其实返回的是 id 在 后面 idStr 中的位置！

SELECT FIND_IN_SET('b', 'a,b,c,d'); 

那么我们上面的,可以在查的时候我们把 FIND_IN_SET(id,'4,2,1') 也都查出放在结果里！

SELECT *,FIND_IN_SET(id,'4,2,1') FROM account WHERE id in (1,2,4) ORDER BY FIND_IN_SET(id,'4,2,1');

order by 默认为升序，改为降序看看效果！

SELECT *,FIND_IN_SET(id,'4,2,1') FROM account WHERE id in (1,2,4) ORDER BY FIND_IN_SET(id,'4,2,1') DESC;

这样就很容易理解了！!