JIYIK CN >

This tutorial explains three methods named LAST_INSERT_ID(), , MAX()and through code examples ORDER BY DESCand demonstrates how to get the ID of the most recently inserted record in MySQL.

Get the ID of the most recently inserted record in a MySQL table

There are 3 methods we can use to get the ID of the latest inserted record in a MySQL table. All of them are listed below, the table must have an AUTO_INCREMENT field to use any of the following methods.

1. Use the LAST_INSERT_ID() function.
2. Use the max() function.
3. Use the ORDER BY DESC clause.

Use the LAST_INSERT_ID() function to get the ID of the last inserted row in MySQL

When using the INSERT or UPDATE command on a table with an AUTO INCREMENTINSERT field, the LAST INSERT ID() function can be used to obtain the ID of the last inserted or updated record (row).

Let's understand it by preparing the tb_courses table and populating it with sample data.

Example query:

# create a table
CREATE TABLE tb_courses (
      ID INT NOT NULL AUTO_INCREMENT,
      COURSE_NAME VARCHAR(255) NOT NULL,
      PRIMARY KEY (id)
);

# insert data
INSERT INTO tb_courses (COURSE_NAME) VALUES ('Introduction to Java');
INSERT INTO tb_courses (COURSE_NAME) VALUES ('Python for Beginners');
INSERT INTO tb_courses (COURSE_NAME) VALUES('Database Systems');

# display data
SELECT * FROM tb_courses;

Output:

+----+----------------------+
| ID | COURSE_NAME          |
+----+----------------------+
|  1 | Introduction to Java |
|  2 | Python for Beginners |
|  3 | Database Systems     |
+----+----------------------+
3 rows in set (0.00 sec)

Now, execute the following command to retrieve the ID of the latest inserted record.

Example query:

SELECT LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

It returns the correct value, 3 is the last inserted ID. Now, we insert more data and insert multiple rows.

Example query:

# insert data
INSERT INTO tb_courses (COURSE_NAME)
VALUES
('Introduction to Machine Learning'),
('Deep Learning'),
('Statistics');

# display data
SELECT * FROM tb_courses;

Output:

+----+----------------------------------+
| ID | COURSE_NAME                      |
+----+----------------------------------+
|  1 | Introduction to Java             |
|  2 | Python for Beginners             |
|  3 | Database Systems                 |
|  4 | Introduction to Machine Learning | <======================
|  5 | Deep Learning                    |
|  6 | Statistics                       |
+----+----------------------------------+
6 rows in set (0.00 sec)

Run the following query to get the latest inserted ID.

Example query:

SELECT LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

We can see the output where LAST_INSERT_ID()the method returns the generated value for the first record instead of the last record. If we use a single INSERT statement for a row, we can only get the last inserted ID using LAST_INSERT_ID().

If we use a single INSERT statement for multiple rows, LAST_INSERT_ID()the function will output the generated value only for the first inserted record (see the output given above). Remember that the generated IDs are maintained on a per-connection basis in the server.

The first AUTO INCREMENT value generated for the most recent statement affecting the client's AUTO INCREMENT field LAST INSERT ID()is returned to the client by the method. Therefore, the value returned by LAST_INSERT_ID() is per-user and is not affected by other statements executed on the server by other users.

Use MAX() function to get the ID of the last inserted row in MySQL

We can use MAX()the function to get the last inserted ID of the row. For this method, it does not matter whether we use a single INSERT statement for one row or multiple rows.

First, we MAX()populate the tb_courses table with a single INSERT statement for one row using the function.

Example query:

INSERT INTO tb_courses (COURSE_NAME) VALUES ('Data Science');
SELECT MAX( ID ) FROM tb_courses;

Output:

+-----------+
| MAX( ID ) |
+-----------+
|         7 |
+-----------+
1 row in set (0.02 sec)

It returns the value 7, which is correct. We use a single INSERT statement for multiple rows and then use the MAX() function to retrieve the last inserted ID.

Example query:

INSERT INTO tb_courses (COURSE_NAME)
VALUES
('Introduction to Recommender Systems'),
('Data Structures'),
('Analysis of Algorithms');

SELECT MAX( ID ) FROM tb_courses;

Output:

+-----------+
| MAX( ID ) |
+-----------+
|        10 |
+-----------+
1 row in set (0.00 sec)

It returns the correct value, which is 10.

Get the ID of the last inserted row in MySQL using ORDER BY DESC

The ORDER BY DESC clause can also be used to sort the data in descending order using the ID field and get the ID from the first row (the last row before sorting).

Example query:

SELECT ID FROM tb_courses ORDER BY ID DESC LIMIT 1;

Output:

+----+
| ID |
+----+
| 10 |
+----+
1 row in set (0.05 sec)