MySQL on duplicate key update + affected row count

Vote count:

0

Hi Overflowers (longterm lurker, first-time question-askener),

Using the following mySQL query:

INSERT INTO table (col_a,col_b,col_c,col_d) VALUES (val_a,val_b...val_x) ON DUPLICATE KEY UPDATE col_d = VALUES(col_d)

Given that mySQL reports:

- 1 row affected per insert

- 2 rows affected per update

- 0 rows affected per duplicate (and info() seemingly incorrectly(?) always reports 0 duplicates regardless of how many duplicated entries have been skipped)

And the only figures I have are:

- The total number of rows I have tried to insert/update (this can be any number like 47, 163, 282 - it is not a set number of rows each time)

- The total number of reported affected rows from mySQL

Is there any mathematical (or other) wizardry capable of reliably returning the number of inserts, updates and rows skipped (duplicates)?

Please note I have tried both the accepted answer and the other equations listed on: Getting number of rows inserted for ON DUPLICATE KEY UPDATE multiple insert?

But these seem to fail for me on a basic test such as:

10 total rows, 2 inserts, 2 updates, 6 duplicates = 10 total rows, 6 mysql affected rows

(10*2 = 20) - 6 = 14 inserts (for the accepted answer)? or,

6 - 10 = -4 updates (equation 3 in second answer)?

And for the bonus points round, if it is impossible to correctly calculate the number of inserts, updates and duplicates only given these two figures (number of rows & affected rows), is there a better (performance-wise) way than simply querying the number of rows in the table before and after the "Insert on Duplicate Key Update" query?

(Please note that neither "Insert Ignore" nor "Replace Into" queries are suitable replacement queries to swap out for the "Insert on Duplicate Key Update" query in this particular instance.)

Cheers.

asked 53 secs ago

clawsout

1