How to Retrieve the Last Inserted ID in MySQL After Multi-Table Inserts?

Retrieving Last Inserted ID in MySQL for Multi-Table Inserts

When working with multiple database tables, it's often necessary to retrieve the last inserted ID after inserting into multiple tables. One common scenario involves retrieving the last inserted ID from the first table, even if subsequent insertions are made into other tables.

Consider the following SQL query:

INSERT INTO table1 (title,userid) VALUES ('test',1);
INSERT INTO table2 (parentid,otherid,userid) VALUES (LAST_INSERT_ID(), 4, 1);
SELECT LAST_INSERT_ID();

By default, LAST_INSERT_ID() will return the last inserted ID for the most recent table insertion, in this case table2. To retrieve the last inserted ID from table1, there are a few methods available:

Storing the Last Inserted ID in a Variable:

Insert a value into table1 and store the last inserted ID in a variable:

SET @last_id_in_table1 = LAST_INSERT_ID();

Subsequent insertions into other tables will not affect this variable, allowing you to reference the last ID inserted into table1 later.

Retrieving the Maximum ID:

An alternative approach is to find the maximum ID from table1 after insertions have been made:

SELECT MAX(id) FROM table1;

This will return the last inserted ID, even if other tables have been inserted into in the meantime.

Note: When retrieving the maximum ID, it's important to consider potential race conditions. If another process or connection inserts a value into table1 concurrently, the maximum ID retrieved may not accurately reflect the last inserted value.

The above is the detailed content of How to Retrieve the Last Inserted ID in MySQL After Multi-Table Inserts?. For more information, please follow other related articles on the PHP Chinese website!