Go操作数据库(二)

发表于

MySQL是常用的关系数据库，本文主要介绍Go语言怎么操作MySQL数据库。

关键词：golang

在项目中我们通常会使用database/sql连接MySQL数据库。本文结束sqlx实现批量插入数据的例子，介绍了sqlx中可能被忽略的sqlx.In和DB.NameExec方法。

sqlx介绍

sqlx被分为是database/sql的超集，在database/sql的基础上提供了一组扩展，而这组扩展处理大家常用来查询的Get(dest interface{}, ...) error和Select(dest interface{}, ...) error之外，还有很多强大的功能。

仓库地址：github.com/jmoiron/sqlx

安装

1
go get github.com/jmoiron/sqlx

基本使用

连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
var db *sqlx.DB
func initDB() (err error){
    dsn := "user:password@tcp(xxx.aws.us-west-1.rds.amazonaws.com:3306)/mt_subtitle_table?charset=utf8mb4&parseTime=True"
    //也可以使用MustConnect，连接不成功就panic
    db, err = sqlx.Connect("mysql", dsn)
    if err != nil {
        fmt.Printf("connect DB failed, err:%v", err)
        return
    }
    db.SetMaxOpenConns(20)              // 设置最大连接数
    db.SetMaxIdleConns(10)			   // 设置最大闲置连接数
}

查询

查询单行数据

1
2
3
4
5
6
7
8
9
10
11
// 查询单条数据
func queryRowDemo() {
    sqlStr := "select id, actress, title from mt_subtitle_table where id = ?"
    var m Moive
    err := db.Get(&m, sqlStr, "AAA-001")
    if err != nil {
        fmt.Printf("get failed, err :%v", err)
        return
    }
    fmt.Printf("m: %v", m)
}

查询多行数据

1
2
3
4
5
6
7
8
9
10
func queryMultRowDemo() {
    sqlStr := "select id, actress, title from mt_subtitle_table where actress= ?"
   	var movies []Movie
    err := db.Select(&moives, sqlStr, "川岛芳子")
    if err != nil {
        fmt.Printf("query failed, err :%v", err)
        return
    }
    fmt.Printf("movies: %#v", movies)
}

插入、更新和删除

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
//插入数据
func insertRowDemo() {
    sqlStr := "insert into mt_subtitle_table(id, actress, title) values (?,?)"
    ret, err := db.Exec(sqlStr, "AAA-002", "仲川そら","赤面羞恥")
    if err != nil {
        fmt.Printf("insert failed, err:%v", err)
        return
    }
    theID, err := ret.LastInsertId() // 新插入数据的id
    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v", err)
        return 
    }
    fmt.Printf("insert success, the id is %d", theID)
}
// 更新数据
func updateRowDemo() {
    sqlStr := "update mt_subtitle_table set actress=? where id = ?"
    ret, err := db.Exec(sqlStr, "仲川そら", "マジックミラ")
    if err != nil {
        fmt.Printf("update failed, err:%v", err)
        return
    }
    n, err := ret.RowAffected() // 影响操作的行数
    if err != nil {
        fmt.Printf("get RowAffected failed, err:%v", err)
        return
    }
    fmt.Printf("update success, affected rows:%d", n)
}
// 删除数据
func deleteRowDemo() {
    sqlStr := "delete from mt_subtitle_table where id = ?"
    ret, err := db.Exec(sqlStr, "AAA-002")
    if err != nil {
        fmt.Printf("delete failed, err:%v", err)
        return
    }
    n, err := ret.RowsAffected() // 影响操作的行数
    if err != nil {
        fmt.Printf("get RowAffected failed, err:%v", err)
        return
    }
    fmt.Printf("delete success, affected rows:%d", n)
}

NamedExec

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

map同名字段

