How the LASTVAL() function works in MariaDB?

The LASTVAL(sequence_name) function in MariaDB returns the most recent value generated from the named sequence in the current connection. Sequences, introduced in MariaDB 10.3, provide counters commonly used for identifiers and document numbering.

Unlike NEXTVAL(), which advances the sequence counter, LASTVAL() reads the current session’s generated value without modifying or incrementing the sequence.

Syntax

The syntax for the MariaDB LASTVAL() function is:

LASTVAL(sequence_name)

The function takes one mandatory argument:

• sequence_name: The name of the sequence object whose last generated value you want to retrieve.

If the sequence has not yet been used by the current connection, LASTVAL(sequence_name) returns NULL. The function is also available as PREVIOUS VALUE FOR sequence_name.

Examples

Example 1: Retrieving the last value of a sequence

In this example, we create a sequence named invoice_seq, advance it using NEXTVAL(), and then inspect the current value using LASTVAL():

DROP SEQUENCE IF EXISTS invoice_seq;
CREATE SEQUENCE invoice_seq START WITH 1000 INCREMENT BY 1;

-- Advance the sequence
SELECT NEXTVAL(invoice_seq);

Output:

+----------------------+
| NEXTVAL(invoice_seq) |
+----------------------+
|                 1000 |
+----------------------+

Now, check the last generated value with LASTVAL():

SELECT LASTVAL(invoice_seq);

Output:

+----------------------+
| LASTVAL(invoice_seq) |
+----------------------+
|                 1000 |
+----------------------+

Calling LASTVAL() multiple times returns 1000 repeatedly without advancing the sequence counter.

Example 2: Behavior before NEXTVAL() is called

If a connection calls LASTVAL() for a sequence before that connection has generated a value from it, MariaDB returns NULL:

DROP SEQUENCE IF EXISTS order_seq;
CREATE SEQUENCE order_seq START WITH 1;

SELECT LASTVAL(order_seq);

Output:

+--------------------+
| LASTVAL(order_seq) |
+--------------------+
|               NULL |
+--------------------+

Example 3: Using LASTVAL() with INSERT statements

When a table uses a sequence for an ID column, you can use LASTVAL() to retrieve the generated ID for child table inserts:

DROP TABLE IF EXISTS orders;
DROP SEQUENCE IF EXISTS order_id_seq;

CREATE SEQUENCE order_id_seq START WITH 500;

CREATE TABLE orders (
    order_id INT PRIMARY KEY DEFAULT (NEXTVAL(order_id_seq)),
    customer_name VARCHAR(100) NOT NULL
);

INSERT INTO orders (customer_name) VALUES ('Acme Corp');

-- Retrieve the ID generated for the newly created order
SELECT LASTVAL(order_id_seq);

Output:

+-----------------------+
| LASTVAL(order_id_seq) |
+-----------------------+
|                   500 |
+-----------------------+

Related Functions

The following functions are closely related to LASTVAL() in MariaDB:

• NEXTVAL(): Advances the sequence and returns the next value.
• SETVAL(): Manually sets the sequence counter to a specified value.
• LAST_INSERT_ID(): Retrieves the most recent AUTO_INCREMENT value generated in the current session.

Conclusion

The LASTVAL(sequence_name) function in MariaDB retrieves the latest value generated from that sequence in the current connection without advancing it. Use it when a later statement in the same connection needs the sequence value. See the MariaDB LASTVAL documentation for details.