MYSQL Tutorial: getting ID_MySQL from the table by inserting records in the main table

MYSQL Tutorial: in MySQL, the ID field of the auto_increment type is used as the primary key of the table and as the foreign key of other tables, it forms a "master-slave table structure", which is a common usage in database design. However, when an id is generated, the operation sequence is generally: first insert a record in the master table, then obtain the automatically generated id, and insert records from the table based on it. The difficulty lies in how to obtain the corresponding id after inserting the primary table record. The general practice is to use the "select max (id) from tablename" method, but obviously this method needs to consider concurrency and "X lock" must be applied to the master table in the transaction. unlock after obtaining the value of max (id. This method requires a lot of steps, it is a little troublesome, and the concurrency is not good. Is there a simpler way? One of the answers is through the select LAST_INSERT_ID () operation. At first glance, it is very similar to select max (id), but in fact it is thread-safe. That is to say, it is specific to database connection. The following is an experiment description:

1. Insert A record to table a in connection 1. Table A contains an auto_increment field.

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

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; the results of executing select max (id) in the two connections are the same.
In MSSQL, the difference between SCOPE_IDENTITY () and IDENT_CURRENT () is similar. Use SCOPE_IDENTITY () to obtain the current session value of an IDENTITY field, and use IDENT_CURRENT () to obtain the maximum value inserted on an IDENTITY field without distinguishing sessions.

Note: When using select last_insert_id (), note that when multiple records are inserted at a time, only the id value inserted for the first time is obtained! Try it.

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