dongwopu8210
2013-11-15 00:55
浏览 379

使用Golang将MySQL表转储为JSON

Was putting together a quick dumper for MySQL to JSON in Go. However I find that everything that I retrieve from the database is a []byte array. Thus instead of native JSON integers or booleans, I'm getting everything encoded as strings.

Subset of the code:

import (
    "encoding/json"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)


func dumpTable(w io.Writer, table) {
    // ...

    rows, err := Query(db, fmt.Sprintf("SELECT * FROM %s", table))
    checkError(err)
    columns, err := rows.Columns()
    checkError(err)

    scanArgs := make([]interface{}, len(columns))
    values   := make([]interface{}, len(columns))

    for i := range values {
        scanArgs[i] = &values[i]
    }

    for rows.Next() {
        err = rows.Scan(scanArgs...)
        checkError(err)

        record := make(map[string]interface{})

        for i, col := range values {
            if col != nil {
                fmt.Printf("
%s: type= %s
", columns[i], reflect.TypeOf(col))

                switch t := col.(type) {
                default:
                    fmt.Printf("Unexpected type %T
", t)
                case bool:
                    fmt.Printf("bool
")
                    record[columns[i]] = col.(bool)
                case int:
                    fmt.Printf("int
")
                    record[columns[i]] = col.(int)
                case int64:
                    fmt.Printf("int64
")
                    record[columns[i]] = col.(int64)
                case float64:
                    fmt.Printf("float64
")
                    record[columns[i]] = col.(float64)
                case string:
                    fmt.Printf("string
")
                    record[columns[i]] = col.(string)
                case []byte:   // -- all cases go HERE!
                    fmt.Printf("[]byte
")
                    record[columns[i]] = string(col.([]byte))
                case time.Time:
                    // record[columns[i]] = col.(string)
                }
            }
        }

        s, _ := json.Marshal(record)
        w.Write(s)
        io.WriteString(w, "
")
    }
}
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

6条回答 默认 最新

  • doutui2883 2015-03-20 10:15
    最佳回答

    I also needed to dump database tables to json and here is how I achieved: (different than another answer in this topic, everything is not string, thanks to this answer: https://stackoverflow.com/a/17885636/4124416, I could get integer fields correctly)

    func getJSON(sqlString string) (string, error) {
        rows, err := db.Query(sqlString)
        if err != nil {
            return "", err
        }
        defer rows.Close()
        columns, err := rows.Columns()
        if err != nil {
            return "", err
        }
        count := len(columns)
        tableData := make([]map[string]interface{}, 0)
        values := make([]interface{}, count)
        valuePtrs := make([]interface{}, count)
        for rows.Next() {
            for i := 0; i < count; i++ {
              valuePtrs[i] = &values[i]
            }
            rows.Scan(valuePtrs...)
            entry := make(map[string]interface{})
            for i, col := range columns {
                var v interface{}
                val := values[i]
                b, ok := val.([]byte)
                if ok {
                    v = string(b)
                } else {
                    v = val
                }
                entry[col] = v
            }
            tableData = append(tableData, entry)
        }
        jsonData, err := json.Marshal(tableData)
        if err != nil {
            return "", err
        }
        fmt.Println(string(jsonData))
        return string(jsonData), nil 
    }
    

    Here is a sample output:

    [{"ID":0,"Text":"Zero"},{"ID":1,"Text":"One"},{"ID":2,"Text":"Two"}]
    
    评论
    解决 无用
    打赏 举报
查看更多回答(5条)

相关推荐 更多相似问题