Getting number of rows inserted for ON DUPLICATE KEY UPDATE multiple insert?

Question

I have a very large table with a primary key of `BINARY(20)`. 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. 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. 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? How can I overcome this? Thanks

Accepted Answer

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

```
(numberOfValuesInInsert * 2) - mysql_affected_rows()
```

EDIT:

As tomas points out, The MySQL docs actually say:

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

[emphasis mine]

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.

Getting number of rows inserted for ON DUPLICATE KEY UPDATE multiple insert?

Solution

Problem