What\'s the MySQL Equivalent of SQL Server\'s SCOPE_IDENTITY()?

MySQL Equivalent of SQL Server's SCOPE_IDENTITY()

When working with auto-incrementing columns in MySQL, you may encounter a need to retrieve the ID of the last inserted record, analogous to the SCOPE_IDENTITY() function in SQL Server. This article explores the equivalent in MySQL and provides detailed examples for its usage.

Equivalent of SCOPE_IDENTITY() in MySQL

The equivalent function in MySQL is LAST_INSERT_ID(). This function returns the last auto-incrementally generated value for the most recent insert operation executed within the current connection.

Usage Example

Consider the following MySQL code:

INSERT INTO Customers (Name, Email) VALUES ('John Doe', 'john.doe@example.com');
SELECT LAST_INSERT_ID();

The first statement inserts a new record into the Customers table and assigns an auto-incrementing ID to the newly added row. The second statement retrieves the ID of the last inserted record using the LAST_INSERT_ID() function.

Additional Notes

• LAST_INSERT_ID() returns the ID of the last inserted row within the current connection, regardless of the table being inserted into.
• If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() will return the ID of the first row added.
• Be aware that if you use a trigger to insert rows, the LAST_INSERT_ID() function will return the ID of the last inserted row in the trigger's context, not necessarily the row in the original table.

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