使用Golang将MySQL表转储为JSON

Question

使用Golang将MySQL表转储为JSON

22

我正在使用Go语言编写一个将MySQL转换为JSON的快速转储程序。然而，我发现从数据库中检索到的所有内容都是[]byte数组。因此，除了本机JSON整数或布尔值外，我得到的所有内容都被编码为字符串。

代码子集：

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("\n%s: type= %s\n", columns[i], reflect.TypeOf(col))

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

        s, _ := json.Marshal(record)
        w.Write(s)
        io.WriteString(w, "\n")
    }
}

- koblas

1

你想要创建什么样的输出？这将有助于理解你所询问的内容。当我听到“dumping mysql”时，我想到的是show tables和describe tablex，但我不确定这是否是你想要的。 - Brenden

"SELECT * FROM %s"，table的语句 - 因此是表的内容 - koblas

我不知道如何在不指定列类型的情况下获取结果集。由于我的回答没有满足问题，我将撤回它。 - rexposadas

@rexposadas 真的很希望你已经解决了它。 - koblas

6个回答

3

需要使用预处理语句来获取本地类型。MySQL有两种协议，一种将所有内容都作为文本传输，另一种则作为“真实”类型传输。只有在使用预处理语句时才会使用二进制协议。请参见https://github.com/go-sql-driver/mysql/issues/407。

下面的getJSON函数是正确的：

func getJSON(sqlString string) (string, error) {
  stmt, err := db.Prepare(sqlString)
  if err != nil {
    return "", err
  }
  defer stmt.Close()

  rows, err := stmt.Query()
  if err != nil {
    return "", err
  }
  defer rows.Close()

  columns, err := rows.Columns()
  if err != nil {
    return "", err
  }

  tableData := make([]map[string]interface{}, 0)

  count := len(columns)
  values := make([]interface{}, count)
  scanArgs := make([]interface{}, count)
  for i := range values {
    scanArgs[i] = &values[i]
  }

  for rows.Next() {
    err := rows.Scan(scanArgs...)
    if err != nil {
      return "", err
    }

    entry := make(map[string]interface{})
    for i, col := range columns {
      v := values[i]

      b, ok := v.([]byte)
      if (ok) {
        entry[col] = string(b)
      } else {
        entry[col] = v
      }
    }

    tableData = append(tableData, entry)
  }

  jsonData, err := json.Marshal(tableData)
  if err != nil {
    return "", err
  }

  return string(jsonData), nil 
}

- Vadim Homchik

2

由于 driver - database/sql 的交互基本上是单向的，当数据传递给 database/sql 后，驱动程序不能在任何方面帮助您，因此您无法做太多事情。

您可以尝试使用 http://godoc.org/github.com/arnehormann/sqlinternals/mysqlinternals

查询数据库
使用 cols, err := mysqlinternals.Columns(rows)获取包含列信息的切片。
创建一个新的变量 values := make([]interface{}, len(cols)) 并迭代 cols
对于每一列使用 refType, err := cols[i].ReflectGoType() 获取最接近的 Go 类型。
使用 values[i] = reflect.Zero(refType).Interface() 创建类型占位符。
使用 rows.Next() 和 err = rows.Scan(values...) 获取下一行数据。不要重新创建 values，请复制并重用它。

我猜这仍然会非常慢，但您应该能够做出一些进展。如果遇到问题，请提交问题 - 我会尽快处理。

- Arne

2

我在practice_db数据库中有一个名为users的表。下面的程序列出了表的结构和数据，并将其转换为JSON格式。你也可以在https://gist.github.com/hygull/1725442b0f121a5fc17b28e04796714d查看源代码。

/**
    {
        "created_on": "26 may 2017",
        "todos": [
            "go get github.com/go-sql-driver/mysql",
            "postman(optional)",
            "browser(optional)",    
        ],
        "aim": "Reading fname column into []string(slice of strings)"
    }
*/


