Last_insert_id usage of MySQL

Last_insert_id () automatically returns the value of the first table set in the auto_increment column in The Last insert or update query.

Notes for last_insert_id of MySQL:

FirstThe connection object used for query and insertion must be the same. Otherwise, the return value is unpredictable.

Mysql> select last_insert_id ();

-> 100

Using this function, the value returned to a given connection object is the first auto_increment value of the latest statement that affects the auto_increment column of the connection object. This value cannot be affected by other connection objects, that is, they generate their own auto_increment values.

SecondLast_insert_id is not related to table. If you insert data to table A and then insert data to table B, last_insert_id returns the id value in table B.

ThirdIf you use an insert statement to insert multiple rows, last_insert_id () Only returns the value generated when inserting the first row of data. This makes it easy to copy the same insert Statement on other servers.

Mysql> insert into T values

-> (Null, 'Mary '), (null, 'Jane'), (null, 'lisa ');

Mysql> select * from T;

| ID | Name |

+ -- +

| 1 | Bob |

| 2 | Mary |

| 3 | Jane |

| 4 | Lisa |

Mysql> select last_insert_id (); // This is the key issue to be explained.

| Last_insert_id () |

| 2 |

Although 3 new rows are inserted into t, the ID generated for the first row of these rows is 2, which is also the value returned by last_insert_id.

FourthIf you use insert ignore and the record is ignored, the auto_increment counter does not increment, and last_insert_id () returns 0, which indicates that no record is inserted.

Generally, you can obtain the ID of the inserted data by using select max (ID) from table. But in the case of multiple threads, it won't work. Max (ID) is obviously unavailable when multiple users insert data alternately. This should use last_insert_id, because last_insert_id is based on connection, as long as each thread uses an independent connection object, the last_insert_id function returns the ID of the first record generated by the connection for the latest insert or update operation on the auto_increment column. Last_insert_id is based on a single connection,
Cannot be changed by other client connections.