LAST_ INSERT_ ID
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
Returns the last value inserted into an AUTO_
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
BIGINT
Remarks
-
LAST_
only returns automatically generatedINSERT_ ID() AUTO_
values.INCREMENT The value of LAST_
remains unchanged if you set theINSERT_ ID() AUTO_
column of a row to an explicit or non-automatically generated value.INCREMENT -
LAST_
is maintained on a per-connection basis, allowing multiple clients to execute theINSERT_ ID UPDATE
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_
is the first value inserted in that group of records.INSERT_ ID() -
The value of
LAST_
remains unchanged if no record is successfully added in anINSERT_ ID() INSERT
statement. -
While using
INSERT IGNORE
if the row is ignored, the value ofLAST_
remains unchanged.INSERT_ ID -
If the value of
LAST_
is changed inside a stored procedure, the changed value is seen by statements executed after the procedure call.INSERT_ ID -
If the value of
LAST_
is changed inside a stored function, the value is restored when the function ends.INSERT_ ID -
If
expr
is specified withLAST_
,INSERT_ ID LAST_
returns the value of the expression expr and stores this value as the next value to be returned byINSERT_ ID(expr) LAST_
.INSERT_ ID() -
LAST_
works withINSERT_ ID UPDATE
/INSERT
statements, but it may not work as expected withSELECT
statements because of the distributed nature of SingleStore.
Note
LAST_
is only updated after INSERT
and UPDATE
queries.LAST_
cannot be used to retrieve the value for LAST_
after executing other SQL statements, like SELECT
or SET
with LAST_
.
Examples
In the following example, LAST_
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_
was changed since a NULL
value was inserted in the AUTO_
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_
.
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 sequencesSET 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)ASBEGININSERT INTO sequence_generator VALUES (sequence_name, starting_value);END //-- The following stored procedure returns a single ID from the sequence generator from a particular instanceCREATE OR REPLACE PROCEDURE get_single_sequence_id (sequence_name TEXT NOT NULL)ASBEGINUPDATE 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 idCREATE OR REPLACE PROCEDURE get_multiple_sequence_id (sequence_name TEXT NOT NULL,num_ids INT NOT NULL)ASBEGINUPDATE 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 |
+----------+
Last modified: June 28, 2024