MySQL重复键更新+受影响的行数 [英] MySQL on duplicate key update + affected row count

问题描述

使用以下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)  

鉴于mySQL报告:

• 每个插入影响1行
• 每次更新影响2行
• 每个重复项影响0行(而info()看似不正确(?)始终报告0个重复项，无论跳过了多少个重复项)

我仅有的数字是:

• 我尝试插入/更新的总行数(可以是47、163、282之类的任何数字-每次都不是固定的行数)
• 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?

请注意，我已经尝试了接受的答案和列在以下列表中的其他方程式:获取为ON DUPLICATE KEY UPDATE多次插入而插入的行数?

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?

但是这些似乎在基本测试中对我来说是失败的，例如:
共10行，2次插入，2次更新，6次重复= 10行，6条受mysql影响的行
(10 * 2 = 20)-6 = 14次插入(对于已接受的答案)?或，
6-10 = -4更新(第二个答案中的等式3)?

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

(还请注意，在此特定情况下，插入忽略"查询和替换为"查询都不适合替换为在重复密钥更新中插入"查询.)

推荐答案

我认为没有更多信息就不可能-仅要插入的行数和受影响的行还不够.

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返回一个有用的字符串，该字符串也提供重复计数-我们可以从中得出其余的数字.

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;

这篇关于MySQL重复键更新+受影响的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！