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
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!