Get Autoincrement value after INSERT query in MySQL
- By Viral Patel on December 2, 2008
Lot of time we have requirement to update two tables simultaneously. Say for example, we have two tables CUSTOMER_DETAILS and CUSTOMER_ADDRESS. While adding a row in CUSTOMER_DETAILS, few details like address is first written in CUSTOMER_ADDRESS and its reference is added in CUSTOMER_DETAILS table. Now if ADD_ID is a primary key in CUSTOMER_ADDRESS table and if it is an auto increment field than how to add this key as a foreign key in CUSTOMER_DETAILS table?
Well, check following queries:
INSERT INTO CUSTOMER_ADDRESS (ADD_ID, ADD_TEXT) VALUES(NULL, 'some address value');
INSERT INTO CUSTOMER_DETAILS (NAME, ADD_ID, GENDER, PHONE_NO)
VALUES ('James Bond', LAST_INSERT_ID(), 'MALE', 007);
Now when the first query will get executed, address details will be added in CUSTOMER_ADDRESS table and the ADD_ID will be updated based on autoincrement field as we passed NULL in its place. Just after the completion of first query, we want to add custormer details in CUSTOMER_DETAILS table where we will need the ADD_ID that we just added in CUSTOMER_ADDRESS. Now note that we have used LAST_INSERT_ID() function to retrieve the latest autoincrement ID that was used in CUSTOMER_ADDRESS table.
Thus, LAST_INSERT_ID() can be used to fetch latest autoincrement id being used in any INSERT query that was just got executed.
Also, you can fetch the recently added autoincrement ID by using following SELECT query:
SELECT LAST_INSERT_ID();
Get our Articles via Email. Enter your email address.
doesnt sound very thread safe to me
Hi Steven,
LAST_INSERT_ID() is unique per session. The initial value will be 0. If multiple threads are using same connection then there may arise some concurrency issues. But for different sessions it will be fine.
Read TIP 9: at http://kerneltrap.org/node/3096
Hi,
Thanks for the tip but i’ve got 1 question:
In a multi-connection situation, wouldn’t the last_insert_id() changed ,’incremented’, BEFORE the next “insert ” query is executed?
As such foreign key will be wrong for the customer details.
Am i wrong?
Hi Akshay,
As I have mentioned in the above mail, LAST_INSERT_ID() is unique to the login session. Go through the link: http://kerneltrap.org/node/3096
I hope this works
viral bhai,
hu drupal par work karu chhu.mare ek field autoincrement karvi chhe runtime.
means jyare hu data enter karu ane database maa feed karu to next time unique field autoincrement thavi joye.
very helpful tutorial which solved my sql trouble. thanks
Hi Viral, maybe is a dumb question, but what do you mean with session?
I have a conn.php and all my php pages connect to the database through this connector.
But what happened if I make an insert and immediately I use the LAST_INSERT_ID but hundreds or thousands of users are inserting rows in this same millisecond?
I want to do something like this but I don’t know is safe:
$insert1 = “INSERT INTO table1 (id_table1, description) VAUES (”,’$var_here’)”
$insert2 = “INSERT INTO table2 (id_table2, id_table1) VALUES (”,LAST_INSERT_ID)”
Thanks in advance
Hello Viral,
Nice article i got the solution for my problem, thanks..
HI, The correct function is mysql_insert_id(); not what you mentioned in this post….
i second that, mysql_insert_id(); works for me
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = $dbName AND TABLE_NAME = $tblName