MySQL Equivalent of SQLServer's SCOPE_IDENTITY()

In MySQL, the equivalent function to SCOPE_IDENTITY() in SQLServer is LAST_INSERT_ID(). This function returns the generated ID of the last inserted row in the current session.

How to Use LAST_INSERT_ID()

LAST_INSERT_ID() can be used in various scenarios to retrieve the ID of a newly inserted record. For instance, consider the following code:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255)
);

INSERT INTO users (name) VALUES ('John Doe');

SELECT LAST_INSERT_ID();

In this example, the LAST_INSERT_ID() function returns the ID of the newly inserted user, which is 1 in this case.

Behavior Within Triggers

It's important to note that LAST_INSERT_ID() operates within the scope of the current session. If you use it within a trigger, it will return the ID of the last inserted row in the table that the trigger is attached to, not in the table that triggered the insertion.