获取为ON DUPLICATE KEY UPDATE多次插入而插入的行数? [英] Getting number of rows inserted for ON DUPLICATE KEY UPDATE multiple insert?

问题描述

我有一个很大的表，主键为BINARY(20).

I have a very large table with a primary key of BINARY(20).

该表大约有1700万行. Cron作业每小时都会使用ON_DUPLICATE_KEY_UPDATE语法尝试向该表中插入多达50,000个新条目.

The table has around 17 million rows. Every hour a cron job tries to insert as many as 50,000 new entries into this table with the ON_DUPLICATE_KEY_UPDATE syntax.

cronjob中的每个插入都具有1,000个值(多个插入).如何从此查询获取插入表中的行数?我前后都无法进行行计数，因为大约有1700万行，而且查询太昂贵了.

Each insert in the cronjob is with 1,000 values (multiple insert). How can I get the number of rows inserted into the table from this query? I cannot do a row count before and after as there are around 17million rows and the query is too expensive.

在mysql手册中说，对于插入的行，受影响的行数为1，对于更新的字段为2，这意味着在我的1000 INSERT ON DUPLICATE KEY UPDATE查询中，我可能影响的行数为1000 -2000，但是我无法告诉您从该号码中插入了多少条记录?

In the manual mysql says for a row inserted the affected number of rows is 1 and for an updated field it is 2, meaning in my 1000 INSERT ON DUPLICATE KEY UPDATE query I could have affected rows ranging from 1000 - 2000, but I have no way of telling how many records were inserted from this number?

我该如何克服?

谢谢

推荐答案

插入数为2000减去受影响的行数.更普遍地:

The number of inserts would be 2000 minus the number of affected rows. More generally:

(numberOfValuesInInsert * 2) - mysql_affected_rows()

作为 MySQL文档实际说:

使用ON DUPLICATE KEY UPDATE，如果将行作为新行插入，则每行的受影响行值为1；如果更新了现有行，则为2； ；如果将现有行设置为当前行，则为0值.

[强调我的]

因此，如果有可能将现有行设置为相同的值，则无法确定更新了多少行与插入了多少行，因为两次插入与一次使用不同值的更新+一次使用相同值的更新是无法区分的

Consequently, if setting an existing row to the same values is a possibility, it's impossible to tell how many rows were updated vs. inserted, since two inserts would be indistinguishable from one update with different values + one update with the same values.

这篇关于获取为ON DUPLICATE KEY UPDATE多次插入而插入的行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！