sqlx的使用及注意点

前言

sqlx是基于Go内置database/sql包上的扩展，主要是简化了sql的使用过程。sqlx的sql.DB, sql.TX, sql.Stmt等保持底层实现不变，因此可以很方便地从database/sql切换到sqlx。sqlx另外还提供了一些功能：

• 可以将Rows内容解析至struct（支持内嵌）、map、slice
• 命名参数支持
• Get/Select可以快速将查询结果转为为struct/slice

使用

1. Open/Connect

Open可能仅校验参数，而没有与db间创建连接，要确认db是否可用，需要调用Ping。Connect则相当于Open+Ping。

使用如下：

db, err := sqlx.Open("postgres", "user=foo dbname=bar sslmode=disable")
    if err != nil {
        log.Fatalln(err)
    }
db, err := sqlx.Connect("mysql", "user:password@host:port?database")
    if err != nil {
        log.Fatalln(err)
    }

2. Queryx/QueryxContext/QueryRowx/QueryRowxContext

用于查询多条/单条数据

与database/sql的Query/QueryxContext/QueryRow/QueryRowContext区别在于返回的是*sqlx.Row

rows, err := db.Queryx("SELECT * FROM place")
    for rows.Next() {
        err := rows.StructScan(&place)
        if err != nil {
            log.Fatalln(err)
        } 
        fmt.Printf("%#v\n", place)
    }

3. Exec

Exec执行sql语句而不返回rows，主要用于insert、update、delete

db.Exec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net")

4. 开启事务

对应database/sql有Beginx、Preparex、Stmtx，带有Must前缀的方法，若发生错误则会panic

tx := db.MustBegin()
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net")
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@gmail.net")
    tx.MustExec("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New York", "1")
    tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Hong Kong", "852")
    tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Singapore", "65")
    // Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &person
    tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"})
    tx.Commit()

5. Get/Select

sqlx提供了快速将查询结果解析到struct、slice的方法，就是Get、Select。
Get用于查询单条数据，Select则用于查询多条数据，需要注意的是方法中的dest必须满足要求，Get中不能为nil，Select中必须为slice。

tag注意：

使用struct需要注意，sqlx默认解析的tag为"db"，未设置tag，默认情况下是直接将field名转换为小写，因此默认情况下不满足需求时，需要注意设置field的tag名，否则可能因为不匹配而导致数据处理失败。

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}
people := []Person{}
db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")
jason, john := people[0], people[1]
jason = Person{}
err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")
fmt.Printf("%#v\n", jason)

如果存在null的情况，则field的类型必须设置为对应的sql.Null*类型（sql.NullBool、sql.NullInt64、sql.NullString等）

type Place struct {
    Country string
    City    sql.NullString
    TelCode int
}
// if you have null fields and use SELECT *, you must use sql.Null* in your struct
places := []Place{}
err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")
if err != nil {
    fmt.Println(err)
    return
}
usa, singsing, honkers := places[0], places[1], places[2]

fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers)

6. 命名参数支持

支持对sql中的命名参数解析支持，命名参数的格式为:name，执行时会主动获取对应name的值。

// Named queries, using `:name` as the bindvar.  Automatic bindvar support
    // which takes into account the dbtype based on the driverName on sqlx.Open/Connect
    _, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, 
        map[string]interface{}{
            "first": "Bin",
            "last": "Smuth",
            "email": "bensmith@allblacks.nz",
    })

    // Selects Mr. Smith from the database
    rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:fn`, map[string]interface{}{"fn": "Bin"})

    // Named queries can also use structs.  Their bind names follow the same rules
    // as the name -> db mapping, so struct fields are lowercased and the `db` tag
    // is taken into consideration.
    rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:first_name`, jason)

总结

本文简单介绍了sqlx的使用及使用过程中的注意点，希望对大家有所帮助。

公众号

鄙人刚刚开通了公众号，专注于分享Go开发相关内容，望大家感兴趣的支持一下，在此特别感谢。