1
2
3
4
5
6
7
8
9
10
func insertUserDemo() (err error)  {
    sqlStr := "INSERT INTO mt_subtitle_table(id, actress, title) values(:name, :age)"
    _, err = db.NamedExec(sqlStr,
                          map[string]interface{}{
                              "id": "ABC-001",
                              "actress": "川岛芳子",
                              "title": "上海回忆录"
                          })
    return
}
  • :字段名的用法要记住。

结构体同名字段

1
2
3
4
5
6
7
8
type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}

tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"})

NamedQuery

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
func nameQuery() {
    sqlStr := "SELECT * FROM mt_subtitle_table WHERE actress=:actress"
    // 使用map做命名查询
    rows, err := db.NamedQuery(sqlStr, map[string]interface{}{
        "actress": "川岛芳子"
    })
    if err != nil {
        fmt.Printf("dn.NamedQuery failed, err:%v", err)
        return
    }
    defer rows.Close()
    for rows.Next() {
        var m Movie
        err := rows.StructScan(&m)
        if err != nil {
            fmt.Printf("scan failed, err: %v", err)
            continue
        }
        fmt.Printf("movie: %v", m)
    }
    m1 := Movie{
        Id: "SNAA-001",
    }
    // 使用结构体命名查询，根据结构体的 db tag 进行映射
    rows, err := db.NameQuery(sqlStr, m1)
    if err != nil {
        fmt.Printf("db.NamedQuery failed, err: %v", err)
        return
    }
    defer rows.Close() 
    for rows.Next() {
        var m Moive
        err != rows.StructScan(&m)
        if err != nil {
            fmt.Printf("scan failed, err:%v", err)
            continue
        }
        fmt.Printf("movie: %#v", m)
    }
}

事务操作

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
func transationDemo() (err error) {
    tx. err := db.Begin() // 开启事务
    if err != nil {
        fmt.Prinf("事务开始失败，错误:%v", err)
        return err
    }
    // return之前执行
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)      // 回滚之后重新panic
        }else if err != nil {
            fmt.Println("rollback")
            tx.Rollback() // 有错误，因此需要回滚
        }else {
            err = tx.Commit() // 没有错误，提交事务
            fmt.Println("commit")
        }
    }()
    
    sqlStr1 := "UPDATE mt_subtitle_table SET title='艺妓回忆录' where id = ?"
    rs, err := tx.Exec(sqlStr1, "ABC-001")
    if err != nil {
        return err
    }
    if n != 1 {
        return errors.New("sqlStr1执行失败")
    }
    sqlStr2 := "UPDATE mt_subtitle_table SET title='上海回忆录' where id = ?"
    rs, err := tx.Exec(sqlStr2, "AAA-001")
    if err != nil {
        return err
    }
    n, err = rs.RowsAffected()
    if err != nil {
        return err
    }
    if n != 1 {
        return errors.New("sqlStr2执行失败")
    }
}

sqlx.In

sqlx.In 是sqlx提供的一个非常方便的函数。

批量插入

这里创建一个表，表结构如下：

