# NEXT VALUE FOR

## Syntax

```sql
NEXT VALUE FOR sequence
```

or

```sql
NEXTVAL(sequence_name)
```

or in Oracle mode ([SQL\_MODE=ORACLE](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode))

```sql
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 `SEQUENCE`s you have to expect gaps in the generated sequence numbers.
* If one updates the `SEQUENCE` with [SETVAL()](https://mariadb.com/docs/server/reference/sql-structure/sequences/sequence-functions/setval) or [ALTER SEQUENCE ... RESTART](https://mariadb.com/docs/server/reference/sql-structure/sequences/alter-sequence), `NEXT VALUE FOR` will notice this and start from the next requested value.
* [FLUSH TABLES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush) 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](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant).
* 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](https://mariadb.com/docs/server/reference/sql-structure/create-sequence#cycle-nocycle) attribute (not the default), calling the function will result in [Error 4084: Sequence has run out](https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-4000-to-4099/e4084).

## Examples

```sql
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](https://mariadb.com/docs/server/reference/sql-structure/sequences/sequence-overview)
* [CREATE SEQUENCE](https://mariadb.com/docs/server/reference/sql-structure/sequences/create-sequence)
* [ALTER SEQUENCE](https://mariadb.com/docs/server/reference/sql-structure/sequences/alter-sequence)
* [PREVIOUS VALUE FOR](https://mariadb.com/docs/server/reference/sql-structure/sequences/sequence-functions/previous-value-for-sequence_name)
* [SETVAL()](https://mariadb.com/docs/server/reference/sql-structure/sequences/sequence-functions/setval). Set next value for the sequence.
* [AUTO\_INCREMENT](https://mariadb.com/docs/server/reference/data-types/auto_increment)
* [Information Schema SEQUENCES Table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-sequences-table)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-structure/sequences/sequence-functions/next-value-for-sequence_name.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.