介绍

在项目中通常会使用database/sql连接MySql数据库。sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组拓展。这些拓展中除了大家常用来查询的Get 和Select外还有很多其它强大的功能。

安装sqlx

1
go get github.com/jmoiron/sqlx

基本使用

连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//连接数据库
func initDb() (err error) {
   dataSourceName := `root:Lys52014.@tcp(127.0.0.1:3306)/sql_test`
   //也可以使用MustConnect连接不成功就panic
   //sqlx.MustConnect("mysql", dataSourceName)
   db, err = sqlx.Connect("mysql", dataSourceName)
   if err != nil {
      fmt.Printf("connect DB failed, err: %v\n", err)
      return
   }
   db.SetMaxOpenConns(20)
   db.SetMaxIdleConns(10)
   return
}

查询

单行查询

1
2
3
4
5
6
7
8
9
10
11
//查询单条数据
func QueryRow() {
   sqlStr := `select id, name, age from user where id=?`
   var user User
   err := db.Get(&user, sqlStr, 1)
   if err != nil {
      fmt.Printf("get failed, err: %v\n", err)
      return
   }
   fmt.Printf("user:%#v\n", user)
}

多行查询

1
2
3
4
5
6
7
8
9
10
11
//查询多条数据
func QueryMultiRow() {
   sqlStr := `select id, name, age from user where id>?`
   var users []User
   err := db.Select(&users, sqlStr, 5)
   if err != nil {
      fmt.Printf("query failed, err: %v\n", err)
      return
   }
   fmt.Printf("users:%#v\n", users)
}

插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//插入
func InsertRow() {
   sqlStr := `insert into user(name, age) values(?, ?)`
   result, err := db.Exec(sqlStr, "王大锤", 32)
   if err != nil {
      fmt.Printf("insert failed, err: %v\n", err)
      return
   }
   //新插入数据的Id
   insertId, err := result.LastInsertId()
   if err != nil {
      fmt.Printf("get insertId failed, err: %v\n", err)
      return
   }
   fmt.Printf("insertId is :%v\n", insertId)
}

更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//更新
func UpdateRow() {
   sqlStr := `update user set age=? where id=?`
   result, err := db.Exec(sqlStr, 18, 1)
   if err != nil {
      fmt.Printf("update failed, err: %v\n", err)
      return
   }
   //影响的行数
   affected, err := result.RowsAffected()
   if err != nil {
      fmt.Printf("get affected failed, err: %v\n", err)
      return
   }
   fmt.Printf("affectedRow is :%v\n", affected)
}

删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//删除数据
func DeleteRow() {
   sqlStr := `delete from user where id=?`
   result, err := db.Exec(sqlStr, 11)
   if err != nil {
      fmt.Printf("delete failed, err: %v\n", err)
      return
   }
   //影响的行数
   affected, err := result.RowsAffected()
   if err != nil {
      fmt.Printf("get affected failed, err: %v\n", err)
      return
   }
   fmt.Printf("affectedRow is :%v\n", affected)
}

NamedExec

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

map Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
//绑定SQL语句与map中同名的字段
func InsertUserWithMap() {
   sqlStr := `insert into user(name, age) values(:name , :age)`
   result, err := db.NamedExec(sqlStr, map[string]interface{}{
      "name": "王大锤",
      "age":  33,
   })
   if err != nil {
      fmt.Printf("insertWithMap failed, err: %v\n", err)
      return
   }
   //新插入数据的Id
   insertId, err := result.LastInsertId()
   if err != nil {
      fmt.Printf("get insertId failed, err: %v\n", err)
      return
   }
   fmt.Printf("insertId is :%v\n", insertId)
}

struct Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
//绑定SQL语句与结构体中同名的字段
func InsertUserWithStruct() {
   sqlStr := `insert into user(name, age) values(:name , :age)`
   user := User{
      Name: "陆小凤",
      Age:  25,
   }
   result, err := db.NamedExec(sqlStr, user)
   if err != nil {
      fmt.Printf("insertWithStruct failed, err: %v\n", err)
      return
   }
   //新插入数据的Id
   insertId, err := result.LastInsertId()
   if err != nil {
      fmt.Printf("get insertId failed, err: %v\n", err)
      return
   }
   fmt.Printf("insertId is :%v\n", insertId)
}

NamedQuery

map Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
//使用map进行查询
func NamedQueryWithMap() {
   sqlStr := `select id, name, age from user where name=:name`
   rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "王大锤"})
   if err != nil {
      fmt.Printf("queryByMap failed, err: %v\n", err)
      return
   }
   defer rows.Close()
   for rows.Next() {
      var user User
      //使用StructScan才能直接传入结构体地址进行解析
      err := rows.StructScan(&user)
      if err != nil {
         fmt.Printf("scan failed, err:%v\n", err)
         continue
      }
      fmt.Printf("user: %#v", user)
   }
}

struct Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
//使用struct进行查询
func NamedQueryWithStruct() {
   sqlStr := `select id, name, age from user where name=:name`
   user := User{
      Name: "陆小凤",
   }
   rows, err := db.NamedQuery(sqlStr, user)
   if err != nil {
      fmt.Printf("queryByStruct failed, err: %v\n", err)
      return
   }
   defer rows.Close()
   for rows.Next() {
      var user User
      //使用StructScan才能直接传入结构体地址进行解析
      err := rows.StructScan(&user)
      if err != nil {
         fmt.Printf("scan failed, err:%v\n", err)
         continue
      }
      fmt.Printf("user: %#v", user)
   }
}