1
2
3
4
5
6
CREATE TABLE `mt_subtitle_table`  (
  `id` text NOT NULL,
  `actress` text NOT NULL,
  `title` text NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

定义一个结构体，字段通过tag和上述表中的字段一致。

1
2
3
4
5
type Moive struct{
    ID string `db:"id"`
    Actress string `db:"actress"`
    Title string `db:"title"`
}

绑定变量：bindvars

查询占位符?又被称为bindvars，非常重要，应该始终使用它们向数据库发送值，因为他们可以防止SQL注入攻击。database/sql不尝试对查询文本进行任何验证；它与编码的参数一起按照原样发送到服务器，除非驱动程序实现一个特殊的接口，否则在执行之前，查询实在服务器上准备的。

bindvars是特定于数据库的：

  • MySQL中使用?
  • PostgreSQL使用枚举的$1、$2等语法
  • SQLite中使用?和$1的语法都支持
  • Oracle中使用:name的语法

bindvars的一个常见错误是，他们用来在sql语句中插入值。其实他们仅仅用于参数化，不允许更改SQL语句的结构。例如使用bindvars尝试参数化列或表名将不起作用。

1
2
3
4
// ? 不能用来插入表名（叫SQL语句中表名的占位符）
db.Query("SELECT * FROM ?", "mytable")
// ? 不能用来插入列名（叫做SQL语句中列名的占位符）
db.Query("SELECT ?, ? FROM people", "name", "location")

自己实现语句实现批量插入

比较笨，但是好理解。就是有多少个movie就拼接多少个(?, ?)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// BatchInsertMoive 自行构造批量插入的语句
func BatchInsertMovie(movies []*Movie) error {
    // 存放(?, ?)的slice
    valueStrings := make([]string, 0, len(movies))
    // 存放values的slice
    valueArgs := make([]interface{}, 0, len(movies) * 3)
    // 遍历users准备相关数据
    for _, m := range moives {
        // 此处占位符要与插入值的个数对应
        valuesStrings = append(valuesStrings, "(?, ?, ?)")
        valueArgs = append(valuesArgs, m.ID, m.Actress, m.Title)
    }
    // 自行拼接要执行的具体语句
    stmt := fmt.Sprintf("INSERT INTO mt_subtitle_table(id, actress, title) VALUES %s",
strings.Join(valueStrings, ","))
    _, err := db.Exec(stmt, valueArgs...)
    
}

使用sqlx.In实现批量插入

前提是需要我们的结构体实现driver.Valuer接口

1
2
3
func (m *Movie) Value() (driver.Value, error) {
    return []interface{}{m.ID, m.Actress, m.title}, nil
}

使用sqlx.In实现批量插入代码如下：

1
2
3
4
5
6
7
8
9
10
11
//BatchInsertMoive 使用的是sqlx.In帮我们拼接语句和参数，注意传入的参数是[]interface{}
func BatchInsertMoive(moive []interface{}) (error) {
    query, args, _ := sqlx.In(
        "INSERT INTO mt_subtitle_table(id, actress, title) VALUES (?), (?), (?)," // 这里是根据movie长度来，还是根据字段的个数来，有待观察
        movies..., // 如果参数实现了driver.Valuer, sqlx.In会通过调用Value()来展开它
    )
    fmt.Println(query) // 查看生成的querysting
    fmt.Println(args) // 查看生成的args
    _, err := db.Exec(query, args...)
    return err
}

使用NamedExec实现批量插入

使用NamedExec实现批量插入的方法如下

1
2
3
4
func BatchInsert(users []*User) error {
    _, err := db.NameExec("INSERT INTO mt_subtitle_table(id, actress, title) VALUES(:name, :age)", users)
    return err
}

sqlx.In的查询例子

关于sqlx.In这里在补充一个用法，在sqlx查询语句中实现了In查询和FIND_IN_SET函数。即实现SELECT * FROM mt_subtitle_table WHERE actress in ("川岛芳子"，"广田雅美")和SELECT * FROM mt_subtitle_table in (3, 2, 1) ORDER BY FIND_IN_SET(id, "川岛芳子"，"广田雅美").

in查询

1
2
3
4
5
6
7
8
9
10
11
12
// QueryByIDs 根据指定ID查询
func QueryByIDs(ids []string) (movies []Movie, err error) {
    // 动态填充id
    query, args, err := sqlx.In("SELECT id, actress, title FROM mt_subtitle_table WHERE actress IN (?)", ids)
    if err != nil {
        return 
    }
    // sqlx.In返回带`?`bindvar`的查询语句，我们使用Rebind()重新绑定他
    query = db.Rebind(query)
    err = db.Select(&movies, query, args...)
    return
}

in查询和FIND_IN_SET函数

查询id在给定id集合的数据并维持给定id集合的顺序。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// QuertAndOrderByIDs 按照指定id查询并维护顺序
func QuertAndOrderByIDs(ids []string)(moives []Movie, 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 id, actress FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?) 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(&movie, query, args...)
    return
}