Golang将MySQL表格转储为JSON [英] Dumping MySQL tables to JSON with Golang
问题描述
[] byte
数组。因此,而不是原生的JSON整数或布尔值,我将所有内容都编码为字符串。 代码的子集:
(
encoding / json
database / sql
_github.com/go-sql-driver/mysql
)
func dumpTable(w io.Writer,table){
// ...
行,err:= Query(db,fmt.Sprintf(SELECT * FROM%s,table))
checkError(err)
列,err:= rows.Columns()
checkError(err)
scanArgs:= make([] interface {},len(columns))
values:= make([] interface {},len(columns))
为i:=范围值{
scanArgs [i] =& values [i]
}
for rows.Next(){
err = rows .Scan(scanArgs ...)
checkError(err)
$ b记录:= make(map [string] interface {})
for i,col: =范围值{
如果col!= nil {
fmt.Printf(\\\
%s :type =%s\\\
,columns [i],reflect.TypeOf(col))
switch t:= col。(type){
default:
fmt.Printf(Unexpected type%T \ n,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 \ n)
record [columns [i]] = col。(float64)
case string:
fmt.Printf(string\\\
)
record [列[i]] = col。(字符串)
案例[]字节:// - 所有案例都在这里!
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,\ n)
}
}
我还需要将数据库表转储到json,以下是我如何实现:
(与另一个答案不同在这个主题中,一切都不是字符串,这要归功于这个答案: https://stackoverflow.com/a/17885636/4124416
$ p $ func getJSON(sqlString string)(string,error){
rows ,err:= db.Query(sqlString)
if err!= nil {
return,err
}
推迟rows.Close()
列,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 ++ {
valuePtrs [i] =& values [i]
}
rows.Scan(valuePtrs ...)
entry:= make(map [string] interface { })
for i,col:= range列{
var v interface {} $ b $ 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
}
$ p $下面是一个输出示例:
[{ID:0,Text:Zero},{ID:1,Text :One},{ID:2,Text:Two}]
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("\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")
}
}
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"}]
这篇关于Golang将MySQL表格转储为JSON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!