mysql+update多次,MySQL重复键更新+受影响的行数

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)?

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 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)?

(Also 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.)

解决方案

I think it's impossible without more information - not enough with just the count of rows you're about to insert and the affected rows.

mysqli->info returns a helpful string that also provides a duplicate count - we can work out the rest from this.

list($rec, $dupes, $warns) = sscanf($mysqli->info, "Records: %d Duplicates: %d Warnings: %d"); // courtesy of user at big lake dot com - php.net

$inserts = $total_rows_affected - ($dupes * 2);

$updates = ($total_rows_affected - $inserts)/2;

$skipped = $rec - ($inserts + $updates);

$total = $rec;