运维之路

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

package main
import (
    _ "github.com/go-sql-driver/mysql"
    "database/sql"
    "fmt"
)
const (
        mysql_user   = "zabbix"
        mysql_passwd = "361way.com"
        mysql_ip     = "10.211.139.10:3306" mysql_dbname = "zabbix"
)
func db(id int) int {
        conn, err := sql.Open("mysql", mysql_user+":"+mysql_passwd+"@tcp("+mysql_ip+")/"+mysql_dbname)
        if err != nil {
              fmt.Println(err)
        }
        switch {
            case id == 6:
                rows,_ := conn.Query("select count(*) as num from daily_t1 where ((select max(runtimestamp) from daily_t1) - runtimestamp) <= 3")
            case id == 8:
                rows,_ := conn.Query("select count(*) as num from daily_t2 where ((select max(runtimestamp) from daily_t2) - runtimestamp) <= 3")
            case id == 10:
                rows,_ := conn.Query("select count(*) as num from daily_t3 where ((select max(runtimestamp) from daily_t3) - runtimestamp) <= 3")
        }
        defer rows.Close()
        var num int
        for rows.Next() {
            err := rows.Scan(&num)
            if err != nil {
                panic(err)
            }
        }
        if err != nil {
              fmt.Println(err)
        }
        return num
}
func main() {
   num := db(8)
   fmt.Println(num)
}

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

var num int
var rows *sql.Rows
switch {
case id == 6:
        row, _ := conn.Query("select count(*) as num from daily_t1 where ((select max(runtimestamp) from daily_t1) - runtimestamp) <= 3")
        rows = row
case id == 8:
        row, _ := conn.Query("select count(*) as num from daily_t2 where ((select max(runtimestamp) from daily_t2) - runtimestamp) <= 3")
        rows = row
case id == 10:
        row, _ := conn.Query("select count(*) as num from daily_t3 where ((select max(runtimestamp) from daily_t3) - runtimestamp) <= 3")
        rows = row
default:
        num = 0
}
defer rows.Close()

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

func DoQuery(db *sql.DB, sqlInfo string) int {
        var num int
        rows, err := db.Query(sqlInfo)
        if err != nil {
                log.Fatal(err)
                num = 0
        }
        defer rows.Close()
        for rows.Next() {
                //var num int
                if err := rows.Scan(&num); err != nil {
                        log.Fatal(err)
                        num = num
                        //return num,nil
                }
        }
          return num
}
func db(id int) int {
        conn, err := sql.Open("mysql", mysql_user+":"+mysql_passwd+"@tcp("+mysql_ip+")/"+mysql_dbname)
        if err != nil {
                fmt.Println(err)
        }
        row6 := "select count(*) as num from daily_t1 where ((select max(runtimestamp) from daily_t1) - runtimestamp) <= 3"
        row8 := "select count(*) as num from daily_t2 where ((select max(runtimestamp) from daily_t2) - runtimestamp) <= 3"
        row10 := "select count(*) as num from daily_t3 where ((select max(runtimestamp) from daily_t3) - runtimestamp) <= 3"
  var num int
        if id == 6 {
                num = DoQuery(conn,row6)
        }
        if id == 8 {
                num = DoQuery(conn,row8)
        }
        if id == 10 {
                num = DoQuery(conn,row10)
        }
        return num
}

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

func DoQuery(db *sql.DB, sqlInfo string, args ...interface{}) ([]map[string]interface{}, error) {
	rows, err := db.Query(sqlInfo, args...)
	if err != nil {
		return nil, err
	}
	columns, _ := rows.Columns()
	columnLength := len(columns)
	cache := make([]interface{}, columnLength) //临时存储每行数据
	for index, _ := range cache { //为每一列初始化一个指针
		var a interface{}
		cache[index] = &a
	}
	var list []map[string]interface{} //返回的切片
	for rows.Next() {
		_ = rows.Scan(cache...)
		item := make(map[string]interface{})
		for i, data := range cache {
			item[columns[i]] = *data.(*interface{}) //取实际类型
		}
		list = append(list, item)
	}
	_ = rows.Close()
	return list, nil
}

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

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