问题:

Is it possible to get the duplicate count when executing MySQL "INSERT IGNORE" statement via JDBC?

For example, when I execute an INSERT IGNORE statement on the mysql command line, and there are duplicates I get something like

Query OK, 0 rows affected (0.02 sec) Records: 1 Duplicates: 1 Warnings: 0

Note where it says "Duplicates: 1", indicating that there were duplicates that were ignored.

Is it possible to get the same information when executing the query via JDBC?

Thanks.

回答1:

I believe you can retrieve this by issuing SHOW WARNINGS after your insert.

http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html

回答2:

You can get the rows affected value via JDBC. Just subtract it from the number of rows you inserted.

Bear in mind that rows affected also includes rows that are indirectly affected by the query. So, if there are any triggers that affect other rows, rows affected will include those rows as well.

See: http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#executeUpdate()

回答3:

You can use the ROW_COUNT() and FOUND_ROWS() functions to determine the number of inserted rows and duplicates when doing INSERT IGNORE.

For Example :

SELECT ROW_COUNT(), FOUND_ROWS()
    INTO myRowCount, myFoundRows;
myInsertedRows = myRowCount;
myDuplicateRows =  myFoundRows - myInsertedRows;
COMMIT;