4 ways to get last inserted id of a MySQL table

This article will see how to get the last inserted id of a MySQL table using 4 different ways.

Table of Contents

• How to get last inserted id of a MySQL table using max() function
• How to get last inserted id of a MySQL table using ORDER BY DESC
• How to get last inserted id of a MySQL table using using SELECT LAST_INSERT_ID()
• How to get last inserted id of a MySQL table using using AUTO_INCREMENT

Let us get started by preparing the sample data. We will be creating a table, employee_designation, followed by inserting rows into it.

#create the table employee_designation
CREATE TABLE employee_designation (
	  id int NOT NULL AUTO_INCREMENT,
	  emp_id int DEFAULT NULL,
	  emp_name varchar(255) DEFAULT NULL,
	  emp_designation varchar(255) DEFAULT NULL,
	  PRIMARY KEY (id)
	);
# insert rows to employee_designation
	INSERT INTO employee_designation (emp_id,emp_name,emp_designation) 
	VALUES(10,"Henry","Sales Manager");
	INSERT INTO employee_designation (emp_id,emp_name,emp_designation) 
	VALUES(20,"Richa","Sales Assistant");
	INSERT INTO employee_designation (emp_id,emp_name,emp_designation) 
	VALUES (30,"Ved","Sales Assistant");
	INSERT INTO employee_designation (emp_id,emp_name,emp_designation) 
	VALUES(40,"George","Senior Sales Manager");
	INSERT INTO employee_designation (emp_id,emp_name,emp_designation) 
	VALUES(50,"Alexendra","Sales Assistant");
	INSERT INTO employee_designation (emp_id,emp_name,emp_designation) 
	VALUES(60,"Gustav","Sales Manager");
	INSERT INTO employee_designation (emp_id,emp_name,emp_designation) 
        VALUES(70,"Veronica","Sales Manager"); 

Note that the id column is missing in the insert statements as id is an auto_increment key and will assign the non-negative values on its own.

Action Output:-

Create table and inserts are successful, to view the snapshot of the table execute:

SELECT * FROM TABLE employee_designation; 

Output:-

How to get last inserted id of a MySQL table using max() function

We will be using the max() function to get the last inserted id. Max() function returns the maximum value at present for a particular column. Observe the below query for the solution:

 SELECT MAX( id ) FROM employee_designation;

Output:-

The output in image_3 shows that the last inserted id was 7 in the employee_designation table.

How to get last inserted id of a MySQL table using ORDER BY DESC

We will be doing ORDER BY DESC on the returned id values from a select query to get the last inserted id. The query will first select all the values from the id column followed by ordering them in descending order. Finally, only one record is returned as a LIMIT clause is applied. Observe the below query for the solution:

SELECT id FROM employee_designation ORDER BY id DESC LIMIT 1;

Output:-

The output in image_4 shows that the last inserted id was 7 in the employee_designation table.

How to get last inserted id of a MySQL table using LAST_INSERT_ID()

We will be using the LAST_INSERT_ID() function to get the last inserted id. Last_insert_id() MySQL function returns the BIG UNSIGNED value for an insert statement on an auto_increment column.

Important Note:

If there is only a single insert statement for multiple rows, last_insert_id() will return the value generated only for the first inserted row.

Observe the below query for the solution.

SELECT LAST_INSERT_ID();

Output:-

How to get last inserted id of a MySQL table using AUTO_INCREMENT

We will be using the INFORMATION SCHEMA to get the last inserted id of an auto-incremented column of a MySQL table.

Important Note:

Note that the below solution will work only for the columns with auto_increment.

Observe the below query for the solution.

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employee_designation';

Output:-

READ MORE:

• Find all tables with specific column names in MySQL
• MySQL add primary key multiple columns
• Mysql update set multiple columns
• Mysql update column with value from another table
• Select all columns except one of a MySQL table
• MySQL ADD COLUMN IF NOT EXISTS
• MYSQL: change column order
• MySQL: change column type to VARCHAR without losing data
• MySQL get column names

We hope this article helped you to get the last inserted id from a MySQL table. Good Luck!!!