go：将mysql返回数据转换为一个字典数组
- 2019 年 11 月 22 日
- 筆記
go官方库返回的是一个rows对象，非常蛋疼。 写了一个很神奇的函数，把返回值db.Rows转换成[]map[string]interface{}结构。
package main import ( "database/sql" "fmt" "github.com/go-sql-driver/mysql" "log" "math" "reflect" "strconv" "strings" ) func main() { db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/db_viewlib?charset=utf8&parseTime=True") if err != nil { log.Fatal(err) } defer db.Close() sql := "select * from t_attr_1400face_common limit 1000" dbRows, err := db.Query(sql) if err != nil { fmt.Printf("query err %v", err) } rows := RowsToMap(dbRows) for _, item := range rows { fmt.Println(item) } } var BytesKind = reflect.TypeOf(sql.RawBytes{}).Kind() var TimeKind = reflect.TypeOf(mysql.NullTime{}).Kind() func checkErr(err error) { if err != nil { fmt.Printf("checkErr:%v", err) } } func ToStr(strObj interface{}) string { switch v := strObj.(type) { case string: return v case []byte: return string(v) case nil: return "" default: return fmt.Sprintf("%v", strObj) } } func ToInt(intObj interface{}) int { // 假定int == int64，运行在64位机 switch v := intObj.(type) { case []byte: return ToInt(string(v)) case int: return v case int8: return int(v) case int16: return int(v) case int32: return int(v) case int64: return int(v) case uint: return int(v) case uint8: return int(v) case uint16: return int(v) case uint32: return int(v) case uint64: if v > math.MaxInt64 { info := fmt.Sprintf("ToInt, error, overflowd %v", v) panic(info) } return int(v) case float32: return int(v) case float64: return int(v) case string: strv := v if strings.Contains(v, ".") { strv = strings.Split(v, ".")[0] } if strv == "" { return 0 } if intv, err := strconv.Atoi(strv); err == nil { return intv } } fmt.Printf(fmt.Sprintf("ToInt err, %v, %v not supporttedn", intObj, reflect.TypeOf(intObj).Kind())) return 0 } func RowsToMap(rows *sql.Rows) []map[string]interface{} { result := make([]map[string]interface{}, 0) for rows.Next() { cols, err := rows.Columns() checkErr(err) colsTypes, err := rows.ColumnTypes() checkErr(err) dest := make([]interface{}, len(cols)) destPointer := make([]interface{}, len(cols)) for i := range dest { destPointer[i] = &dest[i] } err = rows.Scan(destPointer...) checkErr(err) rowResult := make(map[string]interface{}) for i, colVal := range dest { colName := cols[i] itemType := colsTypes[i].ScanType() //fmt.Printf("type %v n", itemType) switch itemType.Kind() { case BytesKind: rowResult[colName] = ToStr(colVal) case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64, reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64: rowResult[colName] = ToInt(colVal) case TimeKind: fmt.Println("time", colVal, reflect.TypeOf(colVal)) rowResult[colName] = ToStr(colVal) default: rowResult[colName] = ToStr(colVal) } } result = append(result, rowResult) } return result }
