{"id":513,"date":"2011-08-08T13:10:02","date_gmt":"2011-08-08T13:10:02","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=513"},"modified":"2023-10-10T01:43:56","modified_gmt":"2023-10-10T08:43:56","slug":"mysql-last_insert_id","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-functions\/mysql-last_insert_id\/","title":{"rendered":"MySQL LAST_INSERT_ID Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL LAST_INSERT_ID()<\/code> function to return the first automatically generated integer successfully inserted for an AUTO_INCREMENT<\/code> column.<\/p>\n\n\n\n

Introduction to MySQL LAST_INSERT_ID() function<\/h2>\n\n\n\n

In database design, we often use a surrogate key to generate unique integer<\/a> values for the primary key<\/a> column of a table by using the AUTO_INCREMENT<\/code> attribute:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name(\n    id<\/span> INT<\/span> AUTO_INCREMENT,\n    ...,\n    PRIMARY KEY<\/span>(id<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The LAST_INSERT_ID()<\/code> function returns the first automatically generated integer ( BIGINT UNSIGNED<\/code>) successfully inserted for an AUTO_INCREMENT<\/code> column.<\/p>\n\n\n\n
If you insert multiple rows<\/a> into the table using a single INSERT<\/code> statement, the LAST_INSERT_ID()<\/code> function returns the first automatically generated value only.<\/p>\n\n\n\n
If the insertion fails, the result returned by the LAST_INSERT_ID()<\/code> remain unchanged.<\/p>\n\n\n\n
The LAST_INSERT_ID()<\/code> function works based on the client-independent principle. It means the value returned by the LAST_INSERT_ID()<\/code> function for a specific client is the value generated by that client only to ensure that each client can obtain its own unique ID.<\/p>\n\n\n\n
MySQL LAST_INSERT_ID function examples<\/h2>\n\n\n\n
Let’s look at an example of using MySQL LAST_INSERT_ID<\/code>  function.<\/p>\n\n\n\n
1) Using MySQL LAST_INSERT_ID() function to get value when inserting one row into a table<\/h3>\n\n\n\n
First, create a new table<\/a> named messages<\/code> that has the id<\/code> column as the primary key and its value is automatically generated:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> messages(\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    description VARCHAR<\/span>(250<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert a new row into the messages<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> messages(description)\nVALUES<\/span>('MySQL last_insert_id'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, use the LAST_INSERT_ID<\/code> function to get the inserted value of the id<\/code> column:<\/p>\n\n\n
SELECT<\/span> LAST_INSERT_ID<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Fourth, attempt to insert a null value into the description<\/code> column:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> messages(description) \nVALUES<\/span>(NULL<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued the following error:<\/p>\n\n\n
Error Code: 1048. Column 'description' cannot be null<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, use the LAST_INSERT_ID<\/code> function to get the last automatically inserted value:<\/p>\n\n\n
SELECT<\/span> LAST_INSERT_ID<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The result is unchanged.<\/p>\n\n\n\n
2) Using MySQL LAST_INSERT_ID() function to get value when inserting multiple rows into a table<\/h3>\n\n\n\n
First, insert three rows into the messages<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> messages(description) \nVALUES<\/span>\n    ('Insert multiple rows'<\/span>), \n    ('LAST_INSERT_ID() example'<\/span>), \n    ('MySQL AUTO_INCREMENT'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, query data from the messages<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> messages;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
Third, use the LAST_INSERT_ID()<\/code> function to get the inserted value:<\/p>\n\n\n
SELECT<\/span> LAST_INSERT_ID<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
As you can see clearly from the output, the LAST_INSERT_ID()<\/code> function returns the generated value of the first row successfully inserted, not the last row.<\/p>\n\n\n\n
3) Using MySQL LAST_INSERT_ID() function in a stored procedure<\/h3>\n\n\n\n
First, create two tables accounts<\/code> and phones<\/code> for testing:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> accounts (\n    account_id INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    first_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    last_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);\n\nCREATE<\/span> TABLE<\/span> phones (\n    phone_id INT<\/span> AUTO_INCREMENT,\n    account_id INT<\/span> NOT<\/span> NULL<\/span>,\n    phone VARCHAR<\/span>(25<\/span>) NOT<\/span> NULL<\/span>,\n    description VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span> (phone_id , account_id),\n    FOREIGN<\/span> KEY<\/span> (account_id)\n        REFERENCES<\/span> accounts (account_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a stored procedure<\/a> that inserts an account with a phone number into both tables:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> PROCEDURE<\/span> CreateAccount(\n    fname VARCHAR<\/span>(255<\/span>), \n    lname VARCHAR<\/span>(255<\/span>),\n    phone VARCHAR<\/span>(25<\/span>),\n    description VARCHAR<\/span>(255<\/span>)\n)\nBEGIN<\/span>\n    DECLARE<\/span> l_account_id INT<\/span> DEFAULT<\/span> 0<\/span>;\n    \n    START<\/span> TRANSACTION<\/span>;\n    -- Insert account data<\/span>\n    INSERT<\/span> INTO<\/span> accounts(first_name, last_name)\n    VALUES<\/span>(fname, lname);\n    \n    -- get account id<\/span>\n    SET<\/span> l_account_id = LAST_INSERT_ID<\/span>();\n    \n    -- insert phone for the account<\/span>\n    IF l_account_id > 0 THEN\n\tINSERT<\/span> INTO<\/span> phones(account_id, phone, description)\n        VALUES<\/span>(l_account_id,phone,description);\n        -- commit<\/span>\n        COMMIT<\/span>;\n     ELSE\n\tROLLBACK<\/span>;\n    END<\/span> IF<\/span>;\nEND<\/span>$$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The stored procedure inserts a row into the accounts<\/code> table, get the account id using the LAST_INSERT_ID()<\/code> function, and use this account id to insert a phone into the phones<\/code> table. <\/p>\n\n\n\n
Third, call the stored procedure CreateAccount<\/code> to create a new account with a phone number:<\/p>\n\n\n
CALL<\/span> CreateAccount(\n    'John'<\/span>,\n    'Doe'<\/span>,\n    '(408)-456-4567'<\/span>,\n    'Emergency Contact'<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, query data from the accounts<\/code> and phones<\/code> tables:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> accounts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n
SELECT<\/span> * FROM<\/span> phones;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
It works as expected.<\/p>\n\n\n\n
Finally, attempt to create a new account with the value of the last name is null:<\/p>\n\n\n
CALL<\/span> CreateAccount(\n   'Jane'<\/span>, \n    null<\/span> ,\n    '(408)-456-1111'<\/span>,\n    'Emergency Contact'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued an error:<\/p>\n\n\n
Error Code: 1048. Column 'last_name' cannot be null<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL LAST_INSERT_ID<\/code> function to get the first automatically generated integer successfully inserted for an AUTO_INCREMENT<\/code> column.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
A row in the phones<\/code> table should only exist if there is a corresponding row in the accounts<\/code> table, therefore, we wrap the insert statements in a transaction<\/a>.<\/p>\n\n\n\n
When you insert a row into the table<\/a> without specifying a value for the id<\/code> column, MySQL automatically generates a sequential unique integer for the id<\/code> column.<\/p>\n\n\n\n