1 of 6

SEQUENCE Functions

Learn about sequence functions in MariaDB Server. This section details SQL functions for retrieving the next or current value from a sequence, crucial for generating unique identifiers.

LASTVAL

LASTVAL is a synonym for PREVIOUS VALUE for sequence_name.

_{This page is licensed: CC BY-SA / Gnu FDL}

NEXT VALUE for sequence_name

Syntax

NEXT VALUE FOR sequence

NEXTVAL(sequence_name)

or in Oracle mode (SQL_MODE=ORACLE)

sequence_name.nextval

NEXT VALUE FOR is ANSI SQL syntax while NEXTVAL() is PostgreSQL syntax.

Description

Generate next value for a SEQUENCE.

You can greatly speed up NEXT VALUE by creating the sequence with the CACHE option. If not, every NEXT VALUE usage will cause changes in the stored SEQUENCE table.
When using NEXT VALUE the value will be reserved at once and will not be reused, except if the SEQUENCE was created with CYCLE. This means that when you are using SEQUENCEs you have to expect gaps in the generated sequence numbers.
If one updates the SEQUENCE with SETVAL() or ALTER SEQUENCE ... RESTART, NEXT VALUE FOR will notice this and start from the next requested value.
FLUSH TABLES will close the sequence and the next sequence number generated will be according to what's stored in the SEQUENCE object. In effect, this will discard the cached values.
A server restart (or closing the current connection) also causes a drop of all cached values. The cached sequence numbers are reserved only for the current connection.
NEXT VALUE requires the INSERT privilege.
You can also use NEXT VALUE FOR sequence for column DEFAULT.

Once the sequence is complete, unless the sequence has been created with the CYCLE attribute (not the default), calling the function will result in Error 4084: Sequence has run out.

Examples

CREATE OR REPLACE SEQUENCE s MAXVALUE=2;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          1 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          2 |
+------------+

SELECT NEXTVAL(s);
ERROR 4084 (HY000): Sequence 'test.s' has run out

ALTER SEQUENCE s MAXVALUE=2 CYCLE;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|          1 |
+------------+

NEXTVAL

NEXTVAL is a synonym for NEXT VALUE for sequence_name.

_{This page is licensed: CC BY-SA / Gnu FDL}

PREVIOUS VALUE FOR sequence_name

Syntax

PREVIOUS VALUE FOR sequence_name

LASTVAL(sequence_name)

or in Oracle mode (SQL_MODE=ORACLE)

sequence_name.currval

PREVIOUS VALUE FOR is IBM DB2 syntax while LASTVAL() is PostgreSQL syntax.

Description

Gets the most recent value in the current connection generated from a sequence.

