Retrieve warnings with INSERT IGNORE
go, mysql
Solution
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.
Problem
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.