MySql LAST_INSERT_ID [when inserting multiple data records]

LAST_INSERT_ID

Automatically return the first occurrence value set for the last INSERT or UPDATE operation as the AUTO_INCREMENT column. Refer to here

The ID that was generated is maintained in the server onPer-connection basis.

LAST_INSERT_ID is based on a single connection and cannot be changed by other client connections.

You can use SELECT LAST_INSERT_ID (); to query the value of LAST_INSERT_ID.

Important: If you insert multiple rows using a singleINSERTStatement,LAST_INSERT_ID()Returns the value generated forFirstInserted rowOnly.

INSERT multiple records using a single INSERT statement. LAST_INSERT_ID only returns the value of the first inserted record. For example:

1. Mysql> insert into t VALUES (NULL, 'aaa'), (NULL, 'bbbbb'), (NULL, 'cccc ');
2. Mysql> SELECT * FROM t;
3. + ---- + ------ +
4. | Id | name |
5. + ---- + ------ +
6. | 1 | Bob |
7. | 2 | aaaa |
8. | 3 | bbbb |
9. | 4 | cccc |
10. + ---- + ------ +
11. Mysql> SELECT LAST_INSERT_ID ();
12. + ------------------ +
13. | LAST_INSERT_ID () |
14. + ------------------ +
15. | 2 |
16. + ------------------ +

ID 2 is generated when the first record aaaa is inserted.

LAST_INSERT_ID is table-independent. If you insert data to table a and then insert data to table B, LAST_INSERT_ID will change.

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. This value cannot be affected by connections of other clients. This ensures that you can retrieve your own ID without worrying about the activity of other clients and do not need to lock the client.