Go使用sqlx

在项目中我们通常可能会使用database/sql连接MySQL数据库。sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。

连接数据库

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
package main

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

var db *sqlx.DB

func initDB() (err error) {
   dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
   // 也可以使用MustConnect连接不成功就panic
   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)
   fmt.Printf("connect DB success!!!")
   return
}

func main() {
   initDB()
}

增删改查操作

sqlx是利用反射将数据库字段映射到结构体字段的。

查询

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
package main

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

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

var db *sqlx.DB

func initDB() (err error) {
   dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
   // 也可以使用MustConnect连接不成功就panic
   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
}

// 查询单条数据示例
func queryRowDemo() {
   sqlStr := "select id, name, age from user where id=?"
   var u user
   err := db.Get(&u, sqlStr, 1)
   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 main() {
   err := initDB() // 调用输出化数据库的函数
   if err != nil {
      fmt.Printf("init db failed,err:%v\n", err)
      return
   }
   fmt.Printf("init db success!!!\n")
   queryRowDemo()
}

插入

sqlx中的exec方法与原生sql中的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
53
54
55
56
57
58
59
package main

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

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

var db *sqlx.DB

func initDB() (err error) {
	dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
	// 也可以使用MustConnect连接不成功就panic
	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
}


// 插入数据
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "小亿", 18)
	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() {
	err := initDB() // 调用输出化数据库的函数
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
	fmt.Printf("init db success!!!\n")
	
	insertRowDemo()
}

更新

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
package main

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

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

var db *sqlx.DB

func initDB() (err error) {
   dsn := "root:520jpc...@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
   // 也可以使用MustConnect连接不成功就panic
   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
}



// 更新数据
func updateRowDemo() {
   sqlStr := "update user set age=? where id = ?"
   ret, err := db.Exec(sqlStr, 21, 9)
   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() {
   err := initDB() // 调用输出化数据库的函数
   if err != nil {
      fmt.Printf("init db failed,err:%v\n", err)
      return
   }
   fmt.Printf("init db success!!!\n")
   
   updateRowDemo()
}

删除

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
package main

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

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

var db *sqlx.DB

func initDB() (err error) {
   dsn := "root:520jpc...@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
   // 也可以使用MustConnect连接不成功就panic
   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
}


// 删除数据
func deleteRowDemo() {
   sqlStr := "delete from user where id = ?"
   ret, err := db.Exec(sqlStr, 9)
   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() {
   err := initDB() // 调用输出化数据库的函数
   if err != nil {
      fmt.Printf("init db failed,err:%v\n", err)
      return
   }
   fmt.Printf("init db success!!!\n")
   deleteRowDemo()
}

sqlx.In批量插入

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
package main

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

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

var db *sqlx.DB

func (u user) Value() (driver.Value, error) {
   return []interface{}{u.Name, u.Age}, nil
}
func initDB() (err error) {
   dsn := "root:password@tcp(127.0.0.1:3306)/web2?charset=utf8mb4&parseTime=True"
   // 也可以使用MustConnect连接不成功就panic
   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
}

// BatchInsertUsers2 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertUsers2(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 := db.Exec(query, args...)
   return err
}
func main() {
   err := initDB() // 调用输出化数据库的函数
   if err != nil {
      fmt.Printf("init db failed,err:%v\n", err)
      return
   }
   fmt.Printf("init db success!!!\n")
   u1 := user{Name: "jpc1", Age: 10}
   u2 := user{Name: "jpc2", Age: 12}
   u3 := user{Name: "jpc3", Age: 13}
   users := []interface{}{u1, u2, u3}
   BatchInsertUsers2(users)
}

sqlx.In查询

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

1
2
3
4
5
6
7
8
9
10
11
12
13
// 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()重新绑定它
	query = DB.Rebind(query)

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// 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
}