content_image/869253.jpg
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 is 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 statements coming after it do not see a changed value. 

The ID that was generated is maintained in the server on a per-connection basis . This means that the value returned by the function to a given client is the first AUTO_INCREMENT  value generated for most recent statement affecting an AUTO_INCREMENT  column by that client . This value cannot be affected by other clients, even if they generate AUTO_INCREMENT  values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions. 

The value of LAST_INSERT_ID()  is not changed if you set the AUTO_INCREMENT  column of a row to a non-“magic”  value (that is, a value that is not NULL  and not 0 ). 

<img src='content_image/869253.jpg'>

For example: 

mysql> USE test; mysql> CREATE TABLE t ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL ); mysql> INSERT INTO t VALUES (NULL, ’Bob’); mysql> SELECT * FROM t; +----+------+ | id | name | +----+------+ | 1 | Bob | +----+------+ mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ mysql> INSERT INTO t VALUES (NULL, ’Mary’), (NULL, ’Jane’), (NULL, ’Lisa’); mysql> SELECT * FROM t; +----+------+ 

## Important 

If you insert multiple rows using a single INSERT  statement, LAST_INSERT_ID()  returns the value generated for the first  inserted row only . The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server. 

