Home > Database > Mysql Tutorial > Equivalent to SQL Server function SCOPE_IDENTITY() in MySQL?

Equivalent to SQL Server function SCOPE_IDENTITY() in MySQL?

PHPz
Release: 2023-09-10 15:49:02
forward
1380 people have browsed it

相当于 MySQL 中的 SQL Server 函数 SCOPE_IDENTITY()?

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

SELECT LAST_INSERT_ID().
Copy after login

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)
Copy after login

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)
Copy after login

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
Copy after login

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 ;
Copy after login

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)
Copy after login

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

mysql> select last_insert_id();
Copy after login

Here is the output:

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

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template