Collections:
LAST_INSERT_ID() - Last Value of AUTO_INCREMENT Column
How to obtain the last inserted value of an AUTO_INCREMENT column using the LAST_INSERT_ID() function?
✍: FYIcenter.com
LAST_INSERT_ID() is a MySQL built-in function that returns the last inserted value of an AUTO_INCREMENT column. For example:
CREATE TABLE MyTable (id INTEGER AUTO_INCREMENT, comment CHAR(80), PRIMARY KEY (id)); SELECT LAST_INSERT_ID(); -- +------------------+ -- | LAST_INSERT_ID() | -- +------------------+ -- | 0 | -- +------------------+ INSERT INTO MyTable (comment) VALUES ('I like it!'); INSERT INTO MyTable (comment) VALUES ('Good job1'); SELECT LAST_INSERT_ID(); -- +------------------+ -- | LAST_INSERT_ID() | -- +------------------+ -- | 2 | -- +------------------+ SELECT * FROM MyTable; -- +----+------------+ -- | id | comment | -- +----+------------+ -- | 1 | I like it! | -- | 2 | Good job1 | -- +----+------------+
Note that LAST_INSERT_ID() returns the first inserted id if the last INSERT statement inserted multiple rows. For example:
INSERT INTO MyTable (comment) VALUES ('Best example!'), ('Easy to follow!'); SELECT LAST_INSERT_ID(); -- +------------------+ -- | LAST_INSERT_ID() | -- +------------------+ -- | 3 | -- +------------------+ SELECT * FROM MyTable; -- +----+-----------------+ -- | id | comment | -- +----+-----------------+ -- | 1 | I like it! | -- | 2 | Good job1 | -- | 3 | Best example! | -- | 4 | Easy to follow! | -- +----+-----------------+
If LAST_INSERT_ID() is called with an argument, it will return the argument and set the argument as the last inserted AUTO_INCREMENT value. For example:
SELECT LAST_INSERT_ID(99); -- +--------------------+ -- | LAST_INSERT_ID(99) | -- +--------------------+ -- | 99 | -- +--------------------+ SELECT LAST_INSERT_ID(); -- +------------------+ -- | LAST_INSERT_ID() | -- +------------------+ -- | 99 | -- +------------------+
You can use the combination of LAST_INSERT_ID(seq) and LAST_INSERT_ID() to build a table-based sequence generator as shown below:
CREATE TABLE sequence (id INTEGER); INSERT INTO sequence VALUES (0); UPDATE sequence SET id = LAST_INSERT_ID(id+1); SELECT LAST_INSERT_ID(); -- +------------------+ -- | LAST_INSERT_ID() | -- +------------------+ -- | 1 | -- +------------------+ UPDATE sequence SET id = LAST_INSERT_ID(id+1); SELECT LAST_INSERT_ID(); -- +------------------+ -- | LAST_INSERT_ID() | -- +------------------+ -- | 2 | -- +------------------+ UPDATE sequence SET id = LAST_INSERT_ID(id+1); SELECT LAST_INSERT_ID(); -- +------------------+ -- | LAST_INSERT_ID() | -- +------------------+ -- | 3 | -- +------------------+
Reference information of the LAST_INSERT_ID() function:
LAST_INSERT_ID(exp): seq Returns the last inserted value of an AUTO_INCREMENT column, if argument is not provided. Returns the given exp and sets it as the last inserted AUTO_INCREMENT value, if argument is provided. Arguments, return value and availability: exp: Optional. Default is the first AUTO_INCREMENT value of the last INSERT statement. seq: Return value. The last inserted AUTO_INCREMENT value. Available since MySQL 4.0.
⇒ PS_CURRENT_THREAD_ID() - PS Thread ID of Current Connect
⇐ ICU_VERSION() - ICU (International Components for Unicode) Version
2023-12-19, 214🔥, 0💬
Popular Posts:
How To Use DATEADD() Function in SQL Server Transact-SQL? DATEADD() is a very useful function for ma...
How To Convert Binary Strings into Hexadecimal Character Strings in SQL Server? When a query returns...
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
Can You Drop an Index Associated with a Unique or Primary Key Constraint in Oracle? You can not dele...
How To Divide Query Output into Multiple Groups with the GROUP BY Clause in SQL Server? Sometimes, y...