英文:
Retrieve warnings with INSERT IGNORE
问题
我需要将许多值插入到MySQL表中。其中一些可能会导致错误,但我仍然希望有效的值被插入。因此,我正在使用一个INSERT IGNORE
查询。
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...)
如果我在终端中运行这个查询,我会得到被拒绝的行作为警告:
Warning | 1062 | Duplicate entry '4' for key 'uk-uniquekey'
但是,我如何在Go中检索它们呢?
使用返回的Result
对象,我可以获取受影响的行数(从而找到被拒绝的行数),但我需要一种明确标识这些行的方法。
另外,我有很多行要插入,我不想为每一行使用一个INSERT
查询。
有没有好的解决方案来解决这个问题?
更新
我考虑使用一个单独的预处理查询,像这样:
stmt, _ := db.Prepare("INSERT INTO mytable (uniquekey, someotherfield) VALUES (?, ?);")
defer stmt.Close()
for _, element := range elements {
stmt.Exec(element.UniqueKey, element.SomeOtherField)
}
我对这个解决方案进行了基准测试,与扩展插入查询进行了比较。对于1000个条目(我承认我的机器不是很有竞争力...),这是我的结果:
循环单个预处理插入:10.652721825秒
单个扩展插入:0.092304425秒
考虑到我每天要插入成千上万个元素,我也不能使用这个解决方案。
英文:
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.
答案1
得分: 6
好的,以下是翻译好的内容:
嗯,有几件事情:
github.com/go-sql-driver/mysql
似乎定义了MySQLWarnings
类型,该类型实现了标准的error
接口,所以我确信它有一种方法在执行查询或扫描查询结果的行时返回这些警告。我建议查看源代码以了解详情。- MySQL 本身支持
SHOW WARNINGS
语句,因此您可以在执行完INSERT
语句后查询它,并迭代返回的行。
英文:
Well, a couple of things:
github.com/go-sql-driver/mysql
appears to define theMySQLWarnings
type which implements the standarderror
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 yourINSERT
statement and iterate over the rows returned.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论