使用INSERT IGNORE检索警告

Question

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.

Answer 1

Well, a couple of things:

• github.com/go-sql-driver/mysql appears to define the MySQLWarnings type which implements the standard error interface so I'm sure it has a way to return these warnings when performing a query or scanning for rows of the result of a query. I'd dig into the sources to find out.
• MySQL itself supports the SHOW WARNINGS statement so that you can just query it after performing your INSERT statement and iterate over the rows returned.