NEXT VALUE FOR sequenceNEXTVAL(sequence_name)sequence_name.nextvalLearn 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.
CYCLESEQUENCECREATE 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 |
+------------+PREVIOUS VALUE FOR sequence_nameLASTVAL(sequence_name)sequence_name.currvalCREATE 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 |
+----------------------+$ .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 |
+----------------------+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(sequence_name, next_value, [is_used, [round]])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 42SELECT 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 |
+------------+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 |
+-------------+SELECT SETVAL(s1, 99, 1, 0);
+----------------------+
| SETVAL(s1, 99, 1, 0) |
+----------------------+
| NULL |
+----------------------+
SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
| 2 |
+-------------+SELECT SETVAL(s1, 99, 1, 1);
+----------------------+
| SETVAL(s1, 99, 1, 1) |
+----------------------+
| 99 |
+----------------------+
SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
| 1 |
+-------------+