MySQL – Get Latest Identity Value by Inserts

In SQL Server, whenever you add data to a table that has an identity column, you can get the lastly generated identity value using @@identity or scope_identity(). Similarly, in MySQL you can make use of LAST_INSERT_ID () function

Let us create the following dataset

Create table test(id int AUTO_INCREMENT NOT NULL,name varchar(100));
INSERT INTO test(name) select 'MySQL' union all
INSERT INTO test(name) select 'SQL Server' union all
INSERT INTO test(name) select 'Oracle' ;

After executing the above code, if you want to know the lastly generated identity value, use the following code

SELECT LAST_INSERT_ID() AS id_value;

The result is 3

Note that this will work only in the current session and it is advisable to use the code immediately after the INSERT statement. If you want to make use of the value at a later point within the block of code, you can assign it to a variable.

SET @id_val:=(SELECT LAST_INSERT_ID());

Now you can use the value of @id_val at the later point within a block of code or a procedure.

Reference : Pinal Dave (https://blog.sqlauthority.com)