What would be the effect on the output of MySQL LAST_INSERT_ID() function in the case on multiple-row insert?


As we know that MySQL LAST_INSERT_ID() function returns the latest generated sequence number but in case of multiple row-insert it would return the sequence number generated by the foremost inserted row.

Example

mysql> Insert into Student(Name) values('Ram'),('Mohan'),('Aryan');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0

The query above inserts three values in Student table with the help of Multiple-row insert query. The value of Column ‘Id’ can be checked with the help of the following query −

mysql> Select * from Student;

+----+-------+
| Id | Name  |
+----+-------+
| 1 | Raman  |
| 2 | Rahul  |
| 3 | Ram    |
| 4 | Mohan  |
| 5 | Aryan  |
+----+-------+

5 rows in set (0.00 sec)

It means that Last_Insert_Id() must return 5 as output but as we can see it returns the value 3 as follows −

mysql> Select Last_Insert_Id();

+------------------+
| Last_Insert_Id() |
+------------------+
| 3                |
+------------------+

1 row in set (0.00 sec)

It returns the value 3 because 3 is the value of foremost inserted row with above multiple row-insert query.

Updated on: 20-Jun-2020

149 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements