golang連接sqlx庫的操作使用指南

第golang連接sqlx庫的操作使用指南目錄sqlx庫使用指南sqlx介紹安裝sqlx基本使用連接數(shù)據(jù)庫查詢插入、更新和刪除NamedExecNamedQuery事務操作sqlx.Insqlx.In的批量插入示例表結(jié)構(gòu)結(jié)構(gòu)體bindvars（綁定變量）自己拼接語句實現(xiàn)批量插入使用sqlx.In實現(xiàn)批量插入使用NamedExec實現(xiàn)批量插入sqlx.In的查詢示例in查詢in查詢和FIND_IN_SET函數(shù)

sqlx庫使用指南

在項目中我們通?？赡軙褂胐atabase/sql連接MySQL數(shù)據(jù)庫。本文借助使用sqlx實現(xiàn)批量插入數(shù)據(jù)的例子，介紹了sqlx中可能被你忽視了的sqlx.In和DB.NamedExec方法。

sqlx介紹

在項目中我們通常可能會使用database/sql連接MySQL數(shù)據(jù)庫。sqlx可以認為是Go語言內(nèi)置database/sql的超集，它在優(yōu)秀的內(nèi)置database/sql基礎(chǔ)上提供了一組擴展。這些擴展中除了大家常用來查詢的Get(destinterface{},...)error和Select(destinterface{},...)error外還有很多其他強大的功能。

安裝sqlx

goget/jmoiron/sqlx

基本使用

連接數(shù)據(jù)庫

vardb*sqlx.DB

