MySQL “INSERT … ON DUPLICATE KEY UPDATE” on Java:

问题:

I'm confused about the return value of MySQL's INSERT ... ON DUPLICATE KEY UPDATE statement. When I try it on a MySQL client (mysql terminal, phpmyadmin or MySQL Workbench), the execution results with one of the followings:

• 1: If new record inserted (i.e. no duplicate key).
• 2: If existing record updated in case of duplicate key.
• 0: If update executed but no column value was changed.

These results make sense. However, when I executed the same query in Java (with mysql-connector 5.1.34), JDBC's executeUpdate method returns 1 in case of both insertion and no change, and 2 in case of an successful update. It doesn't return 0 when update has no effect.

Is this a bug of MySQL JDBC driver? If so, is there a release which is free of this bug? If this is not a bug, how can I get the same result that is returned by a MySQL client?

回答1:

Seems you need to set useAffectedRows in driver properties manually.

I got the solution after some googling. You can refer this MySql Bug for this INSERT ON DUPLICATE KEY UPDATE return 0 if no change in JDBC ExecuteUpdate

Also in the discussion it is stated they released it in 5.1.7

EDIT For Your Second Question:

useAffectedRows:

Don't set the CLIENT_FOUND_ROWS flag when connecting to the server (not JDBC-compliant, will break most applications that rely on "found" rows vs. "affected rows" for DML statements), but does cause "correct" update counts from "INSERT ... ON DUPLICATE KEY UPDATE" statements to be returned by the server.

Default: false

Since version: 5.1.7

Resference useAffectedRows in JDBC Connector-j