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

如何将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?

  • 写回答

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...)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 数据库获取信息反馈出错,直接查询了ref字段并且还使用了User文档的_id而不是自己的
  • ¥15 将安全信息用到以下对象时发生以下错误:c:dumpstack.log.tmp 另一个程序正在使用此文件,因此无法访问
  • ¥15 速度位置规划实现精确定位的问题
  • ¥15 代码问题:df = pd.read_excel('c:\User\18343\Desktop\wpsdata.xlxs')路径读不到
  • ¥15 为什么视频算法现在全是动作识别?
  • ¥15 编写一段matlab代码
  • ¥15 用Python做岩石类别鉴定软件
  • ¥15 关于调取、提交更新数据库记录的问题
  • ¥15 之前删了盘从下vs2022遇见这个问题 搞了一整天了
  • ¥15 从Freecad中宏下载的DesignSPHysics,出现如下问题是什么原因导致的(语言-python)