LAST_INSERT_ID

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_INSERT_ID() only returns automatically generated AUTO_INCREMENT values. The value of LAST_INSERT_ID() remains unchanged if you set the AUTO_INCREMENT column of a row to an explicit or non-automatically generated value.

• LAST_INSERT_ID is maintained on a per-connection basis, allowing multiple clients to execute the UPDATE statement and generate their own sequence with the SELECT 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 an INSERT statement.

• While using INSERT IGNORE if the row is ignored, the value of LAST_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 with LAST_INSERT_ID, LAST_INSERT_ID(expr) returns the value of the expression expr and stores this value as the next value to be returned by LAST_INSERT_ID().

• LAST_INSERT_ID works with UPDATE/INSERT statements, but it may not work as expected with SELECT statements because of the distributed nature of SingleStore.

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.

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 |
+----------+