[MySQL Tips] How to Retrieve the ID of the Last Inserted Row

Introduction / What I want to do

As the title suggests, I want to "get the ID value of the very last row of a table after insertion."

Suppose we have the following table.

• Table name: user_list

id	Username
1	Hajime-kun
2	Tsugiko-chan
3	Miban-kun
4	Shide-chan
5	Goten-san

*Note: Assume the id column has the AUTO_INCREMENT attribute, so it is automatically incremented by 1 for each insertion.

I will show you how to get the value of the last ID (which would be 6 in this case) after updating the table as follows:

-- (Comment): Insert the user with id=1 at the end of the table.
INSERT INTO user_list (Username)
SELECT Username FROM user_list WHERE id = 1;

*By the way, when combining INSERT and SELECT like this, the VALUES keyword is not needed! (INSERT...SELECT Syntax)

• Table name: user_list

id	Username
1	Hajime-kun
2	Tsugiko-chan
3	Miban-kun
4	Shide-chan
5	Goten-san
6	Hajime-kun

[Method 1] Sort by ID in descending order and get the ID of the first row

First, let's review how to sort.
To sort based on a specific column, use the ORDER BY clause. To sort in reverse order, add the DESC keyword. Additionally, using the LIMIT clause allows you to restrict the number of rows returned by a SELECT statement.

ORDER BY reference_column DESC LIMIT count;

*Reference: SELECT Syntax / MySQL Official

From the above, you can see that you can obtain the largest ID value by (1) "sorting the IDs in descending order using the ORDER BY clause and DESC" and (2) "limiting the result to 1 with the LIMIT clause."

Let's actually try it.

SELECT id FROM user_list ORDER BY id DESC LIMIT 1;
-> 6

[Method 2] Get the inserted ID value using an information function

MySQL provides many useful built-in functions by default. This time, we will use LAST_INSERT_ID(), which is one of the information functions, to get the "AUTO_INCREMENT column value for the most recent INSERT."

SELECT LAST_INSERT_ID();

This method is significantly easier.

However, as stated in the official documentation, please note that the LAST_INSERT_ID function retrieves the "AUTO_INCREMENT column value" and is not necessarily the "ID of the last inserted record."
*Reference: MySQL's last_insert_id() was not the "ID of the last inserted record" / Personal Blog

That's all for today.