如何一次插入多个数据

I know that Insert multiple data at once more efficiency:

INSERT INTO test(n1, n2, n3) 
VALUES(v1, v2, v3),(v4, v5, v6),(v7, v8, v9);

How to do that in golang?

data := []map[string]string{
   {"v1":"1", "v2":"1", "v3":"1"},
   {"v1":"2", "v2":"2", "v3":"2"},
   {"v1":"3", "v2":"3", "v3":"3"},
}
//I do not want to do it
for _, v := range data {
    sqlStr := "INSERT INTO test(n1, n2, n3) VALUES(?, ?, ?)"
    stmt, _ := db.Prepare(sqlStr)
    res, _ := stmt.Exec(v["v1"], v["v2"], v["v3"])
}

Use string splice, but it's not good. db.Prepare more safer, right?

sqlStr := "INSERT INTO test(n1, n2, n3) VALUES"
for k, v := range data {
    if k == 0 {
        sqlStr += fmt.Sprintf("(%v, %v, %v)", v["v1"], v["v2"], v["v3"])
    } else {
        sqlStr += fmt.Sprintf(",(%v, %v, %v)", v["v1"], v["v2"], v["v3"])
    } 
}
res, _ := db.Exec(sqlStr)

I need a function safer and efficient insert mulitple data at once.

why not something like this? (writing here without testing so there might be syntax errors):

sqlStr := "INSERT INTO test(n1, n2, n3) VALUES "
vals := []interface{}{}

for _, row := range data {
    sqlStr += "(?, ?, ?),"
    vals = append(vals, row["v1"], row["v2"], row["v3"])
}
//trim the last ,
sqlStr = sqlStr[0:len(sqlStr)-2]
//prepare the statement
stmt, _ := db.Prepare(sqlStr)

//format all vals at once
res, _ := stmt.Exec(vals...)

For Postgres lib pq supports bulk inserts: https://godoc.org/github.com/lib/pq#hdr-Bulk_imports

But same can be achieved through below code but where it is really helpful is when one tries to perform bulk conditional update (change the query accordingly).

For performing similar bulk inserts for Postgres, you can use the following function.

// ReplaceSQL replaces the instance occurrence of any string pattern with an increasing $n based sequence
func ReplaceSQL(old, searchPattern string) string {
   tmpCount := strings.Count(old, searchPattern)
   for m := 1; m <= tmpCount; m++ {
      old = strings.Replace(old, searchPattern, "$"+strconv.Itoa(m), 1)
   }
   return old
}

So above sample becomes

sqlStr := "INSERT INTO test(n1, n2, n3) VALUES "
vals := []interface{}{}

for _, row := range data {
   sqlStr += "(?, ?, ?),"
   vals = append(vals, row["v1"], row["v2"], row["v3"])
}

//trim the last ,
sqlStr = strings.TrimSuffix(sqlStr, ",")

//Replacing ? with $n for postgres
sqlStr = ReplaceSQL(sqlStr, "?")

//prepare the statement
stmt, _ := db.Prepare(sqlStr)

//format all vals at once
res, _ := stmt.Exec(vals...)

If you enable multi statements , then you can execute multiple statement at once. With that , you should be able to handle multiple inserts.

https://github.com/go-sql-driver/mysql#multistatements