Bug #62662 how to retrieve multiple insert IDs

• Submitted: 9 Oct 2011 21:27
• Modified: 26 Dec 2012 8:15
• Reporter: Don Cohen
• Status: Not a Bug
• Category: MySQL Server
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[9 Oct 2011 21:27] Don Cohen

Description:
After statements that do multiple inserts, last_insert_id is documented as returning the first (lowest) inserted ID.  Is there any way to find out all of the inserted IDs?  For instance, if you know you did 20 inserts, is it guaranteed that the inserted IDs are last_insert_id, ... last_insert_id+19 ?
I've not seen such a statement in the doc.
If that is the case, how can one find out how many inserts the last statement did? 
In any case there ought to be a way to find out, so either tell me how to do so or consider this to be a feature request that such a thing be supplied.

How to repeat:
oh, come on!

[26 Dec 2012 8:15] Umesh Shastry

Hello Don Cohen,

>>If that is the case, how can one find out how many inserts the last statement did? 
>>In any case there ought to be a way to find out, so either tell me how to do so or consider this to be a feature request that such a thing be supplied.

You can make use of ROW_COUNT() function which is similar to the value from the mysql_affected_rows() C API function and the row count that the mysql client 
displays following statement execution. Please see, http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count

For example:

mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)