How to Get the Identity of Last Inserted Row in MySQL?

The LAST_INSERT_ID() returns the identity value after an insert into the specific table.

Syntax: SELECT LAST_INSERT_ID();

Getting the ID of a column after an insert may be required during database programming and in this article, we will learn how to get the ID of the inserted row.

In MySQL, the Key column of a table can be set to auto-increment with the starting value. The auto-increment column helps to avoid duplicate IDs being created or inserted.

Auto Increment Column

In MySQL, the auto-increment column creates a unique ID automatically in the table. The auto-increment column can be created as below:

CREATE TABLE IT_Customers 
( CUST_Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL
);

Here the CUST_ID is set as an Auto increment column and the default value is 1.

The auto-increment column can also be set with the starting value of our choice instead of the default value 1. For some reason, if we need the starting value to be another value than the default value, the below command can be used.

ALTER TABLE CUST_Id AUTO_INCREMENT=100;

Getting the Identity Value

The LAST_INSERT_ID() function will return the last Identity value after an insert in the specified table.

Below is an example of how to get the identity value after insert.

Retrieving ID of Last Inserted Row in MySQL Example

In this example, we are going to look at two SQL statements. Let’s take a look at each of these statements and explain them:

INSERT INTO IT_Customers
(Customer_name) VALUES ('HP');
SELECT LAST_INSERT_ID();

Output:

LAST_INSERT_ID() Example

Explanation:

In the above example, one row was inserted into the IT_Customers table and the LAST_INSERT_ID value returns as 1 since this was the first row and the default auto-increment value is 1.

When we insert multiple rows, the last identity value will be returned by LAST_INSERT_ID() if this function is executed after the last insert.

Retrieving ID of Last Inserted Row after Multiple Inserts Example

In this example, the SQL statements involve the insertion of two records into the “IT_Customers” table, each followed by the retrieval of the last inserted ID. Let’s break down the statements and provide an explanation:

INSERT INTO IT_Customers
(Customer_name) VALUES ('Samsung');
INSERT INTO IT_Customers
(Customer_name) VALUES ('DELL');
SELECT LAST_INSERT_ID();

Output:

Retrieving ID of Last Inserted Row After Multiple Inserts

Explanation:

In the above example, 2 rows are inserted and the LAST_INSERT_ID inserted the Identity value of the last row inserted which is 3 as there was 1 row that existed before this insert.

Bulk Insert and Identity Value

When we copy data from one table to another table using the INSERT INTO command, the LAST_INSERT_ID(), will return only the ID of the first row (Last Insert Id is 1).

Example

In this example, a series of SQL statements is presented to illustrate the process of inserting data into the “IT_Suppliers” table by selecting values from the “IT_Customers” table. Additionally, the last inserted ID is retrieved. Let’s break down each part:

INSERT INTO IT_Suppliers
(supplier_name)
SELECT customer_name
FROM IT_Customers
ORDER BY CUST_Id;
SELECT LAST_INSERT_ID();
SELECT* FROM IT_Suppliers

Output:

Identity value after Bulk Insert

Explanation:

In the above example, 2 tables namely IT_Suppliers and IT_Customers are used. Data is copied from the IT_Customers to the IT_Suppliers table using the INSERT INTO command and SELECT query. It can be noted that 3 rows were inserted from IT_Customers to IT_Suppliers and when we check the LAST_INSERT_ID, it returns only the value 1, which is the identity value from the first row inserted.

Conclusion

In this article, we learned about auto-increment columns, what is Identity column in a table, and how to get the Identity of the last inserted row after a single or multiple rows are inserted into a Table in MySQL.

We also have seen how to set the Auto Increment initial value since the default auto-increment value is 1. Getting an identity value can be a useful scenario where we take the identity value after insert and save it to another related table.