Go sqlx库

以仁待人 發表於 2020-7-8 14:06:00

在项目中我们通常可能会使用<code>database/sql</code>连接MySQL数据库。本文借助使用<code>sqlx</code>实现批量插入数据的例子，介绍了<code>sqlx</code>中可能被你忽视了的<code>sqlx.In</code>和<code>DB.NamedExec</code>方法。
<h2 id="autoid-0-0-0">sqlx介绍</h2>
在项目中我们通常可能会使用<code>database/sql</code>连接MySQL数据库。<code>sqlx</code>可以认为是Go语言内置<code>database/sql</code>的超集，它在优秀的内置<code>database/sql</code>基础上提供了一组扩展。这些扩展中除了大家常用来查询的<code>Get(dest interface{}, ...) error</code>和<code>Select(dest interface{}, ...) error</code>外还有很多其他强大的功能。
<h2 id="autoid-0-1-0">安装sqlx</h2>
<pre class="line-numbers language-go"><code class=" language-go">go get github.com/jmoiron/sqlx
</code></pre>
<h2 id="autoid-0-2-0">基本使用</h2>
<h3 id="autoid-0-3-0">连接数据库</h3>
<pre class="line-numbers language-go"><code class=" language-go">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
}
</code></pre>
<h3 id="autoid-0-3-1">查询</h3>
查询单行数据示例代码如下：
<pre class="line-numbers language-go"><code class=" language-go">// 查询单条数据示例
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)
}
</code></pre>
查询多行数据示例代码如下：
<pre class="line-numbers language-go"><code class=" language-go">// 查询多条数据示例
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)
}
</code></pre>
<h3 id="autoid-0-3-2">插入、更新和删除</h3>
sqlx中的exec方法与原生sql中的exec使用基本一致：
<pre class="line-numbers language-go"><code class=" language-go">// 插入数据
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)
}
</code></pre>
<h3 id="autoid-0-3-3">NamedExec</h3>
<code>DB.NamedExec</code>方法用来绑定SQL语句与结构体或map中的同名字段。
<pre class="line-numbers language-go"><code class=" language-go">func insertUserDemo()(err error){
sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
_, err = db.NamedExec(sqlStr,
map[string]interface{}{
"name": "七米",
"age": 28,
})
return
}
</code></pre>
<h3 id="autoid-0-3-4">NamedQuery</h3>
与<code>DB.NamedExec</code>同理，这里是支持查询。
<pre class="line-numbers language-go"><code class=" language-go">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)
}
}
</code></pre>
<h3 id="autoid-0-3-5">事务操作</h3>
对于事务操作，我们可以使用<code>sqlx</code>中提供的<code>db.Beginx()</code>和<code>tx.Exec()</code>方法。示例代码如下：
<pre class="line-numbers language-go"><code class=" language-go">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
}
</code></pre>
<h2 id="autoid-0-3-6">sqlx.In</h2>
<code>sqlx.In</code>是<code>sqlx</code>提供的一个非常方便的函数。
<h3 id="autoid-0-4-0">sqlx.In的批量插入示例</h3>
<h4 id="表结构">表结构</h4>
为了方便演示插入数据操作，这里创建一个<code>user</code>表，表结构如下：
<pre class="line-numbers language-sql"><code class=" language-sql">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;
</code></pre>
<h4 id="结构体">结构体</h4>
定义一个<code>user</code>结构体，字段通过tag与数据库中user表的列一致。
<pre class="line-numbers language-go"><code class=" language-go">type User struct {
Name string `db:"name"`
Ageint `db:"age"`
}
</code></pre>
<h4 id="bindvars-绑定变量">bindvars（绑定变量）</h4>
查询占位符<code>?</code>在内部称为bindvars（查询占位符）,它非常重要。你应该始终使用它们向数据库发送值，因为它们可以防止SQL注入攻击。<code>database/sql</code>不尝试对查询文本进行任何验证；它与编码的参数一起按原样发送到服务器。除非驱动程序实现一个特殊的接口，否则在执行之前，查询是在服务器上准备的。因此<code>bindvars</code>是特定于数据库的:
<ul>
<li>MySQL中使用<code>?</code></li>
<li>PostgreSQL使用枚举的<code>$1</code>、<code>$2</code>等bindvar语法</li>
<li>SQLite中<code>?</code>和<code>$1</code>的语法都支持</li>
<li>Oracle中使用<code>:name</code>的语法</li>
</ul>
<code>bindvars</code>的一个常见误解是，它们用来在sql语句中插入值。它们其实仅用于参数化，不允许更改SQL语句的结构。例如，使用<code>bindvars</code>尝试参数化列或表名将不起作用：
<pre class="line-numbers language-go"><code class=" language-go">// ？不能用来插入表名（做SQL语句中表名的占位符）
db.Query("SELECT * FROM ?", "mytable")

