介绍

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

安装：

go get github.com/jmoiron/sqlx

下面的内容我是使用 MySQL 进行测试，在开始之前需要在 MySQL 中新增如下库、表、表数据：

# 建库
mysql> CREATE DATABASE sql_test;
Query OK, 1 row affected (0.01 sec)
 
mysql> use sql_test;
Database changed
# 建表
mysql> 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;
Query OK, 0 rows affected (0.12 sec)
# 插入数据
mysql> insert into user(name, age) value('zhangsan',12),('ls',23);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

准备对应的结构体：

// 由于 sqlx 会利用反射来为结构体对象赋值，所以结构体的字段必须可被外部访问，即字段名首字母必须大写
type User struct {
	ID int
	Name string
	Age int
}

基本使用

连接数据库

var db *sqlx.DB

func initDB() (err error) {
	dsn := "root:root1234@tcp(192.168.0.27: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
}

要明确 sqlx 只是基于 database/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)
	/*
	id:1 name:zhangsan age:12
	*/
}

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

// 查询多条数据示例
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)
	/*
	users:[]main.User{main.User{ID:1, Name:"zhangsan", Age:12}, main.User{ID:2, Name:"ls", Age:23}}
	*/
}

插入、更新和删除

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

// 插入数据
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "zze", 19)
	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, 18, 4)
	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, 4)
	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

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

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

NamedQuery

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

func namedQuery(){
	sqlStr := "SELECT * FROM user WHERE name=:name"
	// 使用map做命名查询
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "zze"})
	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)
	}

	u := User{
		Name: "zze",
	}
	// 使用结构体命名查询，根据结构体字段的 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")
			fmt.Println(err)
			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 id=?"
	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 提供的一个非常方便的函数。

sqlx.In 的批量插入示例

修改 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, err := sqlx.In(
		"INSERT INTO user (name, age) VALUES (?), (?)",
		users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
	)
	if err != nil{
		fmt.Println(err)
	}
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err = db.Exec(query, args...)
	return err
}

func main() {
	initDB()
	var users = []interface{}{
		User{Name: "zhaoliu", Age: 28},
		User{Name: "shenqi", Age: 22},
	}
	BatchInsertUsers2(users)
	/*
	INSERT INTO user (name, age) VALUES (?, ?), (?, ?)
	[zhaoliu 28 shenqi 22]
	*/
}

使用 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() {
	initDB()
	var users = []*User{
		&User{Name: "zhaoliu", Age: 28},
		&User{Name: "shenqi", Age: 22},
	}
	BatchInsertUsers3(users)
}

sqlx.In 的查询示例

关于 sqlx.In 这里再补充一个用法，在 sqlx 查询语句中实现 In 查询和 FIND_IN_SET 函数。即实现 SELECT * FROM user WHERE id in (3, 2, 1); 和S ELECT * 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()重新绑定它
	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 对查询结果进行排序。

转自：

https://www.liwenzhou.com/posts/Go/sqlx/#autoid-0-3-3

目录CONTENT

Go第三方库 sqlx

介绍