Get Autoincrement value after INSERT query in MySQL

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);
Code language: SQL (Structured Query Language) (sql)
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();
Code language: SQL (Structured Query Language) (sql)
Get our Articles via Email. Enter your email address.

You may also like...

33 Comments

  1. Steven says:

    doesnt sound very thread safe to me

  2. 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

  3. Akshay says:

    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?

  4. Viral says:

    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

    • ravi says:

      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.

      And this URL is not working http://kerneltrap.org/node/3096 please give me plantation

  5. web says:

    I hope this works

  6. pravin says:

    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.

  7. rahu says:

    very helpful tutorial which solved my sql trouble. thanks

  8. Carlo says:

    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

  9. Ram.. says:

    Hello Viral,

    Nice article i got the solution for my problem, thanks..

  10. Lenin says:

    HI, The correct function is mysql_insert_id(); not what you mentioned in this post….

  11. plr store says:

    i second that, mysql_insert_id(); works for me

  12. Neo says:

    SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = $dbName AND TABLE_NAME = $tblName

  13. WebDev says:

    Huh, it was quite a few Google attempts before I found this super useful PHP/MySQL tip!!!

  14. vicky says:

    Hi sir ,, M just Working on Gmail application project .. Where i used the j2ee application tool fror develop this app. so i used the Mysql dB server fr it. I hv a confusen that i want a Profile picture fr any particular user and it is retrived frm the db server wit all the data of user..
    so How can Retrive the image of user From Db when User logged into his account ….???

    • Bala says:

      You cannot store the image files in your database..instead you can store the url path of the images in the server and retrieve the url and put it into the src of the img tag. For example : say you have an image at www(dot)yourdomain(dot)com/images/myimag.jpg..you will store thi url into your database and fetch this url and put it into the img tage in the user profile page whenever this user logs in..

  15. Lokesh says:

    Very very good tip found today.. Will use in future, But i believe, this should be done only when you are using transactions. Otherwise its going to give some sleepless nights when debugging your application.

  16. Jamal Abdullah says:

    SELECT LAST_INSERT_ID(); doesn’t work. how can you specify which table?

  17. Michel says:

    Hi all,
    An official answer :
    For english people : http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html
    Pour les francophones : http://dev.mysql.com/doc/refman/5.0/fr/example-auto-increment.html.

    Michel

  18. navneet says:

    please tel me k kaise database ki query easil smj me aa skti h me bhul jati hu hmesha

  19. samiksha says:

    jab database m kisi row ko delete kar de toh uske corresponds autoincreament id bhi delete ho jati than uss hi id ko reuse next inserted value m kaise kare…
    plz help me to sort out that problem

  20. Fuad says:

    great , i was really looking for this..

  21. Dilukshan Mahendra says:

    Thank You Sooo Much, Seems it works fine for me.. Thanks again

  22. Heramb says:

    Thanks for help man..My error has been solve..:D

  23. Magnet man says:

    yea, it works fine with multi-connections

  24. Though already use this technique at many time. but thnx for the nice post. for novice it will help a lot.
    actually i was searching for a problem of autoincrement in oracle.

  25. Sushil says:

    Thanks a ton.

  26. jithin says:

    yea its works

  27. satbirkira says:

    The PHP function mysql_insert_id() returns the last insert’s auto increment value. Pretty useful function, naively I would have just done another query to get the ID, but I was expecting there was a SQL sub query could be used to return a value on a successful insert . Good thing I didn’t have to. Thanks everyone.

  28. sartaz says:

    hello dear h r u
    i am using jsp ,i want to insert data in stock with increment item value ,and decrement value when we have sale any item in jsp?

  29. thanks says:

    can i say some thing!

    thank you, you helped me by this post :)

  30. Shahrukh says:

    Could anyone tell How could I store or this last_insert_id() value as integer? and use it as integer for further use.

Leave a Reply

Your email address will not be published. Required fields are marked *