The equivalent of SQL Server function SCOPE_IDENTITY() in MySQL?

The equivalent of SQL Server function SCOPE_IDENTITY() is equal to LAST_INSERT_ID() in MySQL. The syntax is as follows:

SELECT LAST_INSERT_ID().

This returns the id of last inserted record.

Here, I am going to create a table with primary key column. The following is the demo of last_insert_id().

First, let us create two tables. The query to create the first table table is as follows:

<TestOnLastInsertIdDemo>

mysql> create table TestOnLastInsertIdDemo
   -> (
   -> StudentId int NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY(StudentId)
   -> );
Query OK, 0 rows affected (0.95 sec)

Now creating the second table. The query is as follows:

<TestOnLastInsertIdDemo2>

mysql> create table TestOnLastInsertIdDemo2
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (2.79 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into TestOnLastInsertIdDemo2 values(),(),(),(),(),(),(),();
Query OK, 8 rows affected (0.21 sec)
Records: 8 Duplicates: 0 Warnings: 0

Now create a trigger on table ‘TestOnLastInsertIdDemo2’. The query to create a table is as follows:

mysql> delimiter //
mysql> create trigger insertingTrigger after insert on TestOnLastInsertIdDemo
   -> for each row begin
   -> insert into TestOnLastInsertIdDemo2 values();
   -> end;
   -> //
Query OK, 0 rows affected (0.19 sec)
mysql> delimiter ;

If you will insert the record in the TestOnLastInsertIdDemo table, the last_insert_id() returns 1. The query to insert record is as follows:

mysql> insert into TestOnLastInsertIdDemo values();
Query OK, 1 row affected (0.31 sec)

Use the function last_insert_id(). The query is as follows:

mysql> select last_insert_id();

The following is the output:

+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

In the above sample output, it gives 1 because the last_insert_id() uses the original table only, not inside trigger table.