mysql - 为什么受影响的行需要时间来更新

Question

MySQL documentation状态:

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

但是，在使用它时，我得到了不同的结果。对于相同的多个主唯一键，需要进行 5 或 6 次更新才能考虑更新而不是新插入。我首先想到可能是nodejs带有mysql哪个包会被窃听？

但是，我注意到通过 mysql shell，我得到了相同类型的错误:

INSERT INTO table SET `master_id` = 119, `slave_id` = 1221829, 
`date` = '2014-09-18', `type` = '42', `threshold` = '18', 
`value` = 18.3536, `ratio` = '1.96'
ON DUPLICATE KEY UPDATE value = (value + VALUES(value)) / 2, 
ratio = (ratio + VALUES(ratio)) / 2;
Query OK, 1 row affected (0.00 sec)

INSERT INTO alerts SET `master_id` = 119, `slave_id` = 1221829, 
`date` = '2014-09-18', `type` = '42', `threshold` = '18', 
`value` = 18.3536, `ratio` = '1.96'
ON DUPLICATE KEY UPDATE value = (value + VALUES(value)) / 2, 
ratio = (ratio + VALUES(ratio)) / 2;
Query OK, 0 rows affected (0.00 sec)

INSERT INTO alerts SET `master_id` = 119, `slave_id` = 1221829, 
`date` = '2014-09-18', `type` = '42', `threshold` = '18', 
`value` = 18.3536, `ratio` = '1.96'
ON DUPLICATE KEY UPDATE value = (value + VALUES(value)) / 2, 
ratio = (ratio + VALUES(ratio)) / 2;
Query OK, 2 rows affected (0.00 sec)

是否是因为不建议对多个主键使用 ON DUPLICATE KEY，如下所述？

In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

或者是因为它们的值相同？还是因为两次插入之间的间隔太短(~ 10 秒)？

我的表有以下主键:{master_id，slave_id，date，type}

实在是没看懂……

Answer 1

您得到 0 个受影响的行，因为 UPDATE 语句不会更改值。

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.