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)

View Comments

  • 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?

    • 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

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

  • 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

Leave a Comment
Share
Published by
Viral Patel
Tags: How-To MySQL Tutorial
16 years ago

Recent Posts

  • Java

Java URL Encoder/Decoder Example

Java URL Encoder/Decoder Example - In this tutorial we will see how to URL encode/decode…

5 years ago
  • General

How to Show Multiple Examples in OpenAPI Spec

Show Multiple Examples in OpenAPI - OpenAPI (aka Swagger) Specifications has become a defecto standard…

5 years ago
  • General

How to Run Local WordPress using Docker

Local WordPress using Docker - Running a local WordPress development environment is crucial for testing…

5 years ago
  • Java

Create and Validate JWT Token in Java using JJWT

1. JWT Token Overview JSON Web Token (JWT) is an open standard defines a compact…

5 years ago
  • Spring Boot

Spring Boot GraphQL Subscription Realtime API

GraphQL Subscription provides a great way of building real-time API. In this tutorial we will…

5 years ago
  • Spring Boot

Spring Boot DynamoDB Integration Test using Testcontainers

1. Overview Spring Boot Webflux DynamoDB Integration tests - In this tutorial we will see…

5 years ago