How to Find Last Inserted ID From Tables in MySQL

1. What Is the AUTO_INCREMENT ID in MySQL
2. Find the Last Inserted ID of AUTO_INCREMENT Column in MySQL

Many articles about finding the last inserted ID of one table in MySQL. However, it is hard to find any tutorial regarding finding the last inserted ID with two tables.

In this article, we will try to address this special case, and of course, we will also explore when we are using the LAST_INSERT_ID() command. First, let’s see what AUTO_INCREMENT is ID and its relation to LAST_INSERT_ID().

What Is the AUTO_INCREMENT ID in MySQL

When dealing with databases, we usually need ID for the specific tables to use them as the primary key. Because a primary key identifies a record in a database, it must be unique.

However, how can we guarantee that the main key is always unique? One alternative option is to construct the main key using a formula that verifies the key’s presence in the database before adding data.

This strategy may work, but it is difficult and not infallible. We may utilize MySQL’s AUTO_INCREMENT() function to create primary keys to avoid this complication and assure that they are constantly unique.

With the INT data type, auto-increment is employed. Both signed and unsigned the INT data type supports values.

Only a positive number can be stored in unsigned data types. When a new record is entered into a database, auto-increment allows a unique number to be created automatically.

Let’s see an example.

CREATE TABLE cars (
     carID INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (carID)
);

After we have created the table now, we can insert values. We also don’t need to specify a value for the carID column.

INSERT INTO cars (name)
VALUES ('BMW'),('Mercedes'),('Audi');
SELECT * FROM cars;

Output:

+----+---------+
| carID | name |
+----+---------+
|  1 | BMW     |
|  2 | Mercedes|
|  3 | Audi    |
+----+---------+

It will take 1 as a starting value by default, incrementing by 1. We can alter the new value table if we need to start from another value.

ALTER TABLE cars AUTO_INCREMENT=32;

Lastly, we need to be careful when we define the data type for auto-increment numbers. For example, if we set it to TINYINT, it will limit the number of records to 255.

We can use the INT data type as its limit is large enough. So now we know quite a lot about the auto_increment function, and we can proceed to the last_insert_id() function.

Find the Last Inserted ID of AUTO_INCREMENT Column in MySQL

LAST_INSERT_ID returns the first generated int inserted for an AUTO_INCREMENT column due to the most recently executed INSERT statement. If the insertion fails, then the value of the last inserted ID doesn’t change.

We will execute it for the table that we created above.

SELECT LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

As you can see, even though the INSERT statement inserted three rows into the cars table, the first row successfully inserted is the one with an ID=1. Let’s say we need the last ID from the insert statement (in this case: 3).

We will utilize the max function of MySQL. It returns the maximum value at present for a particular column.

SELECT MAX( carID ) FROM cars;

Output:

+-----------+
| MAX(carID)|
+-----------+
|         3 |
+-----------+

Now we will analyze another case when we are inserting data into two tables one after another, and we need to get the last inserted ID of the first table.

CREATE TABLE cars (
     carID INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (carID)
);

CREATE TABLE carModels (

         otherID INT NOT NULL ,
          modelID INT NOT NULL AUTO_INCREMENT,
          name CHAR(30) NOT NULL,
           PRIMARY KEY (modelID)
);

ALTER TABLE carModels AUTO_INCREMENT=4;
INSERT INTO cars (name) VALUES ('BMW');
INSERT INTO carModels (otherID,name) VALUES (LAST_INSERT_ID(),'F30');
select LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+

First, let’s understand the code that we have executed. We created two tables, namely cars and carModels.

Then we changed the starting ID of carModels to differentiate the IDs of two tables and set it to 4 using the method we learned above. We can see from the output that it returns the last inserted ID of the second table.

What if we need the last inserted ID of the cars table?

We can do it in several ways, depending on the case. We will look at each of them depending on our case.

First Method: Store ID in a Variable in MySQL

In this method, we will store the last inserted ID of the first table in a variable and then use it in the second table instead of directly calling LAST_INSERT_ID.

INSERT INTO cars (name) VALUES ('BMW');
SET @last_id_in_cars = LAST_INSERT_ID();
INSERT INTO carModels (otherID,name) VALUES (@last_id_in_cars,'F30');
SELECT @last_id_in_cars;

Output:

+------------------+
| @last_id_in_cars |
+------------------+
|               33 |
+------------------+

We need to use the @ sign before selecting a variable.

Second Method: Retrieve Last Inserted ID From the Second Table in MySQL

It is a special case for our example as we used the last inserted ID of the first table in the second table. So we will retrieve it from the second table.

INSERT INTO cars (name) VALUES ('BMW');
INSERT INTO carModels (otherID,name) VALUES (LAST_INSERT_ID(),'F30');
SELECT otherID from carModels where modelID = LAST_INSERT_ID();

Get MAX(carID) from cars table:

We already learned the function max and can use it in this example.

INSERT INTO cars (name) VALUES ('BMW');
INSERT INTO carModels (otherID,name) VALUES (LAST_INSERT_ID(),'F30');
SELECT MAX(carID) FROM cars;

However, senior engineers do not recommend this method because it can give errors in race conditions. A race condition occurs when multiple threads can access the shared data simultaneously.