golang 数据库驱动 sqlx使用指南

Go 前端之家
前端之家收集整理的这篇文章主要介绍了golang 数据库驱动 sqlx使用指南，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

sqlx使用指南

这边文章主要基于Illustrated guide to SQLX翻译而成。
sqlx是一个go语言包，在内置database／sql包之上增加了很多扩展，简化数据库操作代码的书写。

资源

如果对于go语言的sql用法不熟悉，可以到下面网站学习：
database/sql documentation
go-database-sql tutorial

如果对于golang语言不熟悉，可以到下面网站学习：
The Go tour
How to write Go code
Effective Go
CSDN

由于database/sql接口是sqlx的子集，当前文档中所有关于database/sql的用法同样用于sqlx

开始

安装sqlx 驱动

$ go get github.com/jmoiron/sqlx

本文访问sqlite数据库

$ go get github.com/mattn/go-sqlite3

Handle Types

sqlx设计和database/sql使用方法是一样的。包含有4中主要的handle types：
- sqlx.DB - 和sql.DB相似，表示数据库。
- sqlx.Tx - 和sql.Tx相似，表示transacion。
- sqlx.Stmt - 和sql.Stmt相似，表示prepared statement。
- sqlx.NamedStmt - 表示prepared statement（支持named parameters）

所有的handler types都提供了对database/sql的兼容，意味着当你调用sqlx.DB.Query时，可以直接替换为sql.DB.Query.这就使得sqlx可以很容易的加入到已有的数据库项目中。

此外，sqlx还有两个cursor类型：
- sqlx.Rows - 和sql.Rows类似，Queryx返回。
- sqlx.Row - 和sql.Row类似，QueryRowx返回。

连级到数据库

一个DB实例并不是一个链接，但是抽象表示了一个数据库。这就是为什么创建一个DB时并不会返回错误和panic。它内部维护了一个连接池，当需要进行连接的时候尝试连接。你可以通过Open创建一个sqlx.DB或通过NewDb从已存在的sql.DB中创建一个新的sqlx.DB

var db *sqlx.DB

// exactly the same as the built-in
db = sqlx.Open("sqlite3",":memory:")

// from a pre-existing sql.DB; note the required driverName
db = sqlx.NewDb(sql.Open("sqlite3",":memory:"),"sqlite3")

// force a connection and test that it worked
err = db.Ping()

在一些环境下，你可能需要同时打开一个DB并链接。可以调用connect，这个函数打开一个新的DB并尝试Ping。MustConnect函数在链接出错时会panic。

var err error
// open and connect at the same time:
db,err = sqlx.Connect("sqlite3",":memory:")

// open and connect at the same time,panicing on error
db = sqlx.MustConnect("sqlite3",":memory:")

Querying 101

sqlx中的handle types实现了数据库查询相同的基本的操作语法。
- Exec(…) (sql.Result,error) - 和 database/sql相比没有改变
- Query(…) (*sql.Rows,error) - 和 database/sql相比没有改变
- QueryRow(…) *sql.Row - 和 database/sql相比没有改变

对内置语法的扩展
- MustExec() sql.Result – Exec,but panic on error
- Queryx(…) (*sqlx.Rows,error) - Query,but return an sqlx.Rows
- QueryRowx(…) *sqlx.Row – QueryRow,but return an sqlx.Row

还有下面新的语法

  • Get(dest interface{},…) error
  • Select(dest interface{},…) error
    下面会详细介绍这些方法的使用

Exec

Exec和MustExec从连接池中获取一个连接然后只想对应的query操作。对于不支持ad-hoc query execution的驱动，在操作执行的背后会创建一个prepared statement。在结果返回前这个connection会返回到连接池中。

schema := `CREATE TABLE place ( country text,city text NULL,telcode integer);`

// execute a query on the server
result,err := db.Exec(schema)

