I need to insert a lot of values into a MySQL table. Some of them might cause an error, but I still want the valid ones to be inserted. Consequently, I'm using an INSERT IGNORE
query.
query := "INSERT IGNORE INTO mytable "
query += "(uniquekey, someotherfield) "
query += "VALUES "
var params []interface{}
for _, element := range elements {
query += "(?, ?),"
params = append(params, element.UniqueKey, element.SomeOtherField)
}
_, err := db.Exec(query[:len(query)-1] + ";", params...)
If I run this query in my terminal, I would get the rejected rows as warnings:
Warning | 1062 | Duplicate entry '4' for key 'uk-uniquekey'
But how could I retrieve them using Go?
Using the returned Result
object, I can get the number of affected rows (and thus find the number of rejected ones), but I need a way to clearly identify these rows.
Also, I have a lot of rows to insert, and I don't want to use an INSERT
query for each of them.
Are there any good solution for this problem?
Update
I thought of using a single prepared query like this:
stmt, _ := db.Prepare("INSERT INTO mytable (uniquekey, someotherfield) VALUES (?, ?);")
defer stmt.Close()
for _, element := range elements {
stmt.Exec(element.UniqueKey, element.SomeOtherField)
}
And I benchmarcked this solution, in comparison with the extended insert query. For 1000 entries (I admit my machine is not very competitive...), here are my results:
Loop on prepared single insert: 10.652721825 s
Single extended insert: 0.092304425 s
Considering I have thousands of elements to insert everyday, I can't use this solution either.