Home > Database > Mysql Tutorial > How to Retrieve the Last Inserted Auto-Increment ID in MySQL?

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

DDD
Release: 2024-12-09 09:50:07
Original
217 people have browsed it

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

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

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

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!

source:php.cn
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