funcinitDB()(errerror){

dsn:="user:password@tcp(:3306)/sql_testcharset=utf8mb4parseTime=True"

//也可以使用MustConnect連接不成功就panic

db,err=sqlx.Connect("mysql",dsn)

iferr!=nil{

fmt.Printf("connectDBfailed,err:%v\n",err)

return

db.SetMaxOpenConns(20)

db.SetMaxIdleConns(10)

return

查詢

查詢單行數(shù)據(jù)示例代碼如下：

//查詢單條數(shù)據(jù)示例

funcqueryRowDemo(){

sqlStr:="selectid,name,agefromuserwhereid="

varuuser

err:=db.Get(u,sqlStr,1)

iferr!=nil{

fmt.Printf("getfailed,err:%v\n",err)

return

fmt.Printf("id:%dname:%sage:%d\n",u.ID,u.Name,u.Age)

查詢多行數(shù)據(jù)示例代碼如下：

//查詢多條數(shù)據(jù)示例

funcqueryMultiRowDemo(){

sqlStr:="selectid,name,agefromuserwhereid"

varusers[]user

err:=db.Select(amp;users,sqlStr,0)

iferr!=nil{

fmt.Printf("queryfailed,err:%v\n",err)

return

fmt.Printf("users:%#v\n",users)

插入、更新和刪除

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

//插入數(shù)據(jù)

funcinsertRowDemo(){

sqlStr:="insertintouser(name,age)values(,)"

ret,err:=db.Exec(sqlStr,"沙河小王子",19)

iferr!=nil{

fmt.Printf("insertfailed,err:%v\n",err)

return

theID,err:=ret.LastInsertId()//新插入數(shù)據(jù)的id

iferr!=nil{

fmt.Printf("getlastinsertIDfailed,err:%v\n",err)

return

fmt.Printf("insertsuccess,theidis%d.\n",theID)

//更新數(shù)據(jù)

funcupdateRowDemo(){

sqlStr:="updateusersetage=whereid="

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

iferr!=nil{

fmt.Printf("updatefailed,err:%v\n",err)

return

n,err:=ret.RowsAffected()//操作影響的行數(shù)

iferr!=nil{

fmt.Printf("getRowsAffectedfailed,err:%v\n",err)

return

fmt.Printf("updatesuccess,affectedrows:%d\n",n)

//刪除數(shù)據(jù)

funcdeleteRowDemo(){

sqlStr:="deletefromuserwhereid="

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

iferr!=nil{

fmt.Printf("deletefailed,err:%v\n",err)

return

n,err:=ret.RowsAffected()//操作影響的行數(shù)

iferr!=nil{

fmt.Printf("getRowsAffectedfailed,err:%v\n",err)

return

fmt.Printf("deletesuccess,affectedrows:%d\n",n)

NamedExec

DB.NamedExec方法用來綁定SQL語句與結(jié)構(gòu)體或map中的同名字段。

funcinsertUserDemo()(errerror){

sqlStr:="INSERTINTOuser(name,age)VALUES(:name,:age)"

_,err=db.NamedExec(sqlStr,

map[string]interface{}{

"name":"七米",

"age":28,

return

NamedQuery

與DB.NamedExec同理，這里是支持查詢。

funcnamedQuery(){

sqlStr:="SELECT*FROMuserWHEREname=:name"

//使用map做命名查詢

rows,err:=db.NamedQuery(sqlStr,map[string]interface{}{"name":"七米"})

iferr!=nil{

fmt.Printf("db.NamedQueryfailed,err:%v\n",err)

return

deferrows.Close()

forrows.Next(){

varuuser

err:=rows.StructScan(u)

iferr!=nil{

fmt.Printf("scanfailed,err:%v\n",err)

continue

fmt.Printf("user:%#v\n",u)

u:=user{

Name:"七米",

//使用結(jié)構(gòu)體命名查詢，根據(jù)結(jié)構(gòu)體字段的dbtag進行映射

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

iferr!=nil{

fmt.Printf("db.NamedQueryfailed,err:%v\n",err)

return

deferrows.Close()

forrows.Next(){

varuuser

err:=rows.StructScan(u)

iferr!=nil{

fmt.Printf("scanfailed,err:%v\n",err)

continue

fmt.Printf("user:%#v\n",u)

事務操作

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

functransactionDemo2()(errerror){

tx,err:=db.Beginx()//開啟事務

iferr!=nil{

fmt.Printf("begintransfailed,err:%v\n",err)

returnerr

deferfunc(){

ifp:=recover();p!=nil{

tx.Rollback()

panic(p)//re-throwpanicafterRollback

}elseiferr!=nil{

fmt.Println("rollback")

tx.Rollback()//errisnon-nil;don'tchangeit

}else{

err=tx.Commit()//errisnil;ifCommitreturnserrorupdateerr

fmt.Println("commit")

sqlStr1:="Updateusersetage=20whereid="

rs,err:=tx.Exec(sqlStr1,1)

iferr!=nil{

returnerr

n,err:=rs.RowsAffected()

iferr!=nil{

returnerr

ifn!=1{

returnerrors.New("execsqlStr1failed")

sqlStr2:="Updateusersetage=50wherei="

rs,err=tx.Exec(sqlStr2,5)

iferr!=nil{

returnerr

n,err=rs.RowsAffected()

iferr!=nil{

returnerr

ifn!=1{

returnerrors.New("execsqlStr1failed")

returnerr

sqlx.In

sqlx.In是sqlx提供的一個非常方便的函數(shù)。

sqlx.In的批量插入示例

表結(jié)構(gòu)

為了方便演示插入數(shù)據(jù)操作，這里創(chuàng)建一個user表，表結(jié)構(gòu)如下：

CREATETABLE`user`(

`id`BIGINT(20)NOTNULLAUTO_INCREMENT,

`name`VARCHAR(20)DEFAULT'',

`age`INT(11)DEFAULT'0',

PRIMARYKEY(`id`)

)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8mb4;

結(jié)構(gòu)體

定義一個user結(jié)構(gòu)體，字段通過tag與數(shù)據(jù)庫中user表的列一致。

typeUserstruct{

Namestring`db:"name"`

Ageint`db:"age"`

bindvars（綁定變量）

查詢占位符在內(nèi)部稱為bindvars（查詢占位符）,它非常重要。你應該始終使用它們向數(shù)據(jù)庫發(fā)送值，因為它們可以防止SQL注入攻擊。database/sql不嘗試對查詢文本進行任何驗證；它與編碼的參數(shù)一起按原樣發(fā)送到服務器。除非驅(qū)動程序?qū)崿F(xiàn)一個特殊的接口，否則在執(zhí)行之前，查詢是在服務器上準備的。因此bindvars是特定于數(shù)據(jù)庫的:

MySQL中使用PostgreSQL使用枚舉的$1、$2等bindvar語法SQLite中和$1的語法都支持Oracle中使用:name的語法

bindvars的一個常見誤解是，它們用來在sql語句中插入值。它們其實僅用于參數(shù)化，不允許更改SQL語句的結(jié)構(gòu)。例如，使用bindvars嘗試參數(shù)化列或表名將不起作用：

//？不能用來插入表名（做SQL語句中表名的占位符）

db.Query("SELECT*FROM","mytable")

//？也不能用來插入列名（做SQL語句中列名的占位符）

db.Query("SELECT,FROMpeople","name","location")

自己拼接語句實現(xiàn)批量插入

比較笨，但是很好理解。就是有多少個User就拼接多少個(,)。

//BatchInsertUsers自行構(gòu)造批量插入的語句

funcBatchInsertUsers(users[]*User)error{

//存放(,)的slice

valueStrings:=make([]string,0,len(users))

//存放values的slice

valueArgs:=make([]interface{},0,len(users)*2)

//遍歷users準備相關(guān)數(shù)據(jù)

for_,u:=rangeusers{

//此處占位符要與插入值的個數(shù)對應

valueStrings=append(valueStrings,"(,)")

valueArgs=append(valueArgs,u.Name)

valueArgs=append(valueArgs,u.Age)

//自行拼接要執(zhí)行的具體語句

stmt:=fmt.Sprintf("INSERTINTOuser(name,age)VALUES%s",

strings.Join(valueStrings,","))

_,err:=DB.Exec(stmt,valueArgs...)

returnerr

使用sqlx.In實現(xiàn)批量插入

前提是需要我們的結(jié)構(gòu)體實現(xiàn)driver.Valuer接口：

func(uUser)Value()(driver.Value,error){

return[]interface{}{u.Name,u.Age},nil

使用sqlx.In實現(xiàn)批量插入代碼如下：

//BatchInsertUsers2使用sqlx.In幫我們拼接語句和參數(shù),注意傳入的參數(shù)是[]interface{}

funcBatchInsertUsers2(users[]interface{})error{

query,args,_:=sqlx.In(

"INSERTINTOuser(name,age)VALUES(),(),()",

users...,//如果arg實現(xiàn)了driver.Valuer,sqlx.In會通過調(diào)用Value()來展開它

fmt.Println(query)//查看生成的querystring

fmt.Println(args)//查看生成的args

_,err:=DB.Exec(query,args...)

returnerr

使用NamedExec實現(xiàn)批量插入

注意：該功能需1.3.1版本以上，并且1.3.1版本目前還有點問題，sql語句最后不能有空格和;，詳見issues/690。

使用NamedExec實現(xiàn)批量插入的代碼如下：

//BatchInsertUsers3使用NamedExec實現(xiàn)批量插入

funcBatchInsertUsers3(users[]*User)error{

_,err:=DB.NamedExec("INSERTINTOuser(name,age)VALUES(:name,:age)",users)

returnerr

把上面三種方法綜合起來試一下：

funcmain(){

err:=initDB()

iferr!=nil{

panic(err)

deferDB.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)

iferr!=nil{

fmt.Printf("BatchInsertUsersfailed,err:%v\n",err)

//方法2

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

err=BatchInsertUsers2(users2)

iferr!=nil{

fmt.Printf("BatchInsertUsers2failed,err:%v\n",err)

//方法3

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

err=BatchInsertUsers3(users3)

iferr!=nil{

fmt.Printf("BatchInsertUsers3failed,err:%v\n",err)

sqlx.In的查詢示例

關(guān)于sqlx.In這里再補充一個用法，在sqlx查詢語句中實現(xiàn)In查詢和FIND_IN_SET函數(shù)。

即實現(xiàn)SELECT*FROMuserWHEREidin(3,2,1);

和SELECT*FROMuserWHEREidin(3,2,1)ORDERBYFIND_IN_SET(id,3,2,1。

in查詢

查詢id在給定id集合中的數(shù)據(jù)。

//QueryByIDs根據(jù)給定ID查詢

funcQueryByIDs(ids[]int)(users[]User,errerror){

//動態(tài)填充id

query,args,err:=sqlx.In("SELECTname,ageFROMuser