/* 
    mysql> select * from users;
    +----+-----------+----------+----------+-------------------------------+--------------+-------------------------------------------------------------------------------------------------+
    | id | fname     | lname    | uname    | email                         | contact      | profile_pic                                                                                     |
    +----+-----------+----------+----------+-------------------------------+--------------+-------------------------------------------------------------------------------------------------+
    |  1 | Rishikesh | Agrawani | hygull   | rishikesh0014051992@gmail.com | 917353787704 | https://cdn4.iconfinder.com/data/icons/rcons-user/32/user_group_users_accounts_contacts-512.png |
    |  2 | Sandeep   | E        | sandeep  | sandeepeswar8@gmail.com       | 919739040038 | https://cdn4.iconfinder.com/data/icons/eldorado-user/40/user-512.png                            |
    |  3 | Darshan   | Sidar    | darshan  | sidardarshan@gmail.com        | 917996917565 | https://cdn4.iconfinder.com/data/icons/rcons-user/32/child_boy-512.png                          |
    |  4 | Surendra  | Prajapat | surendra | surendrakgadwal@gmail.com     | 918385894407 | https://cdn4.iconfinder.com/data/icons/rcons-user/32/account_male-512.png                       |
    |  5 | Mukesh    | Jakhar   | mukesh   | mjakhar.kjakhar@gmail.com     | 919772254140 | https://cdn2.iconfinder.com/data/icons/rcons-user/32/male-circle-512.png                        |
    +----+-----------+----------+----------+-------------------------------+--------------+-------------------------------------------------------------------------------------------------+
    5 rows in set (0.00 sec)

    mysql> 
*/

package main

import "log"
import "net/http"
import "encoding/json"

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

func users(w http.ResponseWriter, r *http.Request) {
    // db, err := sql.Open("mysql", "<username>:<password>@tcp(127.0.0.1:<port>)/<dbname>?charset=utf8" )
    db, err := sql.Open("mysql", "hygull:admin@67@tcp(127.0.0.1:3306)/practice_db?charset=utf8")

    w.Header().Set("Content-Type", "application/json")

    if err != nil {
        log.Fatal(err)
    }

    rows, err := db.Query("select id, fname, lname, uname, email, contact, profile_pic from users")

    if err != nil {
        log.Fatal(err)
    }

    type User struct {
        Id int       `json:"id"`
        Fname string `json:"firstname"`
        Lname string `json:"lastname"`
        Uname string `json:"username"`
        Email string `json:"email"`
        Contact int `json:"contact"`
        ProfilePic string `json:"profile_pic"`
    }

    var users []User

    for rows.Next() {
        var id, contact int
        var fname string
        var lname string
        var uname, email, profile_pic string

        rows.Scan(&id ,&fname, &lname, &uname, &email, &contact, &profile_pic)
        users = append(users, User{id, fname, lname, uname, email, contact, &profile_pic })
    }

    usersBytes, _ := json.Marshal(&users)

    w.Write(usersBytes)
    db.Close()
}

func main() {
    http.HandleFunc("/users/", users)
    http.ListenAndServe(":8080", nil)
}

/* REQUSET

   http://127.0.0.1:8080/users/
*/

/* RESPONSE
[
  {
    "id": 1,
    "firstname": "Rishikesh",
    "lastname": "Agrawani",
    "username": "hygull",
    "email": "rishikesh0014051992@gmail.com",
    "contact": 917353787704,
    "profile_pic": "https://cdn4.iconfinder.com/data/icons/rcons-user/32/user_group_users_accounts_contacts-512.png"
  },
  {
    "id": 2,
    "firstname": "Sandeep",
    "lastname": "E",
    "username": "sandeep",
    "email": "sandeepeswar8@gmail.com",
    "contact": 919739040038,
    "profile_pic": "https://cdn4.iconfinder.com/data/icons/eldorado-user/40/user-512.png"
  },
  {
    "id": 3,
    "firstname": "Darshan",
    "lastname": "Sidar",
    "username": "darshan",
    "email": "sidardarshan@gmail.com",
    "contact": 917996917565,
    "profile_pic": "https://cdn4.iconfinder.com/data/icons/rcons-user/32/child_boy-512.png"
  },
  {
    "id": 4,
    "firstname": "Surendra",
    "lastname": "Prajapat",
    "username": "surendra",
    "email": "surendrakgadwal@gmail.com",
    "contact": 918385894407,
    "profile_pic": "https://cdn4.iconfinder.com/data/icons/rcons-user/32/account_male-512.png"
  },
  {
    "id": 5,
    "firstname": "Mukesh",
    "lastname": "Jakhar",
    "username": "mukesh",
    "email": "mjakhar.kjakhar@gmail.com",
    "contact": 919772254140,
    "profile_pic": "https://cdn2.iconfinder.com/data/icons/rcons-user/32/male-circle-512.png"
  }
]
*/

