go：将mysql返回数据转换为一个字典数组

go官方库返回的是一个rows对象，非常蛋疼。 写了一个很神奇的函数，把返回值db.Rows转换成[]map[string]interface{}结构。

代码语言：javascript复制

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
}

db interface map rows string

0 人点赞