刘沙河
bigox
2022-04-26
目录

sqlx的使用

在项目中我们通常可能会使用database/sql连接MySQL数据库。本文借助使用sqlx实现批量插入数据的例子，介绍了sqlx中可能被你忽视了的sqlx.In和DB.NamedExec方法。

# 一. 基本操作

# 1. sqlx介绍

  • 在项目中我们通常可能会使用database/sql连接MySQL数据库。sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。这些扩展中除了大家常用来查询的Get(dest interface{}, ...) error和Select(dest interface{}, ...) error外还有很多其他强大的功能。

# 2. 安装sqlx

go get github.com/jmoiron/sqlx

// 拉取master分支最新代码
go get github.com/jmoiron/sqlx@master
1
2
3
4

# 3. 连接数据库

package main

import (
	"fmt"
	"github.com/jmoiron/sqlx"
)

var DbSqlx *sqlx.DB

func InitDbSqlx() (err error) {
    	  //用户:密码@tcp(ip:端口)/数据库?charset=utf8mb4&parseTime=True
	dsn := "root:@tcp(127.0.0.1:3306)/go_mysql_test?charset=utf8mb4&parseTime=True"
	// 也可以使用MustConnect连接不成功就panic
	DbSqlx, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	DbSqlx.SetMaxOpenConns(20)
	DbSqlx.SetMaxIdleConns(10)
	return
}
func main() {
    // sqlx使用
    err := InitDbSqlx()
	if err != nil {
		fmt.Printf("init db failed, err: %v\n", err)
		return
	}
	fmt.Printf("init db success db%v\n", DbSqlx)
    
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

# 4. 查询

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

package main

import "fmt"


// 查询单条数据示例
func QueryRowSqlxDemo(id int) {
	sqlStr := "select id, name, age from user where id=?"
	var u User
	err := DbSqlx.Get(&u, sqlStr, id)
	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 QueryMultiRowSqlxDemo(id int) {
	sqlStr := "select id, name, age from user where id > ?"
	var users []User
	err := DbSqlx.Select(&users, sqlStr, id)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users:%#v\n", users)
}

func main() {
    // sqlx使用
    err := InitDbSqlx()
	if err != nil {
		fmt.Printf("init db failed, err: %v\n", err)
		return
	}
	fmt.Printf("init db success db%v\n", DbSqlx)
    // 查询单条数据示例
	QueryRowSqlxDemo(1)
	// 查询多条数据示例
	QueryMultiRowSqlxDemo(1)
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

# 5. 插入

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

package main

import "fmt"


// 插入数据示例
func InsertRowSqlxDemo(name string, age int) {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := DbSqlx.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 lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)

}
func main() {
    // sqlx使用
    err := InitDbSqlx()
	if err != nil {
		fmt.Printf("init db failed, err: %v\n", err)
		return
	}
	fmt.Printf("init db success db%v\n", DbSqlx)
   // 插入数据
	InsertRowSqlxDemo("RandySunSqlx", 18)
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

# 6. 更新

package main

import "fmt"

// 更新数据示例
func UpdateRowSqlxDemo(id, age int) {
	sqlStr := "update user set age=? where id = ?"
	ret, err := DbSqlx.Exec(sqlStr, age, id)
	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 main() {
    // sqlx使用
    err := InitDbSqlx()
	if err != nil {
		fmt.Printf("init db failed, err: %v\n", err)
		return
	}
	fmt.Printf("init db success db%v\n", DbSqlx)
  	// 更新数据
	UpdateRowSqlxDemo(7, 20)
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

# 7. 删除

package main

import "fmt"


// 删除数据示例
func DeleteRowSqlxDemo(id int) {
	sqlStr := "delete from user where id = ?"
	ret, err := DbSqlx.Exec(sqlStr, id)
	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:%d\n", n)
}

func main() {
    // sqlx使用
    err := InitDbSqlx()
	if err != nil {
		fmt.Printf("init db failed, err: %v\n", err)
		return
	}
	fmt.Printf("init db success db%v\n", DbSqlx)
   // 删除数据
	DeleteRowSqlxDemo(7)
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

# 二. 特殊方法

# 1. NamedExec

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

package main

// 指定map同名字段
func InsertUserSqlxDemo() (err error) {
	sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
	_, err = DbSqlx.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "RandySun2",
			"age":  18,
		})
	return
}
func main() {
    // sqlx使用
    err := InitDbSqlx()
	if err != nil {
		fmt.Printf("init db failed, err: %v\n", err)
		return
	}
	fmt.Printf("init db success db%v\n", DbSqlx)
   // 删除数据
	InsertUserSqlxDemo()
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 2. NamedQuery

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

package main

import "fmt"

/*

@create 2021-08-31-8:34
*/
func NamedQuerySqlxDemo() {
	sqlStr := "SELECT * FROM user WHERE name=:name"
	// 使用map做命名查询
	rows, err := DbSqlx.NamedQuery(sqlStr, map[string]interface{}{"name": "Barry"})
	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)
        // 放到map
		//err := rows.MapScan(&u)
		// 放到切片
		//err := rows.SliceScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}

	u := User{
		Name: "Randy",
	}
	// 使用结构体命名查询，根据结构体字段的 db tag进行映射
	rows, err = DbSqlx.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() {
    // sqlx使用
    err := InitDbSqlx()
	if err != nil {
		fmt.Printf("init db failed, err: %v\n", err)
		return
	}
	fmt.Printf("init db success db%v\n", DbSqlx)
   
	NamedQuerySqlxDemo()
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

# 3. 事务操作

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

package main

import (
	"errors"
	"fmt"
)


// 事务
func TransactionSqlxDemo() (err error) {

	tx, err := DbSqlx.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

}
func main() {
    // sqlx使用
    err := InitDbSqlx()
	if err != nil {
		fmt.Printf("init db failed, err: %v\n", err)
		return
	}
	fmt.Printf("init db success db%v\n", DbSqlx)
   
	TransactionSqlxDemo()
    
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

# 三. sqlx.In

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

# 0. 表结构

为了方便演示插入数据操作，这里创建一个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;
1
2
3
4
5
6

# 0. 结构体

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

type User struct {
	Name string `db:"name"`
	Age  int    `db:"age"`
}
1
2
3
4

# 1. sqlx.In的批量插入示例

# 1.2 bindvars（绑定变量）

查询占位符?在内部称为bindvars（查询占位符）,它非常重要。你应该始终使用它们向数据库发送值，因为它们可以防止SQL注入攻击。database/sql不尝试对查询文本进行任何验证；它与编码的参数一起按原样发送到服务器。除非驱动程序实现一个特殊的接口，否则在执行之前，查询是在服务器上准备的。因此bindvars是特定于数据库的:

  • 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")
1
2
3
4
5

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

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

// BatchInsertUsers 自行构造批量插入的语句
func BatchInsertUsersSqlxDemo(users []*User) error {
	// 存放 (?, ?) 的slice
	valueStrings := make([]string, 0, len(users))
	// 存放values的slice
	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)
	}
	// 自行拼接要执行的具体语句
	stmt := fmt.Sprintf("INSERT INTO user (name, age) VALUES %s",strings.Join(valueStrings, ","))
	fmt.Println("手动拼接sql语句sql:", stmt, valueArgs)
	// 手动拼接sql
	_, err := DbSqlx.Exec(stmt, valueArgs...)
	return err
}

package main

import "fmt"

func main() {
	u1 := User{Name: "RandySun1", Age: 18}
	u2 := User{Name: "RandySun2", Age: 28}
	u3 := User{Name: "RandySun3", Age: 38}
	//
	//// 方法1
	users := []*User{&u1, &u2, &u3}
	err = BatchInsertUsersSqlxDemo(users)
	if err != nil {
		fmt.Printf("BatchInsertUsers failed, err:%v\n", err)
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

手动拼接sql语句sql: INSERT INTO user (name, age) VALUES (?, ?),(?, ?),(?, ?)

# 1.4 使用sqlx.In实现批量插入

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

// BatchInsertInUsersSqlxDemo 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func (u User) Value() (driver.Value, error) {
	return []interface{}{u.Name, u.Age}, nil
}
1
2
3
4

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

package main

import "fmt"

// BatchInsertInUsersSqlxDemo 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertInUsersSqlxDemo(users []interface{}) error {
	query, args, _ := sqlx.In(
		"INSERT INTO user (name, age) VALUES (?), (?), (?)", // 有几个数据，就要有几个占位符
		users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
	)
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err := DbSqlx.Exec(query, args...)
	return err
}



func main() {
	u1 := User{Name: "RandySun1", Age: 18}
	u2 := User{Name: "RandySun2", Age: 28}
	u3 := User{Name: "RandySun3", Age: 38}
	// 方法2
	users2 := []interface{}{u1, u2, u3}
	err = BatchInsertInUsersSqlxDemo(users2)
	if err != nil {
		fmt.Printf("BatchInsertUsers2 failed, err:%v\n", err)
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# 1.5 使用NamedExec实现批量插入

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

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

package main

import "fmt"

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

func main() {
	u1 := User{Name: "RandySun1", Age: 18}
	u2 := User{Name: "RandySun2", Age: 28}
	u3 := User{Name: "RandySun3", Age: 38}
	// 方法3
	users3 := []*User{&u1, &u2, &u3}
	err = BatchInsertNamedExecUsersSqlxDemo(users3)
	if err != nil {
		fmt.Printf("BatchInsertUsers3 failed, err:%v\n", err)
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

把上面三种方法综合起来试一下：

func main() {
	err := initDB()
	if err != nil {
		panic(err)
	}
	defer DB.Close()
	// 方法1
	users := []*User{&u1, &u2, &u3}
	err = BatchInsertUsersSqlxDemo(users)
	if err != nil {
		fmt.Printf("BatchInsertUsers failed, err:%v\n", err)
	}

	// 方法2
	users2 := []interface{}{u1, u2, u3}
	err = BatchInsertInUsersSqlxDemo(users2)
	if err != nil {
		fmt.Printf("BatchInsertUsers2 failed, err:%v\n", err)
	}

	// 方法3
	users3 := []*User{&u1, &u2, &u3}
	err = BatchInsertNamedExecUsersSqlxDemo(users3)
	if err != nil {
		fmt.Printf("BatchInsertUsers3 failed, err:%v\n", err)
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 2. 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');。

# 2.1 in查询

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

package main

import (
	"fmt"
	"github.com/jmoiron/sqlx"
	"strings"
)

// QueryByIds 根据给定ID查询
func QueryByIds(ids []int) (users []User, err error) {
	// 动态填充id
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
	if err != nil {
		return
	}
	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	fmt.Println(query, args, err) 
	query = DbSqlx.Rebind(query) // SELECT name, age FROM user WHERE id IN (?, ?, ?)
	fmt.Println(query)
	err = DbSqlx.Select(&users, query, args...)
	return
}
func main() {
    	// sqlx使用
	err := InitDbSqlx()
	if err != nil {
		fmt.Printf("init db failed, err: %v\n", err)
		return
	}
	fmt.Printf("init db success db%v\n", DbSqlx) //init db success db&{0xc000020ea0 mysql false 0xc00007ea50}
  Ids := []int{3,1,2}
	userList, err := QueryByIds(Ids)
	fmt.Println(userList)  // [{1 18 Randy} {2 30 Jack} {3 200 Barry}]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 2.2 in查询和FIND_IN_SET函数

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

package main

import (
	"fmt"
	"github.com/jmoiron/sqlx"
	"strings"
)

// 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))
	}
	fmt.Println(strIDs)
	query, args, err := sqlx.In("SELECT id, name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	if err != nil {
		return
	}
	fmt.Println(query, args, err)

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

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

func main() {
    	// sqlx使用
	err := InitDbSqlx()
	if err != nil {
		fmt.Printf("init db failed, err: %v\n", err)
		return
	}
	fmt.Printf("init db success db%v\n", DbSqlx)
   
    Ids := []int{3,1,2}
	userList, err := QueryAndOrderByIds(Ids)
	fmt.Println(userList)
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
#Go#
上次更新: 2023/04/16, 18:35:33
Go中的Mutex和RWMutex.md
分布式id 库snowflake和sonyflake

← 分布式id 库snowflake和sonyflake→

最近更新
01
go与http代理
05-24
02
自制申威架构k8s-reloader
12-06
03
Docker Buildx 教程
12-01
更多文章>
Theme by Vdoing | Copyright © 2020-2024 小刘扎扎 | MIT License