Golang Mysql笔记（二）--- CURD基础

数据库查询

我们了解了数据库连接与连接池。拿到了连接当然就是为了跟数据库交互。对于数据库交互，无怪乎两类操作，读和写。其中怎么读，怎么写，读和写的过程糅合一起就会遇到复杂的事务。本篇内容主要关注数据库的读写操作，后面再涉及事务的介绍。

读取数据

database/sql提供了Query和QueryRow方法进行查询数据库。对于Query方法的原理，正如前文介绍的主要分为三步：

1. 从连接池中请求一个连接
2. 执行查询的sql语句
3. 将数据库连接的所属权传递给Result结果集

Query返回的结果集是sql.Rows类型。它有一个Next方法，可以迭代数据库的游标，进而获取每一行的数据，大概使用范式如下：

    rows, err := db.Query("SELECT world FROM test.hello")
    if err != nil{
        log.Fatalln(err)
    }

    for rows.Next(){
        var s string
        err = rows.Scan(&s)
        if err !=nil{
            log.Fatalln(err)
        }
        log.Printf("found row containing %q", s)
    }
    rows.Close()

上述代码我们已经见过好多次了，想必大家都轻车熟路啦。rows.Next方法设计用来迭代。当它迭代到最后一行数据之后，会触发一个io.EOF的信号，即引发一个错误，同时go会自动调用rows.Close方法释放连接，然后返回false。此时循环将会结束退出。

通常你会正常迭代完数据然后退出循环。可是如果并没有正常的循环而因其他错误导致退出了循环。此时rows.Next处理结果集的过程并没有完成，归属于rows的连接不会被释放回到连接池。因此十分有必要正确的处理rows.Close事件。如果没有关闭rows连接，将导致大量的连接并且不会被其他函数重用，就像溢出了一样。最终将导致数据库无法使用。

那么如何阻止这样的行为呢？上述代码已经展示，无论循环是否完成或者因为其他原因退出，都显示的调用rows.Close方法，确保连接释放。又或者使用defer指令在函数退出的时候释放连接，即使连接已经释放了，rows.Close仍然可以调用多次，是无害的。

使用defer的时候需要注意，如果一个函数执行很长的逻辑，例如main函数，那么rows的连接释放就会也很长，好的实践方案是尽可能的越早释放连接。

rows.Next循环迭代的时候，因为触发了io.EOF而退出循环。为了检查是否是迭代正常退出还是异常退出，需要检查rows.Err。例如上面的代码应该改成：

    rows, err := db.Query("SELECT world FROM test.hello")
    if err != nil{
        log.Fatalln(err)
    }
    defer rows.Close()

    for rows.Next(){
        var s string
        err = rows.Scan(&s)
        if err !=nil{
            log.Fatalln(err)
        }
        log.Printf("found row containing %q", s)
    }
    rows.Close()
    if err = rows.Err(); err != nil {
        log.Fatal(err)
    }

读取单条记录

Query方法是读取多行结果集，实际开发中，很多查询只需要单条记录，不需要再通过Next迭代。golang提供了QueryRow方法用于查询单条记录的结果集。

    var s string
    err = db.QueryRow("SELECT world FROM test.hello LIMIT 1").Scan(&s)
    if err != nil{
        if err == sql.ErrNoRows{
            log.Println("There is not row")
        }else {
            log.Fatalln(err)
        }
    }
    log.Println("found a row", s)

QueryRow方法的使用很简单，它要么返回sql.Row类型，要么返回一个error，如果是发送了错误，则会延迟到Scan调用结束后返回，如果没有错误，则Scan正常执行。只有当查询的结果为空的时候，会触发一个sql.ErrNoRows错误。你可以选择先检查错误再调用Scan方法，或者先调用Scan再检查错误。

rows.Scan原理

结果集方法Scan可以把数据库取出的字段值赋值给指定的数据结构。它的参数是一个空接口的切片，这就意味着可以传入任何值。通常把需要赋值的目标变量的指针当成参数传入，它能将数据库取出的值赋值到指针值对象上。

var var1, var2 string
err = row.Scan(&var1, &var2)

在一些特殊案例中，如果你不想把值赋值给指定的目标变量，那么需要使用*sql.RawBytes类型。如何使用*sql.RawBytes需要参考更细的官方文档。大多数情况下我们不必这么做。但是还是需要注意在db.QueryRow().Scan()中不能使用 *sql.RawBytes。

Scan还会帮我们自动推断除数据字段匹配目标变量。比如有个数据库字段的类型是VARCHAR，而他的值是一个数字串，例如"1"。如果我们定义目标变量是string，则scan赋值后目标变量是数字string。如果声明的目标变量是一个数字类型，那么scan会自动调用strconv.ParseInt()或者strconv.ParseInt()方法将字段转换成和声明的目标变量一致的类型。当然如果有些字段无法转换成功，则会返回错误。因此在调用scan后都需要检查错误。

var world int
err = stmt.QueryRow(1).Scan(&world)

此时scan会把字段转变成数字整型的赋值给world变量

var world string
err = stmt.QueryRow(1).Scan(&world)

此时scan取出的字段就是字串。同样的如果字段是int类型，声明的变量是string类型，scan也会自动将数字转换成字串赋值给目标变量。

空值处理

数据库有一个特殊的类型，NULL空值。可是NULL不能通过scan直接跟普遍变量赋值，甚至也不能将null赋值给nil。对于null必须指定特殊的类型，这些类型定义在database/sql库中。例如sql.NullFloat64。如果在标准库中找不到匹配的类型，可以尝试在驱动中寻找。下面是一个简单的例子：

