How to Retrieve the Last Inserted Auto-Increment ID in MySQL?

Finding the Equivalent of SQL Server's SCOPE_IDENTITY() in MySQL

The SQL Server function SCOPE_IDENTITY() provides a way to retrieve the last identity value generated by an INSERT statement within the current scope. Its equivalent in MySQL is LAST_INSERT_ID().

LAST_INSERT_ID()

The LAST_INSERT_ID() function returns the ID generated by the most recent INSERT operation that inserted a row into a table with an AUTO_INCREMENT column. Its syntax is:

LAST_INSERT_ID([table_name])

The optional table_name parameter specifies the table from which to retrieve the last identity value. If omitted, LAST_INSERT_ID() returns the value for the last table that had an AUTO_INCREMENT column inserted into.

Example

Consider the following table:

CREATE TABLE Foo (
    FooId INT AUTO_INCREMENT PRIMARY KEY
);

Inserting a row into this table using an INSERT statement will generate an ID for the new record. The LAST_INSERT_ID() function can be used to retrieve this ID immediately after the INSERT operation, as shown here:

INSERT INTO Foo () VALUES ();
SELECT LAST_INSERT_ID();

This will return the ID of the newly created record.

Additional Notes

• LAST_INSERT_ID() retrieves the ID of the current session only. It does not return IDs generated in other sessions.
• When inserting multiple rows using a single INSERT statement within a transaction, LAST_INSERT_ID() returns the ID of the first row that was inserted.
• If an INSERT statement does not generate an identity value, LAST_INSERT_ID() returns 0.

The above is the detailed content of How to Retrieve the Last Inserted Auto-Increment ID in MySQL?. For more information, please follow other related articles on the PHP Chinese website!