MySQL LAST_INSERT_ID

Summary: in this tutorial, you will learn how to use the MySQL LAST_INSERT_ID function to obtain the generated sequence number of the last insert record.

In database design, we often use a surrogate key to generate unique values for the identity column by setting the AUTO_INCREMENT attribute for the column. When we insert a new record into the table that has AUTO_INCREMENT column, MySQL generates a unique ID automatically based on the column’s definition.

For more information on how to set AUTO_INCREMENT attribute for a column, check it out the MySQL sequencetutorial.

We can obtain the generated sequence number by using the MySQL LAST_INSERT_ID function and use the number for the next statements e.g., inserting a new row into the related tables.

Let’s take a look at an example of using MySQL LAST_INSERT_ID function:

CREATE TABLE tbl(    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,    description varchar(250) NOT NULL ); INSERT INTO tbl(description) VALUES('MySQL last_insert_id'); SELECT LAST_INSERT_ID();

How it works.

• First, we created a new table named tbl for testing. In the tbl table, we set  AUTO_INCREMENT attribute for the   id column.

• Second, we inserted a new record into the tbl table.

• Third, we used MySQL LAST_INSERT_ID function to obtain last insert id that MySQL has been generated.

It’s important to note that if you insert multiple records into a table using a single INSERT statement, the LAST_INSERT_ID function will return the last insert id of the first record. Suppose the AUTO_INCREMENT column has the last sequence number 3 and you insert 5 records into the table, when you use the LAST_INSERT_ID function to get the last insert id, you will get 4 instead of 8.

Let’s take a look at the following example:

INSERT INTO tbl(description) VALUES('record 1'), ('record 2'), ('record 3'); SELECT LAST_INSERT_ID();

Check the data of the tbl table:

SELECT * FROM tbl

First, we inserted 3 records into the tbl table by using a single INSERT statement. Then, we used the LAST_INSERT_ID function to get the last insert id, which is the id of the 'record 1'.


The LAST_INSERT_ID function works based on client-independent principle. It means the value returned by the LAST_INSERT_ID function for a specific client is the value generated by that client only. This ensures that each client can obtain its own unique ID.

In this tutorial, we have shown you how to use the MySQL LAST_INSERT_ID function to get the sequence number of the last record that has been inserted into a table.

原文链接：http://outofmemory.cn/mysql/function/mysql-last_insert_id