LAST_INSERT_ID(), LAST_INSERT_ID(expr)
LAST_INSERT_ID() (no arguments) returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.
If one gives an argument to LAST_INSERT_ID(), then it will return the value of the expression and the next call to LAST_INSERT_ID() will return the same value. The value will also be sent to the client and can be accessed by the mysql_insert_id function.
For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:
SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 9 | +------------------+
You can also use LAST_INSERT_ID() to delete the last inserted row:
DELETE FROM product WHERE id = LAST_INSERT_ID();
If no rows were successfully inserted, LAST_INSERT_ID() returns 0.
The value of LAST_INSERT_ID() will be consistent across all versions if all rows in the INSERT or UPDATE statement were successful.
The currently executing statement does not affect the value of LAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.)
If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined. For transactional tables, if the statement is rolled back due to an error, the value of LAST_INSERT_ID() is left undefined. For manual ROLLBACK, the value of LAST_INSERT_ID() is not restored to that before the transaction; it remains as it was at the point of the ROLLBACK.
Within the body of a stored routine (procedure or function) or a trigger, the value of LAST_INSERT_ID() changes the same way as for statements executed outside the body of these kinds of objects. The effect of a stored routine or trigger upon the value of LAST_INSERT_ID() that is seen by following statements depends on the kind of routine:
CREATE TABLE t ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, f VARCHAR(1)) ENGINE = InnoDB; INSERT INTO t(f) VALUES('a'); SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ INSERT INTO t(f) VALUES('b'); INSERT INTO t(f) VALUES('c'); SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 3 | +------------------+ INSERT INTO t(f) VALUES('d'),('e'); SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 4 | +------------------+ SELECT * FROM t; +----+------+ | id | f | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ SELECT LAST_INSERT_ID(12); +--------------------+ | LAST_INSERT_ID(12) | +--------------------+ | 12 | +--------------------+ SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 12 | +------------------+ INSERT INTO t(f) VALUES('f'); SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 6 | +------------------+ SELECT * FROM t; +----+------+ | id | f | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | | 6 | f | +----+------+ SELECT LAST_INSERT_ID(12); +--------------------+ | LAST_INSERT_ID(12) | +--------------------+ | 12 | +--------------------+ INSERT INTO t(f) VALUES('g'); SELECT * FROM t; +----+------+ | id | f | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | | 6 | f | | 7 | g | +----+------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/last_insert_id/