使用INSERT IGNORE检索警告

huangapple go评论89阅读模式
英文:

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 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.

huangapple
  • 本文由 发表于 2014年7月11日 23:02:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/24700807.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定