sqlx连接mysql_sqlx库使用指南

sqlx介绍

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

安装sqlx

go get 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:%vn", 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:%vn", err)

return

}

fmt.Printf("id:%d name:%s age:%dn", u.ID, u.Name, u.Age)

}

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

// 查询多条数据示例

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:%vn", err)

return

}

fmt.Printf("users:%#vn", 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:%vn", err)

return

}

theID, err := ret.LastInsertId() // 新插入数据的id

if err != nil {

fmt.Printf("get lastinsert ID failed, err:%vn", 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:%vn", err)

return

}

n, err := ret.RowsAffected() // 操作影响的行数

if err != nil {

fmt.Printf("get RowsAffected failed, err:%vn", err)

return

}

fmt.Printf("update success, affected rows:%dn", n)

}

// 删除数据

func deleteRowDemo() {

sqlStr := "delete from user where id = ?"

ret, err := db.Exec(sqlStr, 6)

if err != nil {

fmt.Printf("delete failed, err:%vn", err)

return

}

n, err := ret.RowsAffected() // 操作影响的行数

if err != nil {

fmt.Printf("get RowsAffected failed, err:%vn", err)

return

}

fmt.Printf("delete success, affected rows:%dn", 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:%vn", err)

return

}

defer rows.Close()

for rows.Next(){

var u user

err := rows.StructScan(&u)

if err != nil {

fmt.Printf("scan failed, err:%vn", err)

continue

}

fmt.Printf("user:%#vn", u)

}

u := user{

Name: "七米",

}

// 使用结构体命名查询，根据结构体字段的 db tag进行映射

rows, err = db.NamedQuery(sqlStr, u)

if err != nil {

fmt.Printf("db.NamedQuery failed, err:%vn", err)

return

}

defer rows.Close()

for rows.Next(){

var u user

err := rows.StructScan(&u)

if err != nil {

fmt.Printf("scan failed, err:%vn", err)

continue

}

fmt.Printf("user:%#vn", u)

}

}

事务操作

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

