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

mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 

Although the second INSERT  statement inserted three new rows into t , the ID generated for the first of these rows was 2 , and it is this value that is returned by LAST_INSERT_ID()  for the following SELECT statement. 

If you use INSERT IGNORE  and the row is ignored, the LAST_INSERT_ID()  remains unchanged from the current value (or 0 is returned if the connection has not yet performed a successful INSERT ) and, for non-transactional tables, the AUTO_INCREMENT  counter is not incremented. For InnoDB  tables, the AUTO_INCREMENT  counter is incremented if innodb_autoinc_lock_mode  is set to 1  or 2 , as demonstrated in the following example: 

mysql> USE test; mysql> SELECT @@innodb_autoinc_lock_mode; +----------------------------+ | @@innodb_autoinc_lock_mode | +----------------------------+ | 1 | +----------------------------+ mysql> CREATE TABLE ‘t‘ ( ‘id‘ INT(11) NOT NULL AUTO_INCREMENT, ‘val‘ INT(11) DEFAULT NULL, PRIMARY KEY (‘id‘), UNIQUE KEY ‘i1‘ (‘val‘) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; # Insert two rows mysql> INSERT INTO t (val) VALUES (1),(2); # With auto_increment_offset=1, the inserted rows # result in an AUTO_INCREMENT value of 3 mysql> SHOW CREATE TABLE t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE ‘t‘ ( ‘id‘ int(11) NOT NULL AUTO_INCREMENT, ‘val‘ int(11) DEFAULT NULL, PRIMARY KEY (‘id‘), UNIQUE KEY ‘i1‘ (‘val‘) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 # LAST_INSERT_ID() returns the first automatically generated # value that is successfully inserted for the AUTO_INCREMENT column mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ 

