Understanding LASTVAL in MariaDB for Sequence Management

Sequences in MariaDB

• When you insert data into a table that references a sequence, the next value in the sequence is automatically assigned.
• You can create sequences using the CREATE SEQUENCE statement.
• Sequences are objects that generate an increasing (or decreasing) series of numbers.

LASTVAL Function

• This means it only reflects the value generated within your current session, not globally across all connections.
• The LASTVAL function allows you to retrieve the last value generated by a sequence within the current connection.

Using LASTVAL

• LASTVAL is typically used after you've inserted data that references a sequence and you want to know the specific value assigned.

CREATE SEQUENCE my_sequence START 1 INCREMENT BY 1;

INSERT INTO my_table (id) VALUES (NEXTVAL(my_sequence));

SELECT LASTVAL() AS last_inserted_id;

In this example:

1. We create a sequence named my_sequence that starts at 1 and increments by 1.
2. We insert a record into my_table with the id column referencing NEXTVAL(my_sequence), which will automatically assign the next value (1) from the sequence.
3. Finally, we use SELECT LASTVAL() to retrieve the last generated value, which will be 1 in this case.

• It's useful for retrieving the specific value assigned during an insert operation that references a sequence.
• LASTVAL only reflects the value within the current connection.

---

Inserting Multiple Rows and Retrieving Last Inserted ID

CREATE SEQUENCE order_id_seq START 100 INCREMENT BY 1;

INSERT INTO orders (customer_id, order_date) VALUES (1, CURDATE());
INSERT INTO orders (customer_id, order_date) VALUES (2, CURDATE());

SELECT LASTVAL() AS last_order_id;

This example:

1. Creates a sequence named order_id_seq for order IDs, starting at 100 and incrementing by 1.
2. Inserts two rows into the orders table with different customer IDs and the current date.
3. Uses LASTVAL() to retrieve the ID assigned to the second order (assuming both inserts ran successfully).

Updating Sequence Value

CREATE SEQUENCE product_id_seq START 50;

SETVAL product_id_seq, 75;  -- Reset sequence value to 75

INSERT INTO products (name, price) VALUES ('Headphones', 100.00);

SELECT LASTVAL() AS last_product_id;

1. Creates a sequence named product_id_seq starting at 50.
2. Uses SETVAL product_id_seq, 75 to explicitly set the sequence's current value to 75 (useful for managing gaps or synchronization).
3. Inserts a record into the products table referencing the sequence.
4. Uses LASTVAL() to retrieve the ID assigned to the product, which will be 75.

CREATE SEQUENCE user_seq START 1000;

DECLARE new_user_id INT;

INSERT INTO users (username, email) VALUES ('john.doe', '[email protected]');

SET new_user_id = LASTVAL();

SELECT new_user_id AS user_id;

1. Creates a sequence named user_seq starting at 1000.
2. Declares a user-defined variable new_user_id to store the retrieved ID.
3. Inserts a record into the users table referencing the sequence.
4. Uses LASTVAL() to assign the last generated ID to the variable new_user_id.
5. Finally, selects the value stored in the variable, effectively retrieving the last inserted user ID.

---

1. PREVIOUS VALUE FOR sequence_name
   

• LASTVAL is actually a synonym for PREVIOUS VALUE FOR sequence_name. Both functions achieve the same purpose of retrieving the last generated value within the current connection.

1. NEXT VALUE FOR sequence_name - 1
   

• This approach utilizes the NEXT VALUE FOR sequence_name function, which retrieves the next value in the sequence. However, we subtract 1 from the retrieved value to effectively get the last generated value.

Function	Description
LASTVAL / PREVIOUS VALUE	Retrieves the last value generated by the sequence
NEXT VALUE FOR ... - 1	Retrieves the next value and subtracts 1 to get last

Choosing the Right Option

• Using NEXT VALUE - 1 might be less intuitive and could be prone to errors if not carefully implemented. However, it can be a viable alternative if you need the code to explicitly retrieve the next value while also calculating the last one.
• LASTVAL or PREVIOUS VALUE is generally the preferred choice due to readability and being the intended function for this purpose.

• For ensuring strict sequential order of insertions across multiple connections, consider using transactions or triggers instead of relying solely on LASTVAL.
• Remember that both LASTVAL and PREVIOUS VALUE only reflect the value within the current connection, not globally across all connections to the database.