func transactionDemo2()(err error) {

tx, err := db.Beginx() // 开启事务

if err != nil {

fmt.Printf("begin trans failed, err:%vn", 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提供的一个非常方便的函数。

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实现批量插入

注意 ：该功能目前有人已经推了#285 PR，但是作者还没有发release，所以想要使用下面的方法实现批量插入需要暂时使用master分支的代码：

在项目目录下执行以下命令下载并使用master分支代码：

go get github.com/jmoiron/sqlx@master

使用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}

// 方法1

users := []*User{&u1, &u2, &u3}

err = BatchInsertUsers(users)

if err != nil {

fmt.Printf("BatchInsertUsers failed, err:%vn", err)

}

// 方法2

users2 := []interface{}{u1, u2, u3}

err = BatchInsertUsers2(users2)

if err != nil {

fmt.Printf("BatchInsertUsers2 failed, err:%vn", err)

}

// 方法3

users3 := []*User{&u1, &u2, &u3}

err = BatchInsertUsers3(users3)

if err != nil {

fmt.Printf("BatchInsertUsers3 failed, err:%vn", 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()重新绑定它

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对查询结果进行排序。

预处理

stmt, err := db.Prepare(`SELECT * FROM place WHERE telcode=?`)

row = stmt.QueryRow(65)

tx, err := db.Begin()

txStmt, err := tx.Prepare(`SELECT * FROM place WHERE telcode=?`)

row = txStmt.QueryRow(852)

准备实际上是在数据库上运行准备，因此它需要一个连接及其连接状态。

database / sql从您那里抽象了这一点，从而允许您通过自动在新连接上创建语句，从而在多个连接上同时从单个Stmt对象执行。

Preparex()，它返回一个sqlx.Stmt，它具有sqlx.DB和sqlx.Tx所做的所有句柄扩展：

stmt, err := db.Preparex(`SELECT * FROM place WHERE telcode=?`)

var p Place

err = stmt.Get(&p, 852)

标准的sql.Tx对象还具有Stmt()方法，该方法从预先存在的语句中返回特定于事务的语句。

sqlx.Tx具有Stmtx版本，它将从现有sql.Stmt或sqlx.Stmt创建一个特定于事务的新sqlx.Stmt。

高级扫描

StructScan具有看似复杂的功能。

它支持嵌入式结构，并使用Go用于嵌入式属性和方法访问的相同优先级规则分配给字段。

它的普遍用法是在许多表之间共享表模型的公共部分，例如：

type AutoIncr struct {

ID uint64

Created time.Time

}

type Place struct {

Address string

AutoIncr

}

type Person struct {

Name string

AutoIncr

}

使用上面的结构，Person和Place都将能够从StructScan接收id和创建的列，因为它们嵌入了定义它们的AutoIncr结构。

此功能使您可以快速创建用于联接的临时表。

它也可以递归工作。

通过Go点运算符和StructScan，以下内容将具有“人员名称”及其AutoIncr ID和“创建”字段：

type Employee struct {

BossID uint64

EmployeeID uint64

Person

}

请注意，sqlx过去一直为非嵌入式结构支持此功能，由于用户使用此功能来定义关系并将相同的结构两次嵌入，这最终造成了混乱。

type Child struct {

Father Person

Mother Person

}

这会引起一些问题。

在Go中，阴影后代字段是合法的；

如果嵌入式示例中的Employee定义了Name，则它将优先于Person的Name。

但是模糊的选择器是非法的，并且会导致运行时错误。

如果我们想为Person and Place创建一个快速的JOIN类型，我们将id列放在哪里，这是通过它们的嵌入式AutoIncr定义的？

会有错误吗？

由于sqlx构建字段名称到字段地址的映射的方式，因此当您扫描到结构时，它不再知道在遍历结构树期间是否两次遇到过名称。

因此，与Go不同，StructScan将选择遇到的具有该名称的“第一个”字段。

由于Go struct字段是从上到下排序的，并且sqlx进行广度优先遍历以维护优先级规则，因此它将在最浅，最顶层的定义中发生。

例如，在类型中：

type PersonPlace struct {

Person

Place

}

StructScan将在Person.AutoIncr.ID中设置一个id列结果，也可以作为Person.ID访问。

为避免混淆，建议您改用AS在SQL中创建列别名。

扫描目的地安全

默认情况下，如果列未映射到目标中的字段，则StructScan将返回错误。

这模仿了Go中未使用的变量之类的处理方式，但与标准库编组器(例如encoding / json)的行为方式不匹配。

由于通常以比解析JSON更受控制的方式执行SQL，并且这些错误通常是编码错误，因此决定默认返回错误。

与未使用的变量一样，被忽略的列会浪费网络和数据库资源，并且如果不通过映射器让您知道未发现某些内容，那么就很难在结构标记中及早发现不兼容的映射或错字。

尽管如此，在某些情况下可能还是希望忽略没有目的地的列。

为此，每种句柄类型都有一个Unsafe方法，该方法会在安全性关闭的情况下返回该句柄的新副本：

var p Person

// err here is not nil because there are no field destinations for columns in `place`

err = db.Get(&p, "SELECT * FROM person, place LIMIT 1;")

// this will NOT return an error, even though place columns have no destination

udb := db.Unsafe()

err = udb.Get(&p, "SELECT * FROM person, place LIMIT 1;")

控制名称映射

必须将用作StructScans目标的Struct字段大写，以便sqlx可以访问。

因此，sqlx使用NameMapper来应用字符串.ToLower到字段名称以将它们映射到行结果中的列。

根据您的模式，这并不总是可取的，因此sqlx允许通过多种方式自定义映射。

这些方法中最简单的方法是使用sqlx.DB.MapperFunc将其设置为db句柄，该函数会接收到func(string)字符串类型的参数。

如果您的库需要特定的映射器，并且您不想毒害收到的sqlx.DB，则可以创建一个副本以在库中使用，以确保特定的默认映射：

// if our db schema uses ALLCAPS columns, we can use normal fields

db.MapperFunc(strings.ToUpper)

// suppose a library uses lowercase columns, we can create a copy

copy := sqlx.NewDb(db.DB, db.DriverName())

copy.MapperFunc(strings.ToLower)

每个sqlx.DB都使用sqlx / reflectx包的Mapper在下面实现此映射，并将活动映射器公开为sqlx.DB.Mapper。

您可以通过直接设置它来进一步自定义数据库上的映射：

import "github.com/jmoiron/sqlx/reflectx"

// Create a new mapper which will use the struct field tag "json" instead of "db"

db.Mapper = reflectx.NewMapperFunc("json", strings.ToLower)

替代扫描类型

除了使用Scan和StructScan外，还可以使用sqlx Row或Rows自动返回切片或结果图：

rows, err := db.Queryx("SELECT * FROM place")

for rows.Next() {

// cols is an []interface{} of all of the column results

cols, err := rows.SliceScan()

}

rows, err := db.Queryx("SELECT * FROM place")

for rows.Next() {

results := make(map[string]interface{})

err = rows.MapScan(results)

}

SliceScan返回所有列的[] interface {}接口，在您代表第三方执行查询而无法知道可能返回哪些列的情况下，这很有用。

MapScan的行为相同，但是将列名映射到interface {}值。

这里的一个重要警告是，rows.Columns()返回的结果不包含完全限定的名称，因此从NATURAL JOIN b进行的SELECT a.id，b.id将导致[] string {“ id

“，” id“}，破坏地图中的结果之一。

参考链接：