// or,you can use MustExec,which panics on error
cityState := `INSERT INTO place (country,telcode) VALUES (?,?)` countryCity := `INSERT INTO place (country,city,?,?)` db.MustExec(cityState,"Hong Kong",852) db.MustExec(cityState,"Singapore",65) db.MustExec(countryCity,"South Africa","Johannesburg",27)

上面代码中 result有两个可能的数据LastInsertId() or RowsAffected(),依赖不同的驱动。
MysqL中，在含有auto-increment key的表中执行插入操作会得到LastInsertId()，在Postgresql中这个信息只有在使用RETURNING语句的row cursor中才会返回。

bindvars

代码中？占位符，称为bindvars，非常重要，你可以总是使用它们来向数据库发送数据，可以用来组织sql Injection攻击。
database/sql并不会对查询语句进行任何的校验，传入什么就发送到server是什么。
除非driver实现特定的接口，query在数据库执行之前会准备好。不同的数据库的bindvars不一样。
- MysqL 使用？
- Postgresql 使用 1, 2等等
- sqlite 使用？或$1
- Oracle 使用:name

其他数据库可能还不一样。你可以使用sqlx.DB.Rebind(string) string函数利用？语法来得到一个适合在当前数据库上执行的query语句。

关于bindvars常见的误解是他们用于插值。他们只用于参数化，不允许改变sql语句的合法接口。例如，下面的用法是会报错的

// doesn't work
db.Query("SELECT * FROM ?","mytable") // also doesn't work db.Query("SELECT ?,? FROM people","name","location")

Query

Query是database/sql中执行查询主要使用的方法，该方法返回row结果。Query返回一个sql.Rows对象和一个error对象。

// fetch all places from the db
rows,err := db.Query("SELECT country,telcode FROM place")

// iterate over each row
for rows.Next() {
    var country string
    // note that city can be NULL,so we use the NullString type
    var city    sql.NullString
    var telcode int
    err = rows.Scan(&country,&city,&telcode)
}

在使用的时候应该吧Rows当成一个游标而不是一系列的结果。尽管数据库驱动缓存的方法不一样，通过Next()迭代每次获取一列结果，对于查询结果非常巨大的情况下，可以有效的限制内存的使用，Scan()利用reflect把sql每一列结果映射到go语言的数据类型如string，[]byte等。如果你没有遍历完全部的rows结果，一定要记得在把connection返回到连接池之前调用rows.Close()。

Query返回的error有可能是在server准备查询的时候发生的，也有可能是在执行查询语句的时候发生的。例如可能从连接池中获取一个坏的连级（尽管数据库会尝试10次去发现或创建一个工作连接）。一般来说，错误主要由错误的sql语句，错误的类似匹配，错误的域名或表名等。

在大部分情况下，Rows.Scan()会把从驱动获取的数据进行拷贝，无论驱动如何使用缓存。特殊类型sql.RawBytes可以用来从驱动返回的数据总获取一个zero-copy的slice byte。当下一次调用Next的时候，这个值就不在有效了，因为它指向的内存已经被驱动重写了别的数据。

Query使用的connection在所有的rows通过Next()遍历完后或者调用rows.Close()后释放。
Queryx和Query行为很相似，不过返回一个sqlx.Rows对象，支持扩展的scan行为。

type Place struct { Country string City sql.NullString TelephoneCode int `db:"telcode"` }

rows,err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    var p Place
    err = rows.StructScan(&p)
}

sqlx.Rowx的主要扩展就是StructScan，可以自动把查下结果扫描到对应结构体中的域（fileld）中。注意结构体中域（field）必须是可导出（exported）的，这样sqlx才能够写入值到结构体中。
正如在上面代码中所示，可以利用db结构体标签来指定结构体field映射到数据库中特定的列名，或者用db.MapperFunc()来指定默认的映射。db默认对结构体的filed名执行strings.Lower后，和数据库的列名进行匹配。关于StructScan,SliceScan,MapScan更详细的内容请参见后面章节advanced scanning

QueryRow

