【Go】-sqlx库的使用

目录

sqlx介绍

安装sqlx

基本使用

连接数据库

查询

单行查询

多行查询 

Rows和Row

插入、更新和删除

NamedExec

NamedQuery

事务操作

sqlx.In

sqlx.In的批量插入示例

表结构

结构体

bindvars（绑定变量）

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

使用sqlx.In实现批量插入

使用NamedExec实现批量插入

sqlx.In的查询示例

in查询

in查询和FIND_IN_SET函数

---

sqlx介绍

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

---

安装sqlx

        下载 sqlx依赖。

go get github.com/jmoiron/sqlx

---

基本使用

连接数据库

    SetMaxOpenConns表示数据库连接池中允许的最大打开连接数。

    SetMaxIdleConns表示数据库连接池中可以保持空闲状态的最大连接数。

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"  // 不要忘了导入数据库驱动
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

func initDB() (err error) {
	dsn := "user:password@tcp(127.0.0.1:3306)/sql_test?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
}

---

查询

单行查询

    Get 方法是 sqlx 提供的一个便捷方法，用于执行 SQL 查询并扫描结果到一个单一的结构体或指针中。这个方法通常用于获取单个记录，例如通过主键查询。

err := db.Get(&dest, query, args...)

• db：是一个 *sqlx.DB 对象，表示与数据库的连接。
• &dest：是一个指向要填充结果的结构体或变量的指针。
• query：是要执行的 SQL 查询字符串。
• args...：是查询中的参数，用于替换 SQL 语句中的占位符（通常是 ?）。

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

// 查询单条数据示例
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)
}

    Select 方法用于执行 SQL 查询并扫描结果到一个结构体的切片中，通常用于获取多条记录。与 Get 方法不同，Select 方法可以处理查询返回的多行数据，并将每行数据映射到结构体实例中。

err := db.Select(&destSlice, query, args...)

• db：是一个 *sqlx.DB 对象，表示与数据库的连接。
• &destSlice：是一个指向结构体切片的指针，用于接收查询结果。
• query：是要执行的 SQL 查询字符串。
• args...：是查询中的参数，用于替换 SQL 语句中的占位符（通常是 ?）

多行查询 

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

// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "select id, name, age 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.Printf("users:%#v\n", users)
}

Rows和Row

        在 sqlx 库中，Rows 和 Row 是两个用于处理查询结果的接口，它们提供了遍历和访问数据库查询结果的方法。这些接口是对 database/sql 包中的 Rows 和 Row 接口的扩展，增加了一些额外的功能，使得处理复杂查询结果更加方便。

 sqlx.Rows

    sqlx.Rows 是一个用于处理多条查询结果的接口。当你执行一个返回多行数据的查询时，可以使用 sqlx.Rows 来遍历这些结果。

主要方法

• Next() bool: 移动到下一行，如果还有数据则返回 true，否则返回 false。
• Scan(dest ...interface{}) error: 将当前行的数据扫描到提供的变量中。
• Columns() ([]string, error): 返回结果集中的列名。
• Close() error: 关闭 Rows 迭代器，释放资源。

 sqlx.Row

    sqlx.Row 是一个用于处理单条查询结果的接口。当你执行一个返回单行数据的查询时，可以使用 sqlx.Row 来访问这行数据。

主要方法

• Scan(dest ...interface{}) error: 将当前行的数据扫描到提供的变量中。

注意：

        sqlx 库中，当你执行一个查询后，得到的 Rows 结果集会占用数据库连接直到你关闭它。即使你不调用 Scan 方法去读取数据，数据库连接也不会自动释放。因此，如果你不读取查询结果也不关闭 Rows 结果集，这将导致数据库连接不被释放，可能会耗尽数据库的最大连接数，从而影响应用程序的性能和稳定性。

---

插入、更新和删除

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

// 插入数据
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "奶牛", 5)
	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 updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 39, 6)
	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 deleteRowDemo() {
	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("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

---

NamedExec

    NamedExec 方法用于执行带有命名参数的 SQL 语句。命名参数是在 SQL 字符串中通过冒号 : 加上参数名来指定的，这使得 SQL 语句更易于阅读和维护，尤其是在处理复杂的 SQL 查询时。

err := db.NamedExec(query string, arg interface{}) (sql.Result, error)

• db：是一个 *sqlx.DB 对象，表示与数据库的连接。
• query：是要执行的包含命名参数的 SQL 查询字符串。
• arg：是一个包含命名参数值的 map 或结构体。
• 返回值：
  • sql.Result：包含了执行结果的接口，例如插入行数、受影响行数等。
  • error：如果执行过程中出现错误，将返回错误对象。

func insertUserDemo()(err error){
	sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
	_, err = db.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "奶牛",
			"age": 5,
		})
	return
}

---

NamedQuery

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

func namedQuery(){
	sqlStr := "SELECT * FROM user WHERE name=:name"
	// 使用map做命名查询
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "奶牛"})
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next(){
		var u user
        // StructScan 是一个用于将查询结果的行数据扫描到结构体中的接口。StructScan 接口由 Rows 或 Row 对象实现，这些对象提供了 Scan 方法，可以将数据库中的列数据读取到Go的变量中。
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}

	u := user{
		Name: "奶牛",
	}
	// 使用结构体命名查询，根据结构体字段的 db tag进行映射
	rows, err = db.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)
	}
}

---

事务操作

        对于事务操作，我们可以使用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提供的一个非常方便的函数。它允许你构建 SQL 查询中的 IN 子句，这在处理数组或切片类型的查询参数时尤其有用。使用 sqlx.In 可以简化查询的构建过程，同时保持代码的可读性和灵活性。

query, args := sqlx.In("column_name", values...)

• column_name 是数据库表中的列名。
• values 是一个可变参数列表，可以是任何类型的值，通常是切片或数组。
• 函数返回两个值：
  • query 是构建好的 SQL 子句的一部分，例如 "column_name IN (?)"，其中 ? 是参数占位符。
  • args 是一个 []interface{} 类型的切片，包含所有传入的参数值，这些值将被绑定到 SQL 查询中。

sqlx.In的批量插入示例

表结构

为了方便演示插入数据操作，这里创建一个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"`
}

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")

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

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

// BatchInsertUsers 自行构造批量插入的语句
func BatchInsertUsers(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, ","))
	_, err := DB.Exec(stmt, valueArgs...)
	return err
}

使用sqlx.In实现批量插入

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

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

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

// 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
}

使用NamedExec实现批量插入

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

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

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

func main() {
	err := initDB()
	if err != nil {
		panic(err)
	}
	defer DB.Close()
	u1 := User{Name: "奶牛", Age: 5}
	u2 := User{Name: "nunu", Age: 18}
	u3 := User{Name: "黑牛", Age: 10}

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

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

	// 方法3
	users3 := []*User{&u1, &u2, &u3}
	err = BatchInsertUsers3(users3)
	if err != nil {
		fmt.Printf("BatchInsertUsers3 failed, err:%v\n", 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查询

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

// 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()重新绑定。
	// 重新生成对应数据库的查询语句（如PostgreSQL 用 `$1`, `$2` bindvar）
	query = DB.Rebind(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
}

        当然，在这个例子里面你也可以先使用IN查询，然后通过代码按给定的ids对查询结果进行排序。