第四节：sqlx库使用指南

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

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

基本使用

连接数据库

import ("fmt"_ "github.com/go-sql-driver/mysql"  // 不要忘了导入数据库驱动"github.com/jmoiron/sqlx"
)var db *sqlx.DBfunc initDB() (err error) {dsn := "user:password@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"// 也可以使用MustConnect连接不成功就panicdb, 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 usererr := 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 queryMultiRowDemo() {sqlStr := "select id, name, age from user where id > ?"var users []usererr := db.Select(&users, sqlStr, 0)if err != nil {fmt.Printf("query failed, err:%v\n", err)return}fmt.Printf("users:%#v\n", users)
}

插入、更新和删除

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

// 插入数据
func insertRowDemo() {sqlStr := "insert into user(name, age) values (?,?)"ret, err := db.Exec(sqlStr, "沙河小王子", 19)if err != nil {fmt.Printf("insert failed, err:%v\n", err)return}theID, err := ret.LastInsertId() // 新插入数据的idif 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

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": "七米","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": "七米"})if err != nil {fmt.Printf("db.NamedQuery failed, err:%v\n", err)return}defer rows.Close()for rows.Next(){var u usererr := 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 usererr := 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 errfmt.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表，表结构如下：

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 {// 存放 (?, ?) 的slicevalueStrings := make([]string, 0, len(users))// 存放values的slicevalueArgs := 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) // 查看生成的querystringfmt.Println(args)  // 查看生成的args_, err := DB.Exec(query, args...)return err
}

使用NamedExec实现批量插入

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

使用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: 18}u2 := User{Name: "q1mi", Age: 28}u3 := User{Name: "小王子", Age: 38}// 方法1users := []*User{&u1, &u2, &u3}err = BatchInsertUsers(users)if err != nil {fmt.Printf("BatchInsertUsers failed, err:%v\n", err)}// 方法2users2 := []interface{}{u1, u2, u3}err = BatchInsertUsers2(users2)if err != nil {fmt.Printf("BatchInsertUsers2 failed, err:%v\n", err)}// 方法3users3 := []*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){// 动态填充idquery, 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){// 动态填充idstrIDs := 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对查询结果进行排序。

参考链接：

Illustrated guide to SQLX