CODEWITHSUNDEEP
mysql
antak
antak

Reputation: 20759

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

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:

Consequently:

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.

Upvotes: 6

Views: 731

Answers (1)

Solarflare
Solarflare

Reputation: 11106

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.

Upvotes: 2

Related Questions