How the NEXTVAL() function works in Mariadb?

Syntax

The syntax of the NEXTVAL() function is as follows:

NEXTVAL(sequence_name)

The function takes one argument:

sequence_name: The name of the sequence to get the next value from. This argument is mandatory.

The function returns the next value of the sequence, and also increments the sequence by one. If the sequence does not exist, the function returns NULL.

Examples

Example 1: Creating and using a sequence

In this example, we create a sequence named my_seq and use the NEXTVAL() function to get the next value of the sequence. We use the SELECT statement to display the result.

-- Create a sequence named my_seq
CREATE SEQUENCE my_seq;

-- Use NEXTVAL() to get the next value of the sequence
SELECT NEXTVAL(my_seq) AS next_val;

The output is:

+----------+
| next_val |
+----------+
|        1 |
+----------+

The CREATE SEQUENCE statement creates a sequence named my_seq with the default parameters. The sequence starts from 1, increments by 1, and has no maximum or minimum value. The NEXTVAL() function returns the next value of the sequence, which is 1, and also increments the sequence by 1.

Example 2: Using NEXTVAL() multiple times

In this example, we use the NEXTVAL() function multiple times to get the next values of the sequence my_seq. We use the SELECT statement to display the result.

-- Use NEXTVAL() to get the next value of the sequence
SELECT NEXTVAL(my_seq) AS next_val;

The output is:

+----------+
| next_val |
+----------+
|        2 |
+----------+

The NEXTVAL() function returns the next value of the sequence, which is 2, and also increments the sequence by 1.

-- Use NEXTVAL() to get the next value of the sequence
SELECT NEXTVAL(my_seq) AS next_val;

The output is:

+----------+
| next_val |
+----------+
|        3 |
+----------+

The NEXTVAL() function returns the next value of the sequence, which is 3, and also increments the sequence by 1.

Example 3: Using NEXTVAL() with a custom sequence

In this example, we create a sequence named my_custom_seq with some custom parameters and use the NEXTVAL() function to get the next value of the sequence. We use the SELECT statement to display the result.

-- Create a sequence named my_custom_seq with some custom parameters
CREATE SEQUENCE my_custom_seq
  START WITH 100
  INCREMENT BY 10
  MINVALUE 50
  MAXVALUE 200
  CYCLE;

-- Use NEXTVAL() to get the next value of the sequence
SELECT NEXTVAL(my_custom_seq) AS next_val;

The output is:

+----------+
| next_val |
+----------+
|      100 |
+----------+

The CREATE SEQUENCE statement creates a sequence named my_custom_seq with some custom parameters. The sequence starts from 100, increments by 10, and has a minimum value of 50 and a maximum value of 200. The sequence also cycles, which means that when it reaches the maximum value, it restarts from the minimum value. The NEXTVAL() function returns the next value of the sequence, which is 100, and also increments the sequence by 10.

There are some other functions that are related to the NEXTVAL() function, such as:

SETVAL(): This function sets the current value of the sequence to a specified value. For example:
```
-- Use SETVAL() to set the current value of the sequence to 10
SELECT SETVAL(my_seq, 10) AS set_val;
```
The output is:
```
+---------+
| set_val |
+---------+
|      10 |
+---------+
```
The SETVAL() function sets the current value of the sequence to 10, and returns the same value.

Conclusion

The NEXTVAL() function is a sequence function that returns the next value of a sequence. A sequence is a database object that generates a series of numeric values, according to some rules. Sequences are useful for generating unique identifiers, such as primary keys, or for creating counters, such as page numbers. Mariadb supports sequences since version 10.3. The function returns the next value of the sequence, and also increments the sequence by one. If the sequence does not exist, the function returns NULL. There are some other functions that are related to the NEXTVAL() function, such as LASTVAL(), SETVAL(), and CREATE SEQUENCE. These functions can help us to work with sequences in Mariadb.

Syntax

Examples

Example 1: Creating and using a sequence

Example 2: Using NEXTVAL() multiple times

Example 3: Using NEXTVAL() with a custom sequence

Related Functions

Conclusion