douyoupingji7238
2019-01-12 00:17
浏览 436
已采纳

如何将JSON对象转换为MySQL行？

I'd like to take a JSON string representing an object from a 3rd party API and insert it into a MySQL table. The JSON object properties match the table fields 1-to-1. There are several hundred columns in this table/JSON object. And there will be a few dozen rows to insert at any time.

I'd rather not make a huge struct. But if I need to, then I'd rather not db.Prepare() an INSERT statement with several hundred "?"s. But if I have to then I'd rather not Have to write a stmt.Exec() with several hundred parameters.

Is there a good way to do this in golang? Or is it just going to be extremely inefficient?

图片转代码服务由CSDN问答提供 功能建议

我想从第3方API中获取代表对象的JSON字符串，并将其插入到MySQL表中 。 JSON对象属性与表字段1-to-1匹配。 该表/ JSON对象中有几百列。 而且随时都将有几十行要插入。

我宁愿不要制作庞大的结构。 但是，如果需要的话，我宁愿 db.Prepare（）带有几百个“？”的INSERT语句。 但是如果需要的话，我宁愿不必编写带有数百个参数的 stmt.Exec（）。

是否有一个很好的方法 这个在golang吗？ 还是只是效率极低？

  • 写回答
  • 赞 踩
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongqiaozhe5070 2019-01-12 00:49
    已采纳

    Use the following given a slice of valid database field names fieldNames and JSON data data:

    var j map[string]interface{}
if err := json.Unmarshal(data, &j); err != nil {
    // handle error
}
var names []string
var inserts []string
var values []interface{}
for _, n := range fieldNames {
    if v, ok := j[n]; ok {
        names = append(names, n)
        inserts = append(inserts, "?")
        values = append(values, v)
    }
}
statement := "insert into yourTable (" +
    strings.Join(names, ", ") +
    ") values (" + strings.Join(inserts, ", ") + ")"
err := db.Exec(statement, values...)

    To avoid SQL injection attacks, it's important to work from a slice of known column names.

    You can query the database to create the fieldNames slice. See Get table column names in MySQL? for the query required.

    If the column names and JSON names are different, then replace the slice with a map where the keys are the column names and the values are the JSON names:

    fieldNames := map[string]string{
  "column1": "json1",
  ... and so on
}

var j map[string]interface{}
if err := json.Unmarshal(data, &j); err != nil {
    // handle error
}
var names []string
var inserts []string
var values []interface{}
for dbName, jsonName := range fieldNames {
    if v, ok := j[jsonName]; ok {
        names = append(names, dbName)
        inserts = append(inserts, "?")
        values = append(values, v)
    }
}
statement := "insert into yourTable (" +
    strings.Join(names, ", ") +
    ") values (" + strings.Join(inserts, ", ") + ")"
err := db.Exec(statement, values...)
    赞 踩 打赏 评论

报告相同问题？

提交

相关推荐 更多相似问题