SETVAL | Syntax: | SETVAL(Sequence, Value [, Reserved] [, Cycle]) | Return value: | INTEGER | Function type: | Sequence function | |
|
|
Sets and returns the next "Value" to be returned from a "Sequence".
If the sequence "Value" is invalid, SETVAL() returns NULL.
If "Reserved" is true or 1, the following NEXTVAL() returns the next value after the set "Value". This is the default.
If "Reserved" is false or 0, the following NEXTVAL() returns the value, set with "Value".
If the sequence is cyclically, the "Cycle" can be increased with SETVAL().
Without a "Cycle", the "Value" to set must be greater than the current value in the sequence.
With cycle, either the "Value" to set must be greater than the current value in the sequence, or the cycle must be increased.
Available from MariaDB version 10.3. |
Example 1: SETVAL() and NEXTVAL() without specifying "Reserved". |
|
select setval(`my_sequence`,60) as result;
select nextval(`my_sequence`) as result;
|
|
|
Example 2: SETVAL() and NEXTVAL() specifying "Reserved" = true. |
|
select setval(`my_sequence`,60,true) as result;
select nextval(`my_sequence`) as result;
|
|
|
Example 3: SETVAL() and NEXTVAL() specifying "Reserved" = false. |
|
select setval(`my_sequence`,60,false) as result;
select nextval(`my_sequence`) as result;
|
|
|
Example 4: Returning of NULL if "Value" is less than the current value in the sequence. |
|
select setval(`my_sequence`,60,false) as result;
|
|
|
Example 5: SETVAL() and NEXTVAL() with increment of "Cycle". |
|
select setval(`my_sequence`,1,false,2) as result;
select nextval(`my_sequence`) as result;
|
|
|
The examples were created with the MyWAY SQL manager: |
How to use the SETVAL() function in MariaDB databases |
In MariaDB the SETVAL() function is used to manually set the current value of a sequence object to a new specified value. Passing in the name of the sequence object along with the desired new value, the function returns the newly set value, which can then be used for confirmation or further processing. This is used when the sequence needs to be reset to a specific value or synchronized with other data. Changing the value of a sequence can affect the uniqueness of the generated values, so you should check whether the new value also meets the requirements. |
|
Further MySQL und MariaDB SQL Sequence functions |
|