Getting the "Records" and "Duplicates" counts of INSERT ... SELECT ... ON DUPLICATE KEY UPDATE

Question

The INSERT ... SELECT... ON DUPLICATE KEY UPDATE returns as affected-rows a number derived from (inserted count) + (updated count) * 2, and this is well documented in multiple places.

However in the output of the MySQL Command-Line Tool, I've noticed this extra info:

> INSERT INTO ...
-> SELECT ... FROM ...
-> ON DUPLICATE KEY UPDATE ...
-> ;
Query OK, 97 rows affected (0.03 sec)
Records: 2425  Duplicates: 28  Warnings: 0

Namely, the numbers Records: and Duplicates:.

Analyzing have determined:

• The 97 rows affected is affected-rows (a.k.a. ROW_COUNT()).
• Records: 2425 is the number of rows fetched by the SELECT part.
• Duplicates: 28 is the number of rows actually changed by the ON DUPLICATE KEY UPDATE part.

Consequently:

• affected-rows - Duplicates * 2 is the number of rows actually inserted.
• Records - affected-rows - Duplicates is the number of rows duplicated but not changed (i.e. values were set to the same value).

Which brings us to the question: How does one obtain these numbers Records and Duplicates in a program? (I'm using MySQL Connector/J if that helps answer the question.)

Possibly for Records:, issuing a SELECT on FOUND_ROWS() directly after the INSERT ... SELECT ... ON DUPLICATE KEY UPDATE is one way.

I have no idea where Duplicates: comes from.

Answer 1

The C api does not provide direct access to these values (or the underlying information to calculate these values) as numbers, as it does with mysql_affected_rows().

You have however access to that message using mysql_info():

mysql_info()

const char *mysql_info(MYSQL *mysql)

Description

Retrieves a string providing information about the most recently executed statement, but only for the statements listed here. For other statements, mysql_info() returns NULL. The format of the string varies depending on the type of statement, as described here. The numbers are illustrative only; the string contains values appropriate for the statement.

• INSERT INTO ... SELECT ...

  String format: Records: 100 Duplicates: 0 Warnings: 0

• [...]

• UPDATE

  String format: Rows matched: 40 Changed: 40 Warnings: 0

Return Values

A character string representing additional information about the most recently executed statement. NULL if no information is available for the statement.

You can/have to parse these (query dependent) strings if you need access to those values in detail. The mysql client simply displays this message as it is.

Unfortunately, not every api, including the MySQL Connector/J, implements or relays this feature, so those detailed values seem to not be accessable here.