同事在研究Grafana reporter，一个通过把页把转化为pdf并可以邮件报告的工具。在其中引入的某个模块部分，其调用时发现报错。我对zabbix的东西久未关注了，帮其看了下报错，把报错的模块单独抽离出来单独调用发现其中报”undefined: rows”错误。代码逻辑比较简单，根据传入的id不同，执行不同的SQL并返回相应的值。具体代码如下：

 1package main
 2import (
 3    _ "github.com/go-sql-driver/mysql"
 4    "database/sql"
 5    "fmt"
 6)
 7const (
 8        mysql_user   = "zabbix"
 9        mysql_passwd = "361way.com"
10        mysql_ip     = "10.211.139.10:3306" mysql_dbname = "zabbix"
11)
12func db(id int) int {
13        conn, err := sql.Open("mysql", mysql_user+":"+mysql_passwd+"@tcp("+mysql_ip+")/"+mysql_dbname)
14        if err != nil {
15              fmt.Println(err)
16        }
17        switch {
18            case id == 6:
19                rows,_ := conn.Query("select count(*) as num from daily_t1 where ((select max(runtimestamp) from daily_t1) - runtimestamp) <= 3")
20            case id == 8:
21                rows,_ := conn.Query("select count(*) as num from daily_t2 where ((select max(runtimestamp) from daily_t2) - runtimestamp) <= 3")
22            case id == 10:
23                rows,_ := conn.Query("select count(*) as num from daily_t3 where ((select max(runtimestamp) from daily_t3) - runtimestamp) <= 3")
24        }
25        defer rows.Close()
26        var num int
27        for rows.Next() {
28            err := rows.Scan(&num)
29            if err != nil {
30                panic(err)
31            }
32        }
33        if err != nil {
34              fmt.Println(err)
35        }
36        return num
37}
38func main() {
39   num := db(8)
40   fmt.Println(num)
41}

猛一看，代码确实未发现明显错误，冒号确实是已经做了变量定义了。因为这段代码如果去掉switch语句，单独只用一句，完全可以正常执行，换用switch和if都不行。处理思路也比较简单，逻辑外定义，逻辑内再赋值就好了，这里只列下主要更改的部分，如下：

 1var num int
 2var rows *sql.Rows
 3switch {
 4case id == 6:
 5        row, _ := conn.Query("select count(*) as num from daily_t1 where ((select max(runtimestamp) from daily_t1) - runtimestamp) <= 3")
 6        rows = row
 7case id == 8:
 8        row, _ := conn.Query("select count(*) as num from daily_t2 where ((select max(runtimestamp) from daily_t2) - runtimestamp) <= 3")
 9        rows = row
10case id == 10:
11        row, _ := conn.Query("select count(*) as num from daily_t3 where ((select max(runtimestamp) from daily_t3) - runtimestamp) <= 3")
12        rows = row
13default:
14        num = 0
15}
16defer rows.Close()

但是这个感觉能用性不强啊，能不能换种看起来逻辑更通顺的搞法。可以单独对要处理的语句做一层封装，再在后面的逻辑处理里调用不就好了。如果返回的值不同，只要上面的封装部分中修改就行了。再次修改也能正常执行的代码如下：

 1func DoQuery(db *sql.DB, sqlInfo string) int {
 2        var num int
 3        rows, err := db.Query(sqlInfo)
 4        if err != nil {
 5                log.Fatal(err)
 6                num = 0
 7        }
 8        defer rows.Close()
 9        for rows.Next() {
10                //var num int
11                if err := rows.Scan(&num); err != nil {
12                        log.Fatal(err)
13                        num = num
14                        //return num,nil
15                }
16        }
17          return num
18}
19func db(id int) int {
20        conn, err := sql.Open("mysql", mysql_user+":"+mysql_passwd+"@tcp("+mysql_ip+")/"+mysql_dbname)
21        if err != nil {
22                fmt.Println(err)
23        }
24        row6 := "select count(*) as num from daily_t1 where ((select max(runtimestamp) from daily_t1) - runtimestamp) <= 3"
25        row8 := "select count(*) as num from daily_t2 where ((select max(runtimestamp) from daily_t2) - runtimestamp) <= 3"
26        row10 := "select count(*) as num from daily_t3 where ((select max(runtimestamp) from daily_t3) - runtimestamp) <= 3"
27  var num int
28        if id == 6 {
29                num = DoQuery(conn,row6)
30        }
31        if id == 8 {
32                num = DoQuery(conn,row8)
33        }
34        if id == 10 {
35                num = DoQuery(conn,row10)
36        }
37        return num
38}

当前的问题是解决了，因为这里调用的是count语句，明确返回是一个整型值。如果是select某个表的limit 10条数据呢？返回的是多值怎么办？可以通过ql Rows的执行结果转化保存成map，上面的封装代码就变成了如下：

 1func DoQuery(db *sql.DB, sqlInfo string, args ...interface{}) ([]map[string]interface{}, error) {
 2    rows, err := db.Query(sqlInfo, args...)
 3    if err != nil {
 4        return nil, err
 5    }
 6    columns, _ := rows.Columns()
 7    columnLength := len(columns)
 8    cache := make([]interface{}, columnLength) //临时存储每行数据
 9    for index, _ := range cache { //为每一列初始化一个指针
10        var a interface{}
11        cache[index] = &a
12    }
13    var list []map[string]interface{} //返回的切片
14    for rows.Next() {
15        _ = rows.Scan(cache...)
16        item := make(map[string]interface{})
17        for i, data := range cache {
18            item[columns[i]] = *data.(*interface{}) //取实际类型
19        }
20        list = append(list, item)
21    }
22    _ = rows.Close()
23    return list, nil
24}

可以说上面这个的逻辑处理通用性更强了。

当然，其实也可以不用这么麻烦，使用gorm模块不就完事了。语句写的更短（不过没有直接SQL语句看起来直观，有利有弊吧），具体GORM的使用可以参看官方页面：http://gorm.io/zh_CN/docs/query.html