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
基本使用
连接数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 } |
查询
查询单行数据示例代码如下:
1 2 3 4 5 6 7 8 9 10 11 | // 查询单条数据示例 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) } |
查询多行数据示例代码如下:
1 2 3 4 5 6 7 8 9 10 11 | // 查询多条数据示例 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) } |
插入、更新和删除
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 | // 插入数据 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() // 新插入数据的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
DB.NamedExec
方法用来绑定SQL语句与结构体或map中的同名字段。
1 2 3 4 5 6 7 8 9 | 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
同理,这里是支持查询。
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 | 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 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()
方法。示例代码如下:
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 | 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
提供的一个非常方便的函数。
sqlx.In的批量插入示例
表结构
为了方便演示插入数据操作,这里创建一个user
表,表结构如下:
1 2 3 4 5 6 | 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表的列一致。
1 2 3 4 | 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
尝试参数化列或表名将不起作用:
1 2 3 4 5 | // ?不能用来插入表名(做SQL语句中表名的占位符) db.Query( "SELECT * FROM ?" , "mytable" ) // ?也不能用来插入列名(做SQL语句中列名的占位符) db.Query( "SELECT ?, ? FROM people" , "name" , "location" ) |
自己拼接语句实现批量插入
比较笨,但是很好理解。就是有多少个User就拼接多少个(?, ?)
。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | // 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
接口:
1 2 3 | func (u User) Value() (driver.Value, error) { return [] interface {}{u.Name, u.Age}, nil } |
使用sqlx.In
实现批量插入代码如下:
1 2 3 4 5 6 7 8 9 10 11 | // 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
分支代码:
1 | go get github.com/jmoiron/sqlx@master |
使用NamedExec
实现批量插入的代码如下:
1 2 3 4 5 | // BatchInsertUsers3 使用NamedExec实现批量插入 func BatchInsertUsers3(users []*User) error { _, err := DB.NamedExec( "INSERT INTO user (name, age) VALUES (:name, :age)" , users) return err } |
把上面三种方法综合起来试一下:
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 | 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:%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集合中的数据。
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 } |
in查询和FIND_IN_SET函数
查询id在给定id集合的数据并维持给定id集合的顺序。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | // 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对查询结果进行排序。
预处理
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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用于嵌入式属性和方法访问的相同优先级规则分配给字段。
它的普遍用法是在许多表之间共享表模型的公共部分,例如:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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和“创建”字段:
1 2 3 4 5 | type Employee struct { BossID uint64 EmployeeID uint64 Person } |
请注意,sqlx过去一直为非嵌入式结构支持此功能,由于用户使用此功能来定义关系并将相同的结构两次嵌入,这最终造成了混乱。
1 2 3 4 | type Child struct { Father Person Mother Person } |
这会引起一些问题。
在Go中,阴影后代字段是合法的;
如果嵌入式示例中的Employee定义了Name,则它将优先于Person的Name。
但是模糊的选择器是非法的,并且会导致运行时错误。
如果我们想为Person and Place创建一个快速的JOIN类型,我们将id列放在哪里,这是通过它们的嵌入式AutoIncr定义的?
会有错误吗?
由于sqlx构建字段名称到字段地址的映射的方式,因此当您扫描到结构时,它不再知道在遍历结构树期间是否两次遇到过名称。
因此,与Go不同,StructScan将选择遇到的具有该名称的“第一个”字段。
由于Go struct字段是从上到下排序的,并且sqlx进行广度优先遍历以维护优先级规则,因此它将在最浅,最顶层的定义中发生。
例如,在类型中:
1 2 3 4 | type PersonPlace struct { Person Place } |
StructScan将在Person.AutoIncr.ID中设置一个id列结果,也可以作为Person.ID访问。
为避免混淆,建议您改用AS在SQL中创建列别名。
扫描目的地安全
默认情况下,如果列未映射到目标中的字段,则StructScan将返回错误。
这模仿了Go中未使用的变量之类的处理方式,但与标准库编组器(例如encoding / json)的行为方式不匹配。
由于通常以比解析JSON更受控制的方式执行SQL,并且这些错误通常是编码错误,因此决定默认返回错误。
与未使用的变量一样,被忽略的列会浪费网络和数据库资源,并且如果不通过映射器让您知道未发现某些内容,那么就很难在结构标记中及早发现不兼容的映射或错字。
尽管如此,在某些情况下可能还是希望忽略没有目的地的列。
为此,每种句柄类型都有一个Unsafe方法,该方法会在安全性关闭的情况下返回该句柄的新副本:
1 2 3 4 5 6 7 | 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,则可以创建一个副本以在库中使用,以确保特定的默认映射:
1 2 3 4 5 6 | // 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。
您可以通过直接设置它来进一步自定义数据库上的映射:
1 2 3 4 | 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自动返回切片或结果图:
1 2 3 4 5 6 7 8 9 10 11 | 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“},破坏地图中的结果之一。
参考链接:
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)