Equivalent to SQL Server function SCOPE_IDENTITY() in MySQL?

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

SELECT LAST_INSERT_ID().

This will return the ID of the last inserted record.

Here I will create a table with a primary key column. Below is a demonstration of last_insert_id().

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

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

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

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

Use the insert command to insert some records into the table. 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 the table "TestOnLastInsertIdDemo2". The query to create the 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 want to insert a record in the TestOnLastInsertIdDemo table, last_insert_id() returns 1. The query to insert records 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();

Here is the output:

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

In the above example output, it is giving 1 because last_insert_id() only uses the original table and not inside the trigger table.

The above is the detailed content of Equivalent to SQL Server function SCOPE_IDENTITY() in MySQL?. For more information, please follow other related articles on the PHP Chinese website!