If the sequence has not yet been used by the connection, PREVIOUS VALUE FOR returns NULL (the same thing applies with a new connection which doesn't see a last value for an existing sequence).
If a SEQUENCE has been dropped and re-created then it's treated as a new SEQUENCE and PREVIOUS VALUE FOR will return NULL.
Returns NULL if the sequence is complete.
FLUSH TABLES has no effect on PREVIOUS VALUE FOR.
Previous values for all used sequences are stored per connection until connection ends.
PREVIOUS VALUE FOR requires the SELECT privilege.

Examples

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;

SELECT PREVIOUS VALUE FOR s;
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
|                 NULL |
+----------------------+

# The function works for sequences only, if the table is used an error is generated
SELECT PREVIOUS VALUE FOR t;
ERROR 4089 (42S02): 'test.t' is not a SEQUENCE

# Call the NEXT VALUE FOR s:
SELECT NEXT VALUE FOR s;
+------------------+
| NEXT VALUE FOR s |
+------------------+
|              100 |
+------------------+

SELECT PREVIOUS VALUE FOR s;
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
|                  100 |
+----------------------+

Now try to start the new connection and check that the last value is still NULL, before updating the value in the new connection after the output of the new connection gets current value (110 in the example below). Note that first connection cannot see this change and the result of last value still remains the same (100 in the example above).

$ .mysql -uroot test -e"SELECT PREVIOUS VALUE FOR s; SELECT NEXT VALUE FOR s; SELECT PREVIOUS VALUE FOR s;"
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
|                 NULL |
+----------------------+
+------------------+
| NEXT VALUE FOR s |
+------------------+
|              110 |
+------------------+
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
|                  110 |
+----------------------+

Returns NULL if the sequence has run out:

CREATE OR REPLACE SEQUENCE s MAXVALUE=2;

SELECT NEXTVAL(s), LASTVAL(s);
+------------+------------+
| NEXTVAL(s) | LASTVAL(s) |
+------------+------------+
|          1 |          1 |
+------------+------------+

SELECT NEXTVAL(s), LASTVAL(s);
+------------+------------+
| NEXTVAL(s) | LASTVAL(s) |
+------------+------------+
|          2 |          2 |
+------------+------------+

SELECT NEXTVAL(s), LASTVAL(s);
ERROR 4084 (HY000): Sequence 'test.s' has run out

SELECT LASTVAL(s);
+------------+
| LASTVAL(s) |
+------------+
|       NULL |
+------------+

SETVAL

Syntax

SETVAL(sequence_name, next_value, [is_used, [round]])

Description

Set the next value to be returned for a SEQUENCE.

This function is compatible with PostgreSQL syntax, extended with the round argument.

If the is_used argument is not given or is 1 or true, then the next used value will one after the given value. If is_used is 0 or false then the next generated value will be the given value.

If round is used then it will set the round value (or the internal cycle count, starting at zero) for the sequence. If round is not used, it's assumed to be 0.

next_value must be an integer literal.

For SEQUENCE tables defined with CYCLE (see CREATE SEQUENCE) one should use both next_value and round to define the next value. In this case the current sequence value is defined to be round, next_value.

The result returned by SETVAL() is next_value or NULL if the given next_value and round is smaller than the current value.

SETVAL() will not set the SEQUENCE value to a something that is less than its current value. This is needed to ensure that SETVAL() is replication-safe. If you want to set the SEQUENCE to a smaller number, use ALTER SEQUENCE.

If CYCLE is used, first round and then next_value are compared to see if the value is bigger than the current value.

Internally, in the MariaDB server, SETVAL() is used to inform replicas that a SEQUENCE has changed value. The replica may getSETVAL() statements out of order, but this is ok as only the biggest one will have an effect.

SETVAL requires the INSERT privilege.

Examples

SELECT setval(foo, 42);           -- Next nextval will return 43
SELECT setval(foo, 42, TRUE);     -- Same as above
SELECT setval(foo, 42, FALSE);    -- Next nextval will return 42

SETVAL setting higher and lower values on a sequence with an increment of 10:

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|         50 |
+------------+

SELECT SETVAL(s, 100);
+----------------+
| SETVAL(s, 100) |
+----------------+
|            100 |
+----------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        110 |
+------------+

SELECT SETVAL(s, 50);
+---------------+
| SETVAL(s, 50) |
+---------------+
|          NULL |
+---------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        120 |
+------------+

Example demonstrating round:

CREATE OR REPLACE SEQUENCE s1
  START WITH 1
  MINVALUE 1
  MAXVALUE 99
  INCREMENT BY 1 
  CACHE 20 
  CYCLE;

SELECT SETVAL(s1, 99, 1, 0);
+----------------------+
| SETVAL(s1, 99, 1, 0) |
+----------------------+
|                   99 |
+----------------------+

SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
|           1 |
+-------------+

The following statement returns NULL, as the given next_value and round is smaller than the current value:

SELECT SETVAL(s1, 99, 1, 0);
+----------------------+
| SETVAL(s1, 99, 1, 0) |
+----------------------+
|                 NULL |
+----------------------+

SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
|           2 |
+-------------+

Increasing the round from zero to 1 allows next_value to be returned:

SELECT SETVAL(s1, 99, 1, 1);
+----------------------+
| SETVAL(s1, 99, 1, 1) |
+----------------------+
|                   99 |
+----------------------+

SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
|           1 |
+-------------+

SEQUENCE Functions

LASTVAL

NEXT VALUE for sequence_name

Syntax

Description

Examples

See Also

NEXTVAL

PREVIOUS VALUE FOR sequence_name

Syntax

Description

Examples

See Also

SETVAL

Syntax

Description

Examples

See Also