// ？也不能用来插入列名（做SQL语句中列名的占位符）
db.Query("SELECT ?, ? FROM people", "name", "location")
</code></pre>
<h4 id="自己拼接语句实现批量插入">自己拼接语句实现批量插入</h4>
比较笨，但是很好理解。就是有多少个User就拼接多少个<code>(?, ?)</code>。
<pre class="line-numbers language-go"><code class=" language-go">// 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
}
</code></pre>
<h4 id="使用sqlx-in实现批量插入">使用sqlx.In实现批量插入</h4>
前提是需要我们的结构体实现<code>driver.Valuer</code>接口：
<pre class="line-numbers language-go"><code class=" language-go">func (u User) Value() (driver.Value, error) {
return []interface{}{u.Name, u.Age}, nil
}
</code></pre>
使用<code>sqlx.In</code>实现批量插入代码如下：
<pre class="line-numbers language-go"><code class=" language-go">// 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
}
</code></pre>
<h4 id="使用namedexec实现批量插入">使用NamedExec实现批量插入</h4>
注意 ：该功能目前有人已经推了#285 PR，但是作者还没有发<code>release</code>，所以想要使用下面的方法实现批量插入需要暂时使用<code>master</code>分支的代码：
在项目目录下执行以下命令下载并使用<code>master</code>分支代码：
<pre class="line-numbers language-bash"><code class=" language-bash">go get github.com/jmoiron/sqlx@master
</code></pre>
使用<code>NamedExec</code>实现批量插入的代码如下：
<pre class="line-numbers language-go"><code class=" language-go">// BatchInsertUsers3 使用NamedExec实现批量插入
func BatchInsertUsers3(users []*User) error {
_, err := DB.NamedExec("INSERT INTO user (name, age) VALUES (:name, :age)", users)
return err
}
</code></pre>
把上面三种方法综合起来试一下：
<pre class="line-numbers language-go"><code class=" language-go">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)
}
}
</code></pre>
<h3 id="autoid-0-4-1">sqlx.In的查询示例</h3>
关于<code>sqlx.In</code>这里再补充一个用法，在<code>sqlx</code>查询语句中实现In查询和FIND_IN_SET函数。即实现<code>SELECT * FROM user WHERE id in (3, 2, 1);</code>和<code>SELECT * FROM user WHERE id in (3, 2, 1) ORDER BY FIND_IN_SET(id, '3,2,1');</code>。
<h4 id="in查询">in查询</h4>
查询id在给定id集合中的数据。
<pre class="line-numbers language-go"><code class=" language-go">// 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
}
</code></pre>
<h4 id="in查询和find-in-set函数">in查询和FIND_IN_SET函数</h4>
查询id在给定id集合的数据并维持给定id集合的顺序。
<pre class="line-numbers language-go"><code class=" language-go">// 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
}
</code></pre>
当然，在这个例子里面你也可以先使用<code>IN</code>查询，然后通过代码按给定的ids对查询结果进行排序。
 
来自  https://www.liwenzhou.com/posts/Go/sqlx/ 
来源：https://www.cnblogs.com/staff/p/13266574.html

頁: [1]

琼殿技术论坛's Archiver

Go sqlx库