In MySQL, the LAST_INSERT_ID()
function returns the first automatically generated value successfully inserted for an AUTO_INCREMENT
column as a result of the most recently executed INSERT
statement.
It can also be called with an argument, in which case, it returns the value of the expression and the next call to LAST_INSERT_ID()
will return the same value.
Syntax
The function can be called in the following ways:
LAST_INSERT_ID()
LAST_INSERT_ID(expr)
Where expr
is returned, and the next call to LAST_INSERT_ID()
will return the same value.
Example
As an example, let’s create a table with an AUTO_INCREMENT
column:
CREATE TABLE idiots (
idiot_id INT NOT NULL AUTO_INCREMENT,
idiot_name VARCHAR(255) NOT NULL,
PRIMARY KEY (idiot_id)
);
The idiot_id
column uses AUTO_INCREMENT
for its value.
Now insert some rows:
INSERT INTO idiots (idiot_name) VALUES ('Dumb');
INSERT INTO idiots (idiot_name) VALUES ('Dumber');
INSERT INTO idiots (idiot_name) VALUES ('Dumbest');
Now let’s run LAST_INSERT_ID()
:
SELECT LAST_INSERT_ID();
Result:
+------------------+ | LAST_INSERT_ID() | +------------------+ | 3 | +------------------+
It returns 3
.
Now, let’s return all rows in the table to verify that the last row has an AUTO_INCREMENT
value of 3
inserted:
SELECT * FROM idiots;
Result:
+----------+------------+ | idiot_id | idiot_name | +----------+------------+ | 1 | Dumb | | 2 | Dumber | | 3 | Dumbest | +----------+------------+
As expected, the idiot_id
column goes up to 3
.
Including an Argument
As mentioned, if called with an argument, the function returns the value of the expression and the next call to LAST_INSERT_ID()
will return the same value.
SELECT LAST_INSERT_ID( 22 );
Result:
+----------------------+ | LAST_INSERT_ID( 22 ) | +----------------------+ | 22 | +----------------------+
Now call it again, but with no argument:
SELECT LAST_INSERT_ID();
Result:
+------------------+ | LAST_INSERT_ID() | +------------------+ | 22 | +------------------+
It still returns a value of 22
.
However, if we continue to insert values into our idiots
table, the AUTO_INCREMENT
will continue where it left off on that table:
INSERT INTO idiots (idiot_name) VALUES ('Homer');
Now let’s run LAST_INSERT_ID()
:
SELECT LAST_INSERT_ID();
Result:
+------------------+ | LAST_INSERT_ID() | +------------------+ | 4 | +------------------+
It returns 4
.
And here’s what the table looks like now:
SELECT * FROM idiots;
Result:
+----------+------------+ | idiot_id | idiot_name | +----------+------------+ | 1 | Dumb | | 2 | Dumber | | 3 | Dumbest | | 4 | Homer | +----------+------------+
For more information on this function, see the MySQL documentation.