事务操作

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

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
//事务操作
func Transaction() (err error) {
	//开启事务
	tx, err := db.Beginx()
	if err != nil {
		fmt.Printf("transaction begin failed, err:%v\n", err)
		return err
	}
	//统一事务处理
	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			//回滚之后重新抛出panic
			panic(p)
		} else if err != nil{
			fmt.Println("Rollback")
			//err不为空时回滚
			tx.Rollback()
		}else {
			//无异常无panic，则提交事务
			err = tx.Commit()
			fmt.Println("Commit")
		}
	}()

	sqlStr1 := `update user set age=20 where id=?`
	result, err := tx.Exec(sqlStr1, 1)
	if err != nil {
		return err
	}
	affected1, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if affected1 != 1 {
		return errors.New("exec sqlSrt1 failed")
	}

	sqlStr2 := `update user set age=50 where id=?`
	result2, err := tx.Exec(sqlStr2, 2)
	if err != nil {
		return err
	}
	affected2, err := result2.RowsAffected()
	if err != nil {
		return err
	}
	if affected2 != 1 {
		return errors.New("exec sqlSrt2 failed")
	}
	return
}

sqlx.In

sqlx.In批量插入

表结构：

1
2
3
4
5
6
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
//定义接收数据库返回的结构体
type User struct {
   Id int `db:"id"`
   Name string `db:"name"`
   Age  int `db:"age"`
}

bindvars(绑定变量)

查询占位符?内部称为bindvars(查询占位符)，它非常重要。他们仅用于参数化，不允许更改SQL语句的结构。使用bindvars尝试参数化列或表名将不起作用。

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

就是有多少个User就拼接多少个(?, ?)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
//自己拼接语句实现批量插入
func BatchInsertUsers(users []*User){
   //存放(?, ?)的slice
   valueStrings := make([]string, 0, len(users))
   //存放value的slice
   valueArgs := make([]interface{}, 0, len(users) * 2)
   //遍历users准备相关数据
   for _, user := range users{
      //此处占位符要与插入值的个数对应
      valueStrings = append(valueStrings, "(?, ?)")
      valueArgs = append(valueArgs, user.Name)
      valueArgs = append(valueArgs, user.Age)
   }
   //自行拼接要执行的具体语句
   stmt := fmt.Sprintf(`insert into user (name, age) values %s`, strings.Join(valueStrings, ","))
   fmt.Println(stmt)
   fmt.Println(valueArgs)
   _, _ = db.Exec(stmt, valueArgs...)
}

使用sqlx.In实现批量插入

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

1
2
3
4
5
6
// themselves to a driver Value.
type Valuer interface {
   // Value returns a driver Value.
   // Value must not panic.
   Value() (Value, error)
}

Example:

1
2
3
4
//使用sqlx，In实现批量插入需要结构体`driver.Value接口`
func (u User) Value()(driver.Value, error){
   return []interface{}{u.Name, u.Age}, nil
}

使用sqlx.In实现批量插入：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//使用sqlx,In实现批量插入
func BatchInsertUserWithSqlxIn(users []interface{}){
   sqlStr := `insert into user (name, age) values (?), (?), (?)`
   //如果arg实现了driver.Valuer, sqlx.In会通过调用Value()来展开它
   query, args, _ := sqlx.In(sqlStr, users...)
   //查看生成的query
   fmt.Println(query)
   //查看生成的args
   fmt.Println(args)
   _, err := db.Exec(query, args...)
   if err != nil {
      fmt.Printf("exec failed, err:%#v\n", err)
   }
}

使用NamedExec实现批量插入

1
2
3
4
5
6
7
8
9
//使用NamedExec实现批量插入
func BatchInsertUserWithNamedExec(users []*User){
   sqlStr := `insert into user (name, age) values (:name, :age)`
   _, err := db.NamedExec(sqlStr, users)
   if err != nil {
      fmt.Println("NamedExec failed, err: %#v\n", err)
      return
   }
}

sqlx.In批量查询

在sqlx查询语句中实现In查询和FIND_IN_SET函数。

1
2
select * id, name, age from user where id in (3, 2, 1);
select * id, name, age from user where id in (3, 2, 1) order by find_in_set(id, '3, 2, 1');

In查询

批量查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//Sqlx.In 批量查询
func QueryByIdsWithSqlIn(ids []int){
   sqlStr := `select id, name, age from user where id in (?)`
   query, args, err := sqlx.In(sqlStr, ids)
   if err != nil {
      return
   }
   fmt.Println(query)
   fmt.Println(args)
   //sqlx.In返回带'?' bindvar的查询语句，我们使用Rebind()重新绑定它
   db.Rebind(query)
   var users []User
   err = db.Select(&users, query, args...)
   if err != nil {
      fmt.Printf("Query failed, err: %#v\n", err)
      return
   }
   for _, user := range users{
      fmt.Printf("user: %#v\n", user)
   }
}

In查询和FIND_IN_SET函数

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
//in查询和FIND_IN_SET函数
func QueryAndOrderByIds(ids []int){
   sqlStr := `select id, name, age from user where id in (?) order by find_in_set(id, ?)`
   //动态填充id
   strIds := make([]string, 0, len(ids))
   for _, id := range ids {
      strIds = append(strIds, fmt.Sprintf("%d", id))
   }
   query, args, err := sqlx.In(sqlStr, strIds, strings.Join(strIds, ","))
   if err != nil {
      fmt.Printf("sqlx.In failed, err:%#v\n", err)
      return
   }
   fmt.Println(query)
   fmt.Println(args)
   //sqlx.In 返回带'?' bindvar语句， 使用Rebind()重新绑定它
   db.Rebind(query)

   var users []User
   db.Select(&users, query, args...)
   for _, user := range users{
      fmt.Printf("user: %#v\n", user)
   }
}