douzhantao2857 2014-07-11 15:02
浏览 302
已采纳

使用INSERT IGNORE检索警告

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条回答 默认 最新

  • doumen5087 2014-07-11 15:46
    关注

    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.
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面