How the SETVAL() function works in Mariadb?

The SETVAL() function in MariaDB is used to set the sequence counter for a specified sequence object to a specific value. It is particularly useful when you want to manually control the next value that will be generated by the sequence.

Syntax

The syntax for the MariaDB SETVAL() function is as follows:

SETVAL(sequence_name, value, [is_called])

• sequence_name: The name of the sequence object whose counter value you want to set.
• value: The new value to be assigned to the sequence counter.
• is_called (optional): A boolean value that indicates whether the SETVAL() function should behave as if the sequence had been called or not. If set to true, the next call to NEXTVAL() will return the value + increment_by value. If set to false (default), the next call to NEXTVAL() will return the value.

The function returns the last value set for the specified sequence.

Examples

Example 1: Setting the sequence counter value

In this example, we’ll create a new sequence object and then use the SETVAL() function to set its counter value.

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE
);

DROP SEQUENCE IF EXISTS order_seq;
CREATE SEQUENCE order_seq;

INSERT INTO orders (order_date) VALUES ('2023-05-01');
INSERT INTO orders (order_date) VALUES ('2023-05-02');

SELECT SETVAL(order_seq, 100, false);

The following is the output:

+-------------------------------+
| SETVAL(order_seq, 100, false) |
+-------------------------------+
|                           100 |
+-------------------------------+

After executing the SETVAL() function, the next value generated by the order_seq sequence will be 100.

Example 2: Setting the sequence counter value and handling the next NEXTVAL() call

In this example, we’ll use the is_called parameter to control the behavior of the next NEXTVAL() call.

SELECT SETVAL(order_seq, 200, true);

The following is the output:

+------------------------------+
| SETVAL(order_seq, 200, true) |
+------------------------------+
|                          200 |
+------------------------------+

使用下面的语句查看下一个值：

SELECT NEXTVAL(order_seq);

+--------------------+
| NEXTVAL(order_seq) |
+--------------------+
|                201 |
+--------------------+

By setting is_called to true, the SETVAL() function behaves as if the sequence had been called, and the next NEXTVAL() call returns value + increment_by (201 in this case).

Related Functions

The following are a few functions related to the MariaDB SETVAL() function:

• MariaDB NEXTVAL() function is used to retrieve the next value from a sequence object.
• MariaDB CREATE SEQUENCE statement is used to create a new sequence object.
• MariaDB ALTER SEQUENCE statement is used to modify the properties of an existing sequence object.
• MariaDB DROP SEQUENCE statement is used to remove a sequence object from the database.

Conclusion

The SETVAL() function in MariaDB provides a way to manually control the counter value of a sequence object. It is useful when you need to reset or initialize the sequence to a specific value, or when you need to handle situations where the sequence values have gotten out of sync. By understanding the syntax and usage of this function, along with other sequence-related functions, you can effectively manage and utilize sequence objects in your MariaDB database.