Write at the beginning

Recently, in the development of elastic search, there is a need to import the data of mysql specified tables into the index of elastic search. This requirement seems to be relatively easy. The general idea is to traverse every row of data in the table, convert it into json, and then submit it it to es through the api interface of es.

This seems to be a relatively simple requirement, but one thing is that this is a general operation, the program does not need to care about what kind of tables the teleoperation is, which fields it contains, and so on.

First edition

There is already a better solution on stack overflow:
Only used here go-sql-driver/mysql

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 
}

The code comes from Dumping MySQL tables to JSON with Golang

Second Edition

This code can basically meet the needs, but it seems that there is a problem, that is, the field corresponding to the type of field is not retained, all become string type, here found a more feasible solution:

There's this issue in the github of mysql driver. Returned values are always []byte

That's a specialty of MySQL: you have to use prepared statements to get the native types. MySQL has two protocols, one transmits everything as text, the other as the "real" type. And that binary protocol is only used when you use prepared statements. The driver is pretty much powerless to enforce a protocol and the text protocol takes less resources on the server.

This may help you:

stmt, err := db.Prepare(sqlString)
if err != nil { ...... }
defer stmt.Close()
rows, err := stmt.Query()

This description is quite clear. According to this logic, I changed the code into the following form:

    query := fmt.Sprintf("%s LIMIT %d OFFSET %d", handler.querySQL, limit, offset)

    stmt, err := db.Prepare(query)
    if err != nil {
        panic(err.Error())
    }
    defer stmt.Close()

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

    columns, err := rows.Columns()
    if err != nil {
        panic(err.Error())
    }

    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 {
            log.Fatalf("Sacn data error: %s", err.Error())
            continue
        }

        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
            }
        }

        // Serialized data
        b, err := json.Marshal(entry)
        if err != nil {
            log.Fatal(err.Error())
            continue
        }

        handler.docChan <- &document{
            // Here, the first field in the table is taken to calculate the document id.
            ID:   handler.FormatDocumentID(handler.dbName, handler.table, entry[columns[0]]),
            Data: b,
        }
    }

Tests show that only part of the type problem has been solved, and the shaping data type can be retained, but it seems that the floating-point field is still a string, which is still being explored.

To be continued...