- hygull

1

这正是我正在寻找的！ - Richard Varno

我正在尝试做这件事情，但同样的代码（+我的数据库结构）对我来说不起作用。当我试图在网页中显示JSON时，它显示为空的JSON是[{},{},{},{}][91 123 125 44 123 125 44 123 125 44 123 125 93]。我该如何解决这个问题？我怎么能够以JSON格式在网页中看到JSON？ - Shuvo

我可以看到连续的两个列表。这不是一个有效的JSON。 - hygull

0

根据这里的答案，这是我能想到的最有效的代码。请注意，这将每一行输出为单独的JSON数组，以节省键名重复。

// OutputJSONMysqlRowsStream outputs rows as a JSON array stream to save ram & output size due to key name repetition
func OutputJSONMysqlRowsStream(writer http.ResponseWriter, rows *sql.Rows) {

    defer rows.Close()

    columns, err := rows.Columns()

    if err != nil {
        OutputJSONError(writer, "Failed to get column names")
        return
    }

    jsonColumns, err := json.Marshal(columns)

    if err != nil {
        OutputJSONError(writer, "Failed to encode json of column names")
        return
    }

    writer.Header().Set("Content-Type", "application/cal-json-stream; charset=utf-8")

    fmt.Fprintln(writer, "{\"status\": \"done\", \"data\":{ \"json_stream_fields\":"+string(jsonColumns)+"}}")

    columnCount := len(columns)
    rowDataHolder := make([]interface{}, columnCount)
    rowDataHolderPointers := make([]interface{}, columnCount)

    if err != nil {
        log.Println(err)
    }

    for rows.Next() {

        for i := 0; i < columnCount; i++ {
            rowDataHolderPointers[i] = &rowDataHolder[i]
        }

        err := rows.Scan(rowDataHolderPointers...)

        if err != nil {
            log.Println(err)
        } else {

            for i, value := range rowDataHolder {
                tempValue, ok := value.([]byte)

                if ok {
                    rowDataHolder[i] = string(tempValue)
                }
            }

            jsonEncoder := json.NewEncoder(writer)
            err = jsonEncoder.Encode(rowDataHolder)

            if err != nil {
                log.Println(err)
            }
        }
    }
}

- Dumi Jay

-1

你可以将表转储为 JSON，但是所有内容都会变成字符串 :(

q := "select * from table"
debug("SQL: %s", q)

rows, err := db.Query(q)
checkError(err)
defer rows.Close()

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 {
            record[columns[i]] = fmt.Sprintf("%s", string(col.([]byte)))
        }
    }

    s, _ := json.Marshal(record)
    fmt.Printf("%s\n", s)
}

- Andrew Yasinsky

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- turkenh · Accepted Answer

我还需要将数据库表导出为json格式，这是我如何实现的：

(与该话题下的另一个回答不同，所有内容并非都是字符串形式，感谢此答案：https://dev59.com/pGMm5IYBdhLWcg3wIsQ2#17885636，我可以正确获取整数字段)

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 
}

以下是样例输出：

[{"ID":0,"Text":"Zero"},{"ID":1,"Text":"One"},{"ID":2,"Text":"Two"}]