Syntax:

LAST_INSERT_ID(), LAST_INSERT_ID(expr)

Description:

For MySQL 5.1.12 and later, 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.

For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

MariaDB [test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                9 |
+------------------+
1 row in set (0.00 sec)

MariaDB [test]>

In MySQL 5.1.11 and earlier, LAST_INSERT_ID() (no arguments) returns the first automatically generated value if any rows were successfully inserted or updated. This means that the returned value could be a value that was not successfully inserted into the table. 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.

if a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE updates (rather than inserts) a row, the value of LAST_INSERT_ID() is not meaningful prior to MySQL 5.1.12. For a workaround, see http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html.

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:

  • If a stored procedure executes statements that change the value of LAST_INSERT_ID(), the changed value will be seen by statements that follow the procedure call.
  • For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements will not see a changed value.

Comments

Comments loading...