| 1 | +------------------+ # The attempted insertion of duplicate rows fail but errors are ignored 

mysql> INSERT IGNORE INTO t (val) VALUES (1),(2); Query OK, 0 rows affected (0.00 sec) Records: 2 Duplicates: 2 Warnings: 0 # With innodb_autoinc_lock_mode=1, the AUTO_INCREMENT counter # is incremented for the ignored rows 

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=5 DEFAULT CHARSET=latin1 # The LAST_INSERT_ID is unchanged because the previous insert was unsuccessful 

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

For more information, see Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB” . 

If expr  is given as an argument to LAST_INSERT_ID() , the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID() . This can be used to simulate sequences: 

1. Create a table to hold the sequence counter and initialize it: 

mysql> CREATE TABLE sequence (id INT NOT NULL); mysql> INSERT INTO sequence VALUES (0); 

2. Use the table to generate sequence numbers like this: 

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1); mysql> SELECT LAST_INSERT_ID(); 

The UPDATE  statement increments the sequence counter and causes the next call to LAST_INSERT_ID()  to return the updated value. The SELECT  statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See mysql_insert_id(). 

You can generate sequences without calling LAST_INSERT_ID() , but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the UPDATE  statement and get their own sequence value with the SELECT  statement (or mysql_insert_id() ), without affecting or being affected by other clients that generate their own sequence values. 

Note that mysql_insert_id()  is only updated after INSERT  and UPDATE  statements, so you cannot use the C API function to retrieve the value for LAST_INSERT_ID( expr )  after executing other SQL statements like SELECT  or SET . 

