{"id":18129,"date":"2021-06-30T09:09:22","date_gmt":"2021-06-29T23:09:22","guid":{"rendered":"https:\/\/database.guide\/?p=18129"},"modified":"2021-06-30T09:09:22","modified_gmt":"2021-06-29T23:09:22","slug":"mariadb-last_insert_id-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-last_insert_id-explained\/","title":{"rendered":"MariaDB LAST_INSERT_ID() Explained"},"content":{"rendered":"\n

In MariaDB, LAST_INSERT_ID()<\/code> is a built-in function that returns the first automatically generated value successfully inserted for an AUTO_INCREMENT<\/code> column as a result of the most recently executed INSERT<\/a><\/code> statement.<\/p>\n\n\n\n

It can also be called with an argument, in which case, it returns the value of the expression and the next call to LAST_INSERT_ID()<\/code> will return the same value.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The function can be called in the following ways:<\/p>\n\n\n\n

LAST_INSERT_ID()\nLAST_INSERT_ID(expr)<\/code><\/pre>\n\n\n\n
Where expr<\/code> is returned, and the next call to LAST_INSERT_ID()<\/code> will return the same value.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
As an example, let’s create a table with an AUTO_INCREMENT<\/code> column:<\/p>\n\n\n\n
CREATE TABLE guest (\n  guest_id INT NOT NULL AUTO_INCREMENT,\n  guest_name VARCHAR(255) NOT NULL,\n  PRIMARY KEY (guest_id)\n);<\/code><\/pre>\n\n\n\n
The guest_id<\/code> column uses AUTO_INCREMENT<\/code> for its value.<\/p>\n\n\n\n
Now insert some rows:<\/p>\n\n\n\n
INSERT INTO guest (guest_name) VALUES ('Homer');\nINSERT INTO guest (guest_name) VALUES ('Bart');\nINSERT INTO guest (guest_name) VALUES ('Marge');<\/code><\/pre>\n\n\n\n
Now let’s run LAST_INSERT_ID()<\/code>:<\/p>\n\n\n\n
SELECT LAST_INSERT_ID();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------+\n| LAST_INSERT_ID() |\n+------------------+\n|                3 |\n+------------------+<\/pre>\n\n\n\n
It returns 3<\/code>.<\/p>\n\n\n\n
Now, let’s return all rows in the table to verify that the last row has an AUTO_INCREMENT<\/code> value of 3<\/code> inserted:<\/p>\n\n\n\n
SELECT *\nFROM guest;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------+------------+\n| guest_id | guest_name |\n+----------+------------+\n|        1 | Homer      |\n|        2 | Bart       |\n|        3 | Marge      |\n+----------+------------+<\/pre>\n\n\n\n
The guest_id<\/code> column goes up to 3<\/code>. <\/p>\n\n\n\n
Including an Argument<\/h2>\n\n\n\n
As mentioned, if called with an argument, the function returns the value of the expression and the next call to LAST_INSERT_ID()<\/code> will return the same value.<\/p>\n\n\n\n
SELECT LAST_INSERT_ID(9);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------------+\n| LAST_INSERT_ID(9) |\n+-------------------+\n|                 9 |\n+-------------------+<\/pre>\n\n\n\n
Now call it again, but with no argument:<\/p>\n\n\n\n
SELECT LAST_INSERT_ID();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------+\n| LAST_INSERT_ID() |\n+------------------+\n|                9 |\n+------------------+<\/pre>\n\n\n\n
It still returns a value of 9<\/code>.<\/p>\n\n\n\n
However, if we continue to insert values into our guest<\/code> table, the AUTO_INCREMENT<\/code> will continue where it left off on that table:<\/p>\n\n\n\n
INSERT INTO guest (guest_name) VALUES ('Lisa');<\/code><\/pre>\n\n\n\n
Now let’s run LAST_INSERT_ID()<\/code>:<\/p>\n\n\n\n
SELECT LAST_INSERT_ID();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------+\n| LAST_INSERT_ID() |\n+------------------+\n|                4 |\n+------------------+<\/pre>\n\n\n\n
It returns 4<\/code>.<\/p>\n\n\n\n
And here’s what the table looks like now:<\/p>\n\n\n\n
SELECT *\nFROM guest;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------+------------+\n| guest_id | guest_name |\n+----------+------------+\n|        1 | Homer      |\n|        2 | Bart       |\n|        3 | Marge      |\n|        4 | Lisa       |\n+----------+------------+<\/pre>\n\n\n\n
For more information on this function, see the MariaDB documentation<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, LAST_INSERT_ID() is a built-in function that returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. It can also be called with an argument, in which case, it returns the value of the expression and the next call to LAST_INSERT_ID() will … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[93,20],"class_list":["post-18129","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18129","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=18129"}],"version-history":[{"count":9,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18129\/revisions"}],"predecessor-version":[{"id":18139,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18129\/revisions\/18139"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18129"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}