在项目中我们通常可能会使用database/sql连接MySQL数据库。
本文使用sqlx实现批量插入数据的例子，介绍了sqlx中可能被你忽视了的sqlx.In和DB.NamedExec方法。
在项目中我们通常可能会使用database/sql连接MySQL数据库。sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。这些扩展中除了大家常用来查询的Get(dest interface{}, …) error和Select(dest interface{}, …) error外还有很多其他强大的功能。


1.安装sqlx

  1. go get github.com/jmoiron/sqlx
  1. import (
  2.     "fmt"
  3.     "github.com/jmoiron/sqlx"
  4.     _ "github.com/go-sql-driver/mysql"
  5. )
  6. var db *sqlx.DB
  7. func initDB() (err error) {
  8.     dsn := "root:123456@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
  9.     db, err = sqlx.Connect("mysql", dsn)
  10.     if err != nil {
  11.         fmt.Printf("connect db failed,err:%v\n",err)
  12.         return
  13.     }
  14.     db.SetMaxOpenConns(20)
  15.     db.SetMaxIdleConns(10)
  16.     return
  17. }

2.查询

当db.Get(&u,sql,id)执行时，sqlx包中会访问结构体u中的各字段，这时发现字段全部为小写，不可访问。

单行查询

  1. type user struct {
  2.     ID int
  3.     Name string
  4.     Age int
  5. }f
  6. unc queryRowDemo() {
  7.     sqlStr := "select id, name, age from user where id=?"
  8.     var u user
  9.     err := db.Get(&u,sqlStr,1)
  10.     if err != nil {
  11.         fmt.Printf("get failed, err:%v\n", err)
  12.         return
  13.     }
  14.     fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
  15. }

多行查询

  1. func queryMultiRowDemo() {
  2.     sqlStr := "select id, name, age from user where id > ?"
  3.     var users []user
  4.     err := db.Select(&users,sqlStr,0)
  5.     if err != nil {
  6.         fmt.Printf("query failed, err:%v\n", err)
  7.         return
  8.     }
  9.     fmt.Printf("id:%#v\n", users)
  10. }

3.插入、更新和删除

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

  1. type user struct {
  2.     ID int
  3.     Name string
  4.     Age int
  5. }
  6. var db *sqlx.DB
  7. func insertRowDemo() {
  8.     sqlStr := "insert into user(name,age)values (?,?)"
  9.     result, err := db.Exec(sqlStr, "cheng", "45")
  10.     if err != nil {
  11.         fmt.Printf("insert failed,err:%v\n",err)
  12.         return
  13.     }
  14.     id, err := result.LastInsertId()
  15.     if err != nil {
  16.         fmt.Printf("get lastinsert ID failed, err:%v\n", err)
  17.         return
  18.     }
  19.     fmt.Printf("insert success,id:%d\n",id)
  21. }
  1. var db *sqlx.DB
  2. func updateRowDemo() {
  3.     sqlStr := "update user set age = ? where id = ?"
  4.     result, err := db.Exec(sqlStr, 45, 1)
  5.     if err != nil {
  6.         fmt.Printf("update failed, err:%v\n", err)
  7.         return
  8.     }
  9.     n, err := result.RowsAffected()
  10.     if err != nil {
  11.         fmt.Printf("get RowsAffected failed, err:%v\n", err)
  12.         return
  13.     }
  14.     fmt.Printf("update success, affected rows:%d\n", n)
  15. }
  1. func deleteRowDemo() {
  2.     sqlStr := "delete from user where id = ?"
  3.     result, err := db.Exec(sqlStr, 1)
  4.     if err != nil {
  5.         fmt.Printf("delete failed, err:%v\n", err)
  6.         return
  7.     }
  8.     n, err := result.RowsAffected()
  9.     if err != nil {
  10.         fmt.Printf("get RowsAffected failed, err:%v\n", err)
  11.         return
  12.     }
  13.     fmt.Printf("delete success, affected rows:%d\n", n)
  14. }

4.NamedQuery

DB.NamedExec方法用来绑定SQL语句与结构体或map中的同名字段

  1. func insertUserDemo()(err error)  {
  2.     sqlStr :="insert into user (name,age) values(:name,:age)"
  3.     _, err = db.NamedExec(sqlStr,
  4.         map[string]interface{}{
  5.             "name": "cheng",
  6.             "age":  50,
  7.         })
  8.     return err
  9. }

5.NamedQuery

与DB.NamedExec同理，这里是支持查询

  1. func namedQuery() {
  2.     sqlStr := "select * from user where name=:name"
  3.     rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "cheng"})
  4.     if err != nil {
  5.         fmt.Printf("db.NamedQuery failed, err:%v\n", err)
  6.         return
  7.     }
  8.     defer rows.Close()
  9.     for rows.Next(){
  10.         var u user
  11.         err := rows.StructScan(&u)
  12.         if err != nil {
  13.             fmt.Printf("scan failed, err:%v\n", err)
  14.             continue
  15.         }
  16.         fmt.Printf("user:%#v\n", u)
  17.     }
  19. }
  1. func nameQuery() {
  2.     sqlStr := "select * from user where name=:name"
  3.     u := user{
  4.         Name: "cheng",
  5.     }
  6.     rows, err := db.NamedQuery(sqlStr, &u)
  7.     if err != nil {
  8.         fmt.Printf("db.NamedQuery failed, err:%v\n", err)
  9.         return
  10.     }
  11.     defer rows.Close()
  12.     for rows.Next(){
  13.         var u user
  14.         err := rows.StructScan(&u)
  15.         if err != nil {
  16.             fmt.Printf("scan failed, err:%v\n", err)
  17.             continue
  18.         }
  19.         fmt.Printf("user:%#v\n",u)
  20.     }
  21. }

6.事务操作

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

func transcation() (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()
            fmt.Println("rollback1")
            panic(p)
        }else if err != nil{
            fmt.Println("rollback2")
            tx.Rollback()  //事务回滚
        }else {
            tx.Commit()
            fmt.Println("commit")  //事务提交
        }
    }()
    sqlStr1 := "update user set age = 20 where id = ?"
    result, err := tx.Exec(sqlStr1, 2)
    if err != nil {
        return err
    }
    n, err := result.RowsAffected()
    if err != nil {
        return err
    }
    if n != 1 {
        return errors.New("exec failed")
    }
    sqlStr2 := "Update user set age=50 where id=?"
    result, err = tx.Exec(sqlStr2, 5)
    if err!=nil{
        return err
    }
    n, err = result.RowsAffected()
    if err != nil {
        return err
    }
    if n != 1 {
        return errors.New("exec  failed")
    }
    return err

}