or
or in Oracle mode (SQL_MODE=ORACLE)
NEXT VALUE FOR is ANSI SQL syntax while NEXTVAL() is PostgreSQL syntax.
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 SEQUENCE
Once the sequence is complete, unless the sequence has been created with the attribute (not the default), calling the function will result in .
This page is licensed: CC BY-SA / Gnu FDL
NEXT VALUE FOR sequenceNEXTVAL(sequence_name)sequence_name.nextvalIf 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.
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 |
+------------+