MySQL Tutorial: Insert record in primary table get ID from table

In MySQL, the Auto_increment type ID field is used as the primary key of the table, and it forms a "master-slave table structure" as a foreign key to other tables, which is a common usage in database design. But when it comes to generating IDs, we typically do this by inserting a record in the primary table and then getting the automatically generated ID to insert the record from the table based on it. One difficulty here is how to get the ID of the primary table after it's been recorded. The usual practice is through the "SELECT Max (ID) from tablename" approach, but it is obvious that this approach requires consideration of concurrency, the need to "X-lock" the primary table in a transaction, and then unlock the value of Max (ID) after it is obtained. This approach requires a lot of steps, some trouble, and the concurrency is not good. Is there any simpler way to do it? One answer is through the Select last_insert_id () operation. At first glance, it looks like the Select Max (ID), but it's actually thread-safe. This means that it is specific to the database connection. The following experiment shows:

1. Insert a record in connection 1 to table A, and a table contains a field of type Auto_increment.

2. Insert a record in connection 2 to table A.

3. Result: The result of executing select last_insert_id () in connection 1 is different from the result of executing select last_insert_id () in connection 2, and the result of executing select MAX (ID) in two connections is the same.
In fact, the difference between scope_identity () and Ident_current () in MSSQL is similar here. Using Scope_identity () allows you to get the value of the current session in which an identity field is inserted, and using Ident_current () obtains the maximum value inserted on an identity field without distinguishing between different sessions.

Note: When using select LAST_INSERT_ID (), be aware that when you insert multiple records at one time, you only get the ID value that you inserted the first time, be sure! You can try.

INSERT into TB (C1,C2) VALUES (C1value,c2value), (c1value1,c2value2) ...