var (
   s1 string
    s2 sql.NullString i1 int
    f1 float64
    f2 float64
)
// 假设数据库的记录为 ["hello", NULL, 12345, "12345.6789", "not-a-float"]
err = rows.Scan(&s1, &s2, &i1, &f1, &f2) if err != nil {
log.Fatal(err) }

因为最后一个f2字段的值不是float，这会英法一个错误。

sql: Scan error on column index 4: converting string "not-a- oat" to a  oat64: strconv.ParseFloat: parsing "not-a- oat": invalid syntax

如果忽略err，强行读取目标变量，可以看到最后一个值转换错误会处理，而不是抛出异常：

err = rows.Scan(&s1, &s2, &i1, &f1, &f2)
log.Printf("%q %#v %d %f %f", s1, s2, i1, f1, f2)

// 输出
 "hello" sql.NullString{String:"", Valid:false} 12345 12345.678900
0.000000

可以看到，除了最后一个转换失败变成了零值之外，其他都正常的取出了值，尤其是null匹配了NullString类型的目标变量。

对于null的操作，通常仍然需要验证：

var world sql.NullString
err := db.QueryRow("SELECT world FROM hello WHERE id = ?", id).Scan(&world)
...
if world.Valid {
      wrold.String 
} else {
    // 数据库的value是不是null的时候，输出 world的字符串值， 空字符串   
    world.String
}

对应的，如果world字段是一个int，那么声明的目标变量类似是sql.NullInt64，读取其值的方法为world.Int64。

但是有时候我们并不关心值是不是Null,我们只需要吧他当一个空字符串来对待就行。这时候我们可以使用[]byte（null byte[]可以转化为空string）

var world []byte
err := db.QueryRow("SELECT world FROM hello WHERE id = ?", id).Scan(&world)
...
log.Println(string(real_name)) // 有值则取出字串值，null则转换成 空字串。

自动匹配字段

在执行查询的时候，我们定义了目标变量，同时查询的时候也写明了字段，如果不指名字段，或者字段的顺序和查询的不一样，都有可能出错。因此如果能够自动匹配查询的字段值，将会十分节省代码，同时也易于维护。

go提供了Columns方法用获取字段名，与大多数函数一样，读取失败将会返回一个err，因此需要检查错误。

cols, err := rows.Columns()
if err != nil{
   log.Fatalln(er)
}

对于不定字段查询，我们可以定义一个map的key和value用来表示数据库一条记录的row的值。通过rows.Columns得到的col作为map的key值。下面是一个例子

func main() {
    db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?parseTime=true")
    if err != nil{
        log.Fatal(err)
    }
    defer db.Close()

    rows, err := db.Query("SELECT * FROM user WHERE gid = 1")
    if err != nil{
        log.Fatalln(err)
    }
    defer rows.Close()


    cols, err := rows.Columns()
    if err != nil{
        log.Fatalln(err)
    }
    fmt.Println(cols)
    vals := make([][]byte, len(cols))
    scans := make([]interface{}, len(cols))

    for i := range vals{
        scans[i] = &vals[i]
    }

    var results []map[string]string

    for rows.Next(){
        err = rows.Scan(scans...)
        if err != nil{
            log.Fatalln(err)
        }

        row := make(map[string]string)
        for k, v := range vals{
            key := cols[k]
            row[key] = string(v)
        }
        results = append(results, row)
    }

    for k, v :=range results{
        fmt.Println(k, v)
    }
}

数据表user有三个字段，id（int），gid（int），real_name（varchar）。我们使用*取出所有的字段。使用rows.Columns()获取字段名，是一个string的数组。然后创建一个切片vals，用来存放所取出来的数据结果，类似是byte的切片。接下来还需要定义一个切片，这个切片用来scan，将数据库的值复制到给它。

完成这一步之后，vals则得到了scan复制给他的值，因为是byte的切片，因此在循环一次，将其转换成string即可。

转换后的row即我们取出的数据行值，最后组装到result切片中。

运行结果如下

[id gid real_name]
0 map[id:4 gid:1 real_name:瑟兰督依]
1 map[real_name:来格拉斯 id:5 gid:1]
2 map[id:15 gid:1 real_name:]

有一条记录的 real_name 值为空字串，因为其数据库存储的是NULL。

Exec

前面介绍了很多关于查询方面的内容，查询是读方便的内容，对于写，即插入更新和删除。这类操作与query不太一样，写的操作只关系是否写成功了。database/sql提供了Exec方法用于执行写的操作。

我们也见识到了，Eexc返回一个sql.Result类型，它有两个方法LastInsertId和RowsAffected。LastInsertId返回是一个数据库自增的id，这是一个int64类型的值。

Exec执行完毕之后，连接会立即释放回到连接池中，因此不需要像query那样再手动调用row的close方法。

关于LastInsertId和RowsAffected方法执行错误的返回，这跟底层的数据库是有关系的。因此更多的细节可以参考驱动的文档。

总结

目前，我们大致了解了数据库的CURD操作。对于读的操作，需要定义目标变量才能scan数据记录。scan会智能的帮我们转换一些数据，取决于定义的目标变量类型。对于null的处理，可以使用database/sql或驱动提供的类型声明，也可以使用[]byte将其转换成空字串。除了读数据之外，对于写的操作，database/sql也提供了Exec方法，并且对于sql.Result提供了LastInsertId和RowsAffected方法用于获取写后的结果。

在实际应用中，与数据库交互，往往写的sql语句还带有参数，这类sql可以称之为prepare语句。prepare语句有很多好处，可以防止sql注入，可以批量执行等。但是prepare的连接管理有其自己的机制，也有其使用上的陷进，关于prepare的使用，我们将会在以后讨论。