MySQL LAST_INSERT_ID() – A Complete Guide

The MySQL LAST_INSERT_ID() function of MySQL does exactly as it reads. It will return the latest ID of the AUTO_INCREMENT column. This can be a useful little function to find out the latest ID of the AUTO_INCREMENT column.

The LAST_INSERT_ID() function can be used without an expression and also with an expression. This function when used without expression it returns the value in BIGINT UNSIGNED(64-bit) value.

When the LAST_INSERT_ID() is used with an expression the return value is an unsigned integer.

Also read: MySQL CONVERT() Function

Syntax of MySQL LAST_INSERT_ID()

SELECT LAST_INSERT_ID();Code language: SQL (Structured Query Language) (sql)

SELECT LAST_INSERT_ID(expression);Code language: SQL (Structured Query Language) (sql)

Let’s look at an example to understand this function better.

Example of LAST_INSERT_ID()

CREATE TABLE SQLDB (
       ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
       Name VARCHAR(30) NOT NULL
       );

INSERT INTO SQLDB VALUES (NULL, 'MySQL');

SELECT LAST_INSERT_ID();Code language: SQL (Structured Query Language) (sql)

In the above block of code, a new table called “SQLDB” is created with “ID” as a primary key which is auto-incremented. The second column is for “Name” which can accept a VARCHAR value of up to 30 characters and it cannot be NULL.

After creating the table, one row is inserted with the name value of “MySQL“

After inserting the new row, using LAST_INSERT_ID() the last auto-incremented value of the table comes out to be 1.

Now let’s look at how many rows are in our table.

SELECT * FROM SQLDB;Code language: SQL (Structured Query Language) (sql)

The above output shows that the table has only one row and the ID is 1.

Inserting some more rows will give a much clearer look into the function.

INSERT INTO SQLDB VALUES
       (NULL, 'SQL Server'), 
       (NULL, 'PostgreSQL'), 
       (NULL, 'SQLite'), 
       (NULL, 'MariaDB');Code language: SQL (Structured Query Language) (sql)

The query above will add 4 more rows into the table with name values “SQL Server”, “PostgreSQL”, “SQLite”, and “MariaDB”. This should return the LAST_INSERT_ID() value 5.

SELECT LAST_INSERT_ID();

After running the above query we get the below output.

Since we combined all the queries in one, you’ll see that the last insert ID that’s generated is 2. You can try the same by increasing the number of insert statements instead of clubbing them into one.

This basically gives you the number of insert statements that went into building the table you’re querying.

Example with an expression

Let’s look at an example where we use an expression in our LAST_INSERT_ID() function.

CREATE TABLE JD (id INT NOT NULL);

INSERT INTO JD VALUES (1);

UPDATE JD SET id=LAST_INSERT_ID(id+1);Code language: SQL (Structured Query Language) (sql)

In the above code, a new table called “JD” is created. 1 is inserted into that table. Using UPDATE the LAST_INSERT_ID() is set to “id+1”. The output we will receive every time we run LAST_INSERT_ID() will be the id + 1.

Conclusion

The LAST_INSERT_ID() Function is super simple and is not very difficult to understand and execute. It works like a neat little trick to find out the auto-incremented ID for a table. When used in amalgamation with other keywords this function can be very useful. Using an expression in the LAST_INSERT_ID() function can help do precisely with what we aim to do.

You can read more about the LAST_INSERT_ID() Function here.

If you’re receiving an error such as an “error code: 1175” while running the UPDATE query this Stackoverflow question can help you solve it. MySQL error code: 1175 during UPDATE in MySQL Workbench. We’ve also covered different SQLite tutorials if you’re interested in that topic.