NEXT VALUE for sequence_name
Syntax
NEXT VALUE FOR sequence
or
NEXTVAL(sequence_name)
or in Oracle mode (SQL_MODE=ORACLE)
sequence_name.nextval
Contents
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 theCACHE
option. If not, everyNEXT VALUE
usage will cause changes in the storedSEQUENCE
table. - When using
NEXT VALUE
the value will be reserved at once and will not be reused, except if theSEQUENCE
was created withCYCLE
. This means that when you are usingSEQUENCE
s 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 theINSERT
privilege.- You can also use
NEXT VALUE FOR sequence
for columnDEFAULT
.
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 | +------------+
See Also
- Sequence Overview
- CREATE SEQUENCE
- ALTER SEQUENCE
- PREVIOUS VALUE FOR
- SETVAL(). Set next value for the sequence.
- AUTO_INCREMENT
- Information Schema SEQUENCES Table
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.