MySQL LAST_INSERT_ID Function

Summary: in this tutorial, you will learn how to use the MySQL LAST_INSERT_ID() function to return the first automatically generated integer successfully inserted for an AUTO_INCREMENT column.

Introduction to MySQL LAST_INSERT_ID() function

In database design, we often use a surrogate key to generate unique integer values for the primary key column of a table by using the AUTO_INCREMENT attribute:

CREATE TABLE table_name(
    id INT AUTO_INCREMENT,
    ...,
    PRIMARY KEY(id)
);Code language: SQL (Structured Query Language) (sql)

When you insert a row into the table without specifying a value for the id column, MySQL automatically generates a sequential unique integer for the id column.

The LAST_INSERT_ID() function returns the first automatically generated integer ( BIGINT UNSIGNED) successfully inserted for an AUTO_INCREMENT column.

If you insert multiple rows into the table using a single INSERT statement, the LAST_INSERT_ID() function returns the first automatically generated value only.

If the insertion fails, the result returned by the LAST_INSERT_ID() remain unchanged.

The LAST_INSERT_ID() function works based on the 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 to ensure that each client can obtain its own unique ID.

MySQL LAST_INSERT_ID function examples

Let’s look at an example of using MySQL LAST_INSERT_ID  function.

1) Using MySQL LAST_INSERT_ID() function to get value when inserting one row into a table

First, create a new table named messages that has the id column as the primary key and its value is automatically generated:

CREATE TABLE messages(
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(250) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into the messages table:

INSERT INTO messages(description)
VALUES('MySQL last_insert_id');Code language: SQL (Structured Query Language) (sql)

Third, use the LAST_INSERT_ID function to get the inserted value of the id column:

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

Fourth, attempt to insert a null value into the description column:

INSERT INTO messages(description) 
VALUES(NULL);Code language: SQL (Structured Query Language) (sql)

MySQL issued the following error:

Error Code: 1048. Column 'description' cannot be nullCode language: SQL (Structured Query Language) (sql)

Finally, use the LAST_INSERT_ID function to get the last automatically inserted value:

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

The result is unchanged.

2) Using MySQL LAST_INSERT_ID() function to get value when inserting multiple rows into a table

First, insert three rows into the messages table:

INSERT INTO messages(description) 
VALUES
    ('Insert multiple rows'), 
    ('LAST_INSERT_ID() example'), 
    ('MySQL AUTO_INCREMENT');Code language: SQL (Structured Query Language) (sql)

Second, query data from the messages table:

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

Third, use the LAST_INSERT_ID() function to get the inserted value:

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

As you can see clearly from the output, the LAST_INSERT_ID() function returns the generated value of the first row successfully inserted, not the last row.

3) Using MySQL LAST_INSERT_ID() function in a stored procedure

First, create two tables accounts and phones for testing:

CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL
);

CREATE TABLE phones (
    phone_id INT AUTO_INCREMENT,
    account_id INT NOT NULL,
    phone VARCHAR(25) NOT NULL,
    description VARCHAR(255) NOT NULL,
    PRIMARY KEY (phone_id , account_id),
    FOREIGN KEY (account_id)
        REFERENCES accounts (account_id)
);Code language: SQL (Structured Query Language) (sql)

Second, create a stored procedure that inserts an account with a phone number into both tables:

DELIMITER $$

CREATE PROCEDURE CreateAccount(
    fname VARCHAR(255), 
    lname VARCHAR(255),
    phone VARCHAR(25),
    description VARCHAR(255)
)
BEGIN
    DECLARE l_account_id INT DEFAULT 0;
    
    START TRANSACTION;
    -- Insert account data
    INSERT INTO accounts(first_name, last_name)
    VALUES(fname, lname);
    
    -- get account id
    SET l_account_id = LAST_INSERT_ID();
    
    -- insert phone for the account
    IF l_account_id > 0 THEN
	INSERT INTO phones(account_id, phone, description)
        VALUES(l_account_id,phone,description);
        -- commit
        COMMIT;
     ELSE
	ROLLBACK;
    END IF;
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The stored procedure inserts a row into the accounts table, get the account id using the LAST_INSERT_ID() function, and use this account id to insert a phone into the phones table.

A row in the phones table should only exist if there is a corresponding row in the accounts table, therefore, we wrap the insert statements in a transaction.

Third, call the stored procedure CreateAccount to create a new account with a phone number:

CALL CreateAccount(
    'John',
    'Doe',
    '(408)-456-4567',
    'Emergency Contact'
);Code language: SQL (Structured Query Language) (sql)

Fourth, query data from the accounts and phones tables:

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

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

It works as expected.

Finally, attempt to create a new account with the value of the last name is null:

CALL CreateAccount(
   'Jane', 
    null ,
    '(408)-456-1111',
    'Emergency Contact');Code language: SQL (Structured Query Language) (sql)

MySQL issued an error:

Error Code: 1048. Column 'last_name' cannot be nullCode language: SQL (Structured Query Language) (sql)

Summary

• Use the MySQL LAST_INSERT_ID function to get the first automatically generated integer successfully inserted for an AUTO_INCREMENT column.