MYSQL made the last record

In MySQL, using the id field of the auto_increment type as the primary key of a table and using it as a foreign key to other tables to form a "master-slave table structure" is a common usage in database design. However, in the specific generation of id, our order of operations is generally: insert records in the main table, and then get the automatically generated id, based on it to insert the record from the table. There is a difficulty here, is inserted into the main table record, how to get it corresponds to the id. The usual practice is through the "select max (id) from tablename" approach, but apparently this approach need to consider the situation of concurrency, the need for the main table in the transaction "X lock" until the value of the max (id) Later, unlock again. This approach requires more steps, some trouble, and concurrency is not good. Is there any easier way? One of the answers is through the select LAST_INSERT_ID () operation. At first glance, it looks like select max (id), but in fact it is thread-safe. In other words it is specific to the database connection. The following experiment shows:

1, insert a record in the connection to the A table, A table contains an auto_increment type of field.

2, in the connection 2 to A table and then insert a record.

3. Results: The result of executing select LAST_INSERT_ID () on connection 1 is different from the result of executing select LAST_INSERT_ID () on connection 2. The result of executing select max (id) on both connections is the same.
In fact, the difference between SCOPE_IDENTITY () and IDENT_CURRENT () in MSSQL is similar to here. Use SCOPE_IDENTITY () to get the value of the current session inserted into an IDENTITY field, and IDENT_CURRENT () to get the maximum value inserted on an IDENTITY field without distinguishing between different sessions.

Note: When using select last_insert_id () should pay attention to when inserting multiple records, just get the first insert id value, be sure to pay attention! Can try

insert into tb (c1, c2) values ​​(c1value, c2value), (c1value1, c2value2) ...