LAST_INSERT_ID
Returns the last value inserted into an AUTO_INCREMENT column.
Syntax
LAST_INSERT_ID() LAST_INSERT_ID(expr)
Arguments
expr
: any expression. This may be a column name, the result of another function, or a math operation.
Return type
Integer
Remarks
LAST_INSERT_ID()
only returns automatically generatedAUTO_INCREMENT
values. The value ofLAST_INSERT_ID()
remains unchanged if you set theAUTO_INCREMENT
column of a row to a explicit or non-automatically generated value.LAST_INSERT_ID
is maintained on a per-connection basis, allowing multiple clients to execute theUPDATE
statement and generate their own sequence with theSELECT
statement, without affecting or being affected by the sequence generated by other clients.After a multi-insert statement, the value returned by
LAST_INSERT_ID()
is the first value inserted in that group of records.The value of
LAST_INSERT_ID()
remains unchanged if no record is successfully added in anINSERT
statement.While using
INSERT IGNORE
if the row is ignored, the value ofLAST_INSERT_ID
remains unchanged.If the value of
LAST_INSERT_ID
is changed inside a stored procedure, the changed value is seen by statements executed after the procedure call.If the value of
LAST_INSERT_ID
is changed inside a stored function, the value is restored when the function ends.If
expr
is specified withLAST_INSERT_ID
,LAST_INSERT_ID(expr)
returns the value of the expression expr and stores this value as the next value to be returned byLAST_INSERT_ID()
.LAST_INSERT_ID
works withUPDATE
/INSERT
statements, but it may not work as expected withSELECT
statements because of the distributed nature of SingleStoreDB.
Note
LAST_INSERT_ID(expr)
is only updated after INSERT
and UPDATE
queries. So, LAST_INSERT_ID()
cannot be used to retrieve the value for LAST_INSERT_ID(expr)
after executing other SQL statements, like SELECT
or SET
with LAST_INSERT_ID(expr)
.
Examples
In the following example, LAST_INSERT_ID()
returns 2 when executed after a multi-insert statement, which is the first inserted value in the statement.
CREATE TABLE persons ( id BIGINT PRIMARY KEY AUTO_INCREMENT, firstname VARCHAR(64), lastname VARCHAR(64)); INSERT INTO persons VALUES (NULL, 'Eponymous', 'Bach');
SELECT LAST_INSERT_ID(); **** +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+
INSERT INTO persons VALUES (NULL, 'Ping', 'Baudot'), (NULL, 'Count', 'Modulo'), (NULL, 'Hugh', 'Rustic');
SELECT LAST_INSERT_ID(); **** +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec)
Following each previous INSERT
statement, the value of LAST_INSERT_ID()
was changed since a NULL
value was inserted in the AUTO_INCREMENT
column.
INSERT INTO persons VALUES (5, 'Grant', 'Acos');
SELECT LAST_INSERT_ID(); **** +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+
The following set of queries demonstrate the behavior of LAST_INSERT_ID(expr)
. Create a table and initiate the sequence counter as:
CREATE TABLE sequences (sequence_name TEXT PRIMARY KEY, value BIGINT NOT NULL); INSERT INTO sequences VALUES ('my_sequence_name', 0);
Now, generate the sequence:
UPDATE sequences SET value = LAST_INSERT_ID(value + 1) WHERE sequence_name = 'my_sequence_name'; SELECT LAST_INSERT_ID(); **** +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+
The UPDATE
statement increments the counter.
Using Stored Procedures to Support Multiple Sequences
CREATE TABLE sequence_generator ( seq_name TEXT PRIMARY KEY, next_val INT NOT NULL ); DELIMITER // CREATE OR REPLACE PROCEDURE create_new_sequence ( sequence_name TEXT NOT NULL, starting_value INT NOT NULL) AS BEGIN INSERT INTO sequence_generator VALUES (sequence_name, starting_value); END // -- The following stored procedure returns a single ID from the sequence generator from a particular instance CREATE OR REPLACE PROCEDURE get_single_sequence_id (sequence_name TEXT NOT NULL) AS BEGIN UPDATE sequence_generator SET next_val = LAST_INSERT_ID(next_val + 1) WHERE seq_name = sequence_name; echo SELECT LAST_INSERT_ID() - 1 AS next_id; END // -- The following procedure reserves multiple IDs from the sequence generator from a particular sequence and returns the first id CREATE OR REPLACE PROCEDURE get_multiple_sequence_id ( sequence_name TEXT NOT NULL, num_ids INT NOT NULL) AS BEGIN UPDATE sequence_generator SET next_val = LAST_INSERT_ID(next_val + num_ids) WHERE seq_name = sequence_name; echo SELECT (LAST_INSERT_ID() - num_ids) AS start_id; END // DELIMITER ; CALL create_new_sequence("my_seq_1", 0); CALL create_new_sequence("my_seq_2", 100);
CALL get_single_sequence_id("my_seq_1"); **** +---------+ | next_id | +---------+ | 0 | +---------+
CALL get_single_sequence_id("my_seq_1"); **** +---------+ | next_id | +---------+ | 1 | +---------+
CALL get_single_sequence_id("my_seq_2"); **** +---------+ | next_id | +---------+ | 100 | +---------+
CALL get_multiple_sequence_id("my_seq_2", 10); **** +----------+ | start_id | +----------+ | 101 | +----------+
CALL get_multiple_sequence_id("my_seq_2", 10); **** +----------+ | start_id | +----------+ | 111 | +----------+