# Sequence Overview

This page is about sequence objects. For details about the storage engine, see [Sequence Storage Engine](https://mariadb.com/docs/server/server-usage/storage-engines/sequence-storage-engine).

## Introduction

A sequence is an object that generates a sequence of numeric values, as specified by the [CREATE SEQUENCE](https://mariadb.com/docs/server/reference/sql-structure/sequences/create-sequence) statement.

`CREATE SEQUENCE` creates a sequence that generates new values when called with `NEXT VALUE FOR` sequence\_name. It's an alternative to [AUTO INCREMENT](https://mariadb.com/docs/server/reference/data-types/auto_increment) when one wants to have more control of how the numbers are generated. As the `SEQUENCE` caches values (up to the `CACHE` value in the [CREATE SEQUENCE](https://mariadb.com/docs/server/reference/sql-structure/sequences/create-sequence) statement, by default 1000) it can in some cases be much faster than AUTO INCREMENT. Another benefit is that one can access the last value generated by all used sequences, which solves one of the limitations with [LAST\_INSERT\_ID()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/last_insert_id).

{% hint style="warning" %}
Sequences should not be used with statement-based logging; see [this section for details](#statement-based-replication-limitation).
{% endhint %}

## Creating a Sequence

The [CREATE SEQUENCE](https://mariadb.com/docs/server/reference/sql-structure/sequences/create-sequence) statement is used to create a sequence. Here is an example of a sequence starting at 100, incrementing by 10 each time:

```sql
CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;
```

The CREATE SEQUENCE statement, along with defaults, can be viewd with the [SHOW CREATE SEQUENCE STATEMENT](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-sequence), for example:

```sql
SHOW CREATE SEQUENCE s\G
*************************** 1. row ***************************
       Table: s
Create Table: CREATE SEQUENCE `s` start with 100 minvalue 1 maxvalue 9223372036854775806 
  increment by 10 cache 1000 nocycle ENGINE=InnoDB
```

## Using Sequence Objects

To get the [next value from a sequence](https://mariadb.com/docs/server/reference/sql-structure/sequences/sequence-functions/next-value-for-sequence_name), use

```sql
NEXT VALUE FOR sequence_name
```

or

```sql
NEXTVAL(sequence_name)
```

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

```
sequence_name.nextval
```

For [retrieving the last value](https://mariadb.com/docs/server/reference/sql-structure/sequences/sequence-functions/previous-value-for-sequence_name) used by the current connection from a sequence use:

```sql
PREVIOUS VALUE FOR sequence_name
```

or

```sql
LASTVAL(sequence_name)
```

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

```
sequence_name.currval
```

For example:

```sql
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        100 |
+------------+

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

SELECT LASTVAL(s);
+------------+
| LASTVAL(s) |
+------------+
|        110 |
+------------+
```

### Using Sequences in DEFAULT

Sequences can be used in `DEFAULT`:

```sql
CREATE SEQUENCE s1;
CREATE TABLE t1 (a INT PRIMARY KEY DEFAULT (NEXT VALUE FOR s1), b INT);
INSERT INTO t1 (b) VALUES (1),(2);
SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+
```

## Changing a Sequence

The [ALTER SEQUENCE](https://mariadb.com/docs/server/reference/sql-structure/sequences/alter-sequence) statement is used for changing sequences. For example, to restart the sequence at another value:

```sql
ALTER SEQUENCE s RESTART 50;

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

The [SETVAL function](https://mariadb.com/docs/server/reference/sql-structure/sequences/sequence-functions/setval) can also be used to set the next value to be returned for a `SEQUENCE`, for example:

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

`SETVAL` can only be used to increase the sequence value. Attempting to set a lower value will fail, returning `NULL`:

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

## Dropping a Sequence

The [DROP SEQUENCE](https://mariadb.com/docs/server/reference/sql-structure/sequences/drop-sequence) statement is used to drop a sequence, for example:

```sql
DROP SEQUENCE s;
```

## Replication

If you want to use Sequences in a master-master setup or with Galera, you should use `INCREMENT=0`. This tells the Sequence to use [auto\_increment\_increment](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables) and [auto\_increment\_offset](https://mariadb.com/docs/server/ha-and-performance/standard-replication/replication-and-binary-log-system-variables) to generate unique values for each server.

## Statement-Based Replication Limitation

Using `SELECT NEXT VALUE` causes an error for statement-based logging:

```
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
```

This happens because `SELECT` modifies objects.

## Standards Compliance

MariaDB supports both ANSI SQL and Oracle syntax for sequences.

However as `SEQUENCE` is implemented as a special kind of table, it uses the same namespace as tables. The benefits are that sequences show up in [SHOW TABLES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-tables), and one can also create a sequence with [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) and drop it with [DROP TABLE](https://mariadb.com/docs/server/server-usage/tables/drop-table). One can [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) from it as from any other table. This ensures that all old tools that work with tables should work with sequences.

Since sequence objects act as regular tables in many contexts, they will be affected by [LOCK TABLES](https://mariadb.com/docs/server/reference/sql-statements/transactions/lock-tables). This is not the case in other DBMS, such as Oracle, where LOCK TABLE does not affect sequences.

## Notes

One of the goals with the Sequence implementation is that all old tools, such as [mariadb-dump](https://mariadb.com/docs/server/clients-and-utilities/backup-restore-and-import-clients/mariadb-dump) (previously mysqldump), should work unchanged, while still keeping the normal usage of sequence standard compatibly.

To make this possible, `sequence` is currently implemented as a table with a few exclusive properties.

The special properties for sequence tables are:

* A sequence table has always one row.
* When one creates a sequence, either with [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) or [CREATE SEQUENCE](https://mariadb.com/docs/server/reference/sql-structure/sequences/create-sequence), one row will be inserted.
* If one tries to insert into a sequence table, the single row will be updated. This allows [mariadb-dump](https://mariadb.com/docs/server/clients-and-utilities/backup-restore-and-import-clients/mariadb-dump) to work but also gives the additional benefit that one can change all properties of a sequence with a single insert. New applications should of course also use `ALTER SEQUENCE`.
* [UPDATE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update) or [DELETE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete) can't be performed on Sequence objects.
* Doing a select on the sequence shows the current state of the sequence, except the values that are reserved in the cache. The `next_value` column shows the next value not reserved by the cache.
* [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 number of normal table operations work on Sequence tables. See next section.

## Table Operations that Work with Sequences

* [SHOW CREATE TABLE sequence\_name](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-table). This shows the table structure that is behind the `SEQUENCE` including the field names that can be used with [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) or even [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table).
* [CREATE TABLE sequence-structure ... SEQUENCE=1](https://mariadb.com/docs/server/server-usage/tables/create-table)
* [ALTER TABLE sequence RENAME TO sequence2](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table)
* [RENAME TABLE sequence\_name TO new\_sequence\_name](https://mariadb.com/docs/server/reference/sql-statements/data-definition/rename-table)
* [DROP TABLE sequence\_name](https://mariadb.com/docs/server/server-usage/tables/drop-table). This is allowed mainly to get old tools like [mariadb-dump](https://mariadb.com/docs/server/clients-and-utilities/backup-restore-and-import-clients/mariadb-dump) to work with sequence tables.
* [SHOW TABLES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-tables)

## Implementation

Internally, sequence tables are created as a normal table without rollback (the [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb), [Aria](https://mariadb.com/docs/server/server-usage/storage-engines/aria) and [MySAM](https://mariadb.com/docs/server/server-usage/storage-engines/myisam-storage-engine) engines support this), wrapped by a sequence engine object. This allowed us to create sequences with\
almost no performance impact for normal tables. (The cost is one 'if' per insert if the [binary log](https://mariadb.com/docs/server/server-management/server-monitoring-logs/binary-log) is enabled).

## Underlying Table Structure

The following example shows the table structure of sequences and how it can be used as a table.\
(Output of results are slightly edited to make them easier to read.)

```sql
CREATE SEQUENCE t1;
SHOW CREATE SEQUENCE t1\G
*************************** 1. row ***************************
  CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806
  increment by 1 cache 1000 nocycle ENGINE=InnoDB

SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `t1` (
  `next_not_cached_value` bigint(21) NOT NULL,
  `minimum_value` bigint(21) NOT NULL,
  `maximum_value` bigint(21) NOT NULL,
  `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
  `increment` bigint(21) NOT NULL COMMENT 'increment value',
  `cache_size` bigint(21) unsigned NOT NULL,
  `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
  `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=InnoDB SEQUENCE=1

SELECT * FROM t1\G
next_not_cached_value: 1
 minimum_value: 1
 maximum_value: 9223372036854775806
  start_value: 1
  increment: 1
  cache_size: 1000
  cycle_option: 0
  cycle_count: 0
```

The `cycle_count` column is incremented every time the sequence wraps around.

## Credits

* Thanks to Jianwe Zhao from Aliyun for his work on `SEQUENCE` in AliSQL, which gave ideas and inspiration for this work.
* Thanks to Peter Gulutzan, who helped test and gave useful comments about the implementation.

## See Also

* [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)
* [DROP SEQUENCE](https://mariadb.com/docs/server/reference/sql-structure/sequences/drop-sequence)
* [NEXT VALUE FOR](https://mariadb.com/docs/server/reference/sql-structure/sequences/sequence-functions/next-value-for-sequence_name)
* [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)
* [Sequence Storage Engine](https://mariadb.com/docs/server/server-usage/storage-engines/sequence-storage-engine)
* [Information Schema SEQUENCES Table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-sequences-table)
* [Error 4084: Sequence has run out](https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-4000-to-4099/e4084)

<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-overview.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.