QueryRow从数据库server中获取一列数据。它从连接池中获取一个连级，然后执行Query，返回一个Row对象，这个对象有一个自己的内部的Rows对象。

row := db.QueryRow("SELECT * FROM place WHERE telcode=?",852)
var telcode int
err = row.Scan(&telcode)

不像Query，QueryRow只返回一个Row类型，并不返回error，如果在执行查询过程中出错，则错误通过Scan返回，如果查询结果为空，则返回sql.ErrNoRows。如果Scan本身出错，error同样由scan返回。

QueryRow使用的connection当result返回的时候就关闭了，也就意味着使用QueryRow的时候不能够使用sql.RawByes,因为driver使用sql.RawBytes引用内存，在connection回收后可能也会无效。

QueryRowx返回一个sqlx.Row而不是sql.Row，它实现了跟Rows相同的scan方法如上，同时还有高级的scan方法如下：（更高级的scan方法advanced scanning section）

var p Place
err := db.QueryRowx("SELECT city,telcode FROM place LIMIT 1").StructScan(&p)

Get and Select

Get和Select是一个非常省时的扩展。它们把query和非常灵活的scan语法结合起来。为了更加清晰的介绍它们，我们先讨论下什么是scannalbe：

  • a value is scannable if it is not a struct,eg string,int
  • a value is scannable if it implements sql.Scanner
  • a value is scannable if it is a struct with no exported fields (eg. time.Time)

Get和Select对scannable的类型使用rows.scan，对non-scannable的类型使用rows.StructScan。Get用来获取单个结果然后Scan，Select用来获取结果切片。

p := Place{}
pp := []Place{}

// this will pull the first place directly into p
err = db.Get(&p,"SELECT * FROM place LIMIT 1") // this will pull places with telcode > 50 into the slice pp err = db.Select(&pp,"SELECT * FROM place WHERE telcode > ?",50) // they work with regular types as well var id int err = db.Get(&id,"SELECT count(*) FROM place") // fetch at most 10 place names var names []string err = db.Select(&names,"SELECT name FROM place LIMIT 10")

Get和Select在执行完查询后就会关闭Rows，并且在执行阶段遇到任何问题都会返回错误。由于它们内部使用的StructScan，所以 下文中advanced scanning section讲的特征也适用与Get和Select。

Select可以提高编码小路，但是要注意Select和Queryx是有很大不同的，因为Select会把整个结果一次放入内存。如果查询结果没有限制特定的大小，那么最好使用Query/StructScan迭代方法。

Transactions

为了使用transactions，必须使用DB.Begin()来创建，下面的代码是错误的：

db.MustExec("BEGIN;")
db.MustExec(...)
db.MustExec("COMMIT;")

Exec和其他查询语句会向DB请求一个connection，执行完后就返回到连接池中，并不能保证每次获取的connection就是BEGIN执行时使用的那个，所以正确的做法要使用DB.Begin:

tx,err := db.Begin()
err = tx.Exec(...)
err = tx.Commit()

DB除了Begin之外，还可以使用扩展Beginx()和MustBegin()，返回sqlx.Tx：

tx := db.MustBegin()
tx.MustExec(...)
err = tx.Commit()

sqlx.Tx拥有sqlx.DB拥有的所有的handle extensions.
由于transaction是一个connection状态，所以Tx对象必须绑定和控制单个connection。一个Tx会在整个生命周期中保存一个connection，然后在调用commit或Rollback()的时候释放掉。你在调用这几个函数的时候必须十分小心，否则connection会一直被占用直到被垃圾回收。
由于在一个transaction中只能有一个connection，所以每次只能执行一条语句。在执行另外的query操作之前，cursor对象Row*和Rows必须被Scanned或Closed。如果在数据库给你返回数据的时候你尝试向数据库发送数据，这个操作可能会中断connection。

最后，Tx对象仅仅执行了一个BEGIN语句和绑定了一个connection，它其实并没有在server上执行任何操作。而transaction真实的行为包含locking和isolation，在不同数据库上实现是不同的。

Prepared Statements

对于大部分的数据库来说，当一个query执行的时候，在数据库内部statements其实已经准备好了。然后你可以通过sqlx.DB.Prepare()准备statements，便于后面在别的地方使用。

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)

Prepare实际上在数据库上执行preparation操作，所以它需要一个connection和它的connection state。
database/sql把这部分进行了抽象，自动在新的connection上创建statement，这样开发者就能通过stmt对象在多个connection上并发执行操作。
Preparex()返回一个sqlx.Stmt对象，包含sqlx.DB和sqlx.Tx所有的handle 扩展（方法）。

sql.Tx对象含有一个Stmt()方法，从已存在的statement中返回一个特定于改transaction的statement。
sqlx.Tx同样含有一个Stmtx()方法，从已有的sql.Stmt或sqlx.Stmt中创建一个特定于transaction的sqlx.Stmt。

Query Helpers

“In” Queries

由于database/sql并不会分析你的查询语句然后直接把参数传递给driver，这样对于IN����的查询操作就非常麻烦了。

SELECT * FROM users WHERE level IN (?);

当这条语句在后台prepare为一个statement时，?智慧对应一个参数，但是这样的查询语句往往对应的是多个参数。

var levels = []int{4,6,7}
rows,err := db.Query("SELECT * FROM users WHERE level IN (?);",levels)

这样的情况在sqlx下可以这样做：

var levels = []int{4,7}
query,args,err := sqlx.In("SELECT * FROM users WHERE level IN (?);",levels)

// sqlx.In returns queries with the `?` bindvar,we can rebind it for our backend
query = db.Rebind(query)
rows,err := db.Query(query,args...)

sqlx.In根据传入的参数个数扩充对应的bindvars,然后把参数放到一个新的参数列表中,然后用返回的query语句和args执行query操作。
这个函数只针对?，可以使用db.Rebind来获取一个适合当前数据库的query.
sqlx.In结合select 

names := []string{"Jane","John"}
    query := `SELECT * FROM person WHERE first_name IN (?)`
    query,err := sqlx.In(query,names)
    if err != nil {
        fmt.Println(err)
        return
    }

    query = db.Rebind(query)

    peoples := []Person{}
    fmt.Println(query)
    fmt.Println(args)
    err = db.Select(&peoples,query,args...)
    fmt.Println(peoples)

注意args后面的…不可缺少

结果

SELECT * FROM person WHERE first_name IN (?,?)
[Jane John]
[{John Doe johndoeDNE@gmail.net} {Jane Citizen jane.citzen@example.com} {John Doe johndoeDNE@gmail.net} {Jane Citizen jane.citzen@example.com}]

Named Queries

Named query对于许多数据库包时非常常见的做法。允许开发者通过引用结构体域名或map key的bindvar语法，绑定查询语句中的值。结构体域名的名字转换规则遵循StructScan，使用NameMapper和db结构式标签。除此之外还有两个相关Named查询：

  • NamedQuery(…) (*sqlx.Rows,error) - like Queryx,but with named bindvars
  • NamedExec(…) (sql.Result,error) - like Exec,but with named bindvars
// named query with a struct
p := Place{Country: "South Africa"}
rows,err := db.NamedQuery(`SELECT * FROM place WHERE country=:country`,p)

// named query with a map
m := map[string]interface{}{"city": "Johannesburg"}
result,err := db.NamedExec(`SELECT * FROM place WHERE city=:city`,m)

还有一个额外的类型
- NamedStmt - an sqlx.Stmt which can be prepared with named bindvars

p := Place{TelephoneCode: 50}
pp := []Place{}

// select all telcodes > 50
nstmt,err := db.PrepareNamed(`SELECT * FROM place WHERE telcode > :telcode`)
err = nstmt.Select(&pp,p)

Names query通过解析查询中的:param语法然后用当前数据库支持的bindvar替换，然后在执行数据库操作时按照结构体或map进行映射，所以适应于任何sqlx支持的数据库。
你还可以使用sqlx.Names（使用？bindvar）结合sqlx.IN执行：

arg := map[string]interface{}{ "published": true,"authors": []{8,19,32,44},}
query,err := sqlx.Named("SELECT * FROM articles WHERE published=:published AND author_id IN (:authors)",arg)
query,err := sqlx.In(query,args...)
query = db.Rebind(query)
db.Query(query,args...)

Advanced Scanning

structScan很复杂，支持embedded struct,赋值时使用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和creatd列，因为它们都内嵌了一个结构体AutoIncr,AutoIncr中定义了ID和Created对应列id和creatd。这种方法还支持递归如下：

type Employee struct { BossID uint64 EmployeeID uint64 Person }

上述代码中Person的Name，以及AutoIncr的ID和Created都是可访问的，无论是Go的.操作符还是通过StructScan。

在Go语言中，屏蔽子域是合法的，如果结构体Employee定义一个Name，那么就会屏蔽Person的Name，但是不确定的的**Selectors时非法的会引起一个runtime error。

基于sqlx构建结构体域名到域地址映射的方法，当把列Scan到结构体时，它并不知道一个name在遍历结构体树的时候是否遇到了两次。所以不像Go语言，StructScan会选择第一个遇到的匹配name的结构体域。由于Go结构体时从top往down排序，sqlx执行的是breadth-first traversal搜索算法，所以

type PersonPlace struct { Person Place }

StrucScan会把id赋值给Person.AUtoIncr.ID,但是访问时 访问的却是 Person.ID.所以未来避免混淆，建议在sql语句中使用AS来增加列别名。

原文链接：https://www.f2er.com/go/187832.html

猜你在找的Go相关文章

golang简单实现jwt验证(beego、xorm、jwt)
程序目录结构 简单实现，用户登录后返回一个jwt的token，下次请求带上token请求用户信息接...
【工程实践】利用go语言写一个简单的Pow样例
本篇博客的主要内容是用go写一个简单的Proof-of-Work共识机制，不涉及到网络通信环节，只是...
【工程实践】go语言实现MerkleTree
简介 默克尔树（MerkleTree）是一种典型的二叉树结构，其主要特点为： 最下面的叶节点包含...
第九章 goroutine
接下来学习并发编程, 并发编程是go语言最有特色的地方, go对并发编程是原生支持. goroutin...
第十一章 运用广度优先搜索走迷宫
先普及一下, 什么是广度优先搜索 广度优先搜索类似于树的层次遍历。从图中的某一顶点出发，...
第十三章 go实现分布式网络爬虫---单机版爬虫
爬虫的分类 网络爬虫分为两类 1. 通用爬虫: 类似于baidu, google. 他们会把大量的数据挖下...
第十章 channel select 总结
感觉channel在理解起来还有点费劲的, 尤其是select的使用, 既要可以读数据, 又要可以写数据...
第十五章 并发版爬虫第二版 -- 完结
并发版爬虫, 在上一篇单机版爬虫的基础上演变而来 这里只有并发引擎的代码, 基本的解析器代...
第八章--性能优化--pprof详细研究
接着上次的博客: https://www.cnblogs.com/ITPower/articles/12315637.html ppro...
第八章 测试与性能调优--生成文档和测试示例代码
java也可以生成文档, 就像一个手册一样, 可以用来查看方法, 接口, 下面我们给go项目生成文...
PHPJavaJava SEPythonC#C&C++RubyVBasp.NetGoPerlnettyDjangoDelphiJsp.NET CoreSpringFlaskSpringbootSpringMVCLuaLaravelMybatisAspGroovyThinkPHPYiiswoole
文件时间pythonm相等性PHP Warning时间问题问题解决pcntl_signal采样点wav模块动态文本调用频率限制对外暴露多个访问请求更新数据表模型结构type()方法比较速度手写体sobel算子保存模型Image类nn.Conv2dpytorch1.0kaggleDCGAN交并比range()用法打印模型反卷积卷积