How to detect inserted ids on "insert ignore" statement?

Question

Now,do nothing when "on duplicate key update". So,

INSERT IGNORE INTO t1 c1 VALUES(...,...,...);

The last_insert_id is only one id at most.

Then how to detect multi inserted ids on "insert ignore" statement?

Answer 1

Using LAST_INSERT_ID()

There is no way that I'm aware of that a regular bulk INSERT can return more than one LAST_INSERT_ID(). It will always return the ID of the first record inserted for that bulk INSERT statement.

You can view the documentation on it which explains that here

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

Emphasis on the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column. So as described the first successful row inserted via a bulk INSERT will be the return value of LAST_INSERT_ID().

Using LAST_INSERT_ID() and ROW_COUNT() with InnoDB

There is an alternate solution as posted by Dag Sondre Hansen here which may suit your needs. It does presume your tables/database use InnoDB though.

If you are using InnoDB on a recent version of MySQL, you can get the list of IDs using LAST_INSERT_ID() and ROW_COUNT().

InnoDB guarantees sequential numbers for AUTO INCREMENT when doing bulk inserts, provided innodb_autoinc_lock_mode is set to 0 (traditional) or 1 (consecutive). Consequently you can get the first ID from LAST_INSERT_ID() and the last by adding ROW_COUNT()-1.

Just make sure you do this on the same connection, directly after your INSERT statement or you won't get the results you're expecting.