INSERT IGNORE - How can I tell if inserted?

Question

I am going to need an SQL query that would INSERT .. ON DUPLICATE KEY UPDATE (or INSERT IGNORE INTO) my table.

I need to know if an update actually took place or rather a new row was inserted.

A good reference was this SO answer, unfortunately there's no answer to the action taken.

As for now I am using INSERT .. ON DUPLICATE KEY UPDATE:

INSERT INTO `tbl` (`CLIENT_ID`, `COMP_ID`, `DATETIME`) VALUES (12334,32,NOW())
                    ON DUPLICATE KEY UPDATE
                    `COMP_ID`=VALUES(`COMP_ID`), `DATETIME`=VALUES(`DATETIME`);";

and check the affected_rows value.
If affected_rows equals 1, means -> new row inserted
If affected_rows equals 2, means -> row updated

I'd like no change to happen in case of a duplicate, but I would like to know a duplicate exists.

Using MYSQL

Answer 1

Inspect the number of rows affected:

• A return value of 1 means an INSERT occurred
• A return value of 2 means an UPDATE occurred

If you're using JDBC, this is the int return value of the update() or execute() call.

All frameworks have a way of passing this value back to you, for example Hibernate's Query.executeUpdate() returns this int value.