CREATE SEQUENCE|V4.1.0| docs|Distributed Database

CREATE SEQUENCE

Last Updated：2023-08-01 06:02:28 Updated

Purpose

You can use this statement to create a sequence. A sequence is a database object. You can generate a unique integer from a sequence.

If two users increment the same sequence concurrently, the sequence numbers obtained by the two users have an interval. One user can never obtain the sequence number generated by the other user. Sequence numbers are generated independently of the table. Therefore, you can use the same sequence for multiple tables.

After creating a sequence, you can use the CURRVAL pseudocolumn to return the current sequence value or use the NEXTVAL pseudocolumn to return the new increment value in the SQL statement. For more information, see Sequence pseudocolumns.

Syntax

CREATE SEQUENCE [ IF NOT EXISTS ] [ schema. ] sequence_name
{ START WITH int_value
|[ INCREMENT BY int_value ]
|[ MINVALUE int_value | NOMINVALUE ]
|[ MAXVALUE int_value | NOMAXVALUE ]  
|[ CACHE int_value | NOCACHE ]
|[ ORDER | NOORDER ]
|[ CYCLE | NOCYCLE ]  
}
;

Parameters

Parameter	Description
schema.	The schema where the sequence to be created resides. If this parameter is not specified, the database creates the sequence in the schema of the current user.
sequence_name	The name of the sequence to be created.
IF NOT EXISTS	If the sequence name already exists and `IF NOT EXISTS` is not specified, an error is returned.
MINVALUE int_value	The minimum value of the sequence. Value range: [-(10²⁷-1), (10²⁷-1)].
NOMINVALUE	`NOMINVALUE` is the default value. If `NOMINVALUE` is specified, the minimum value is 1 for an ascending sequence and -(10²⁷-1) for a descending sequence.
MAXVALUE int_value	The maximum value of the sequence. Value range: [(-10²⁷+1), (10²⁸-1)]. Notice When `MINVALUE` and `MAXVALUE` are both specified, `MINVALUE` must be smaller than `MAXVALUE`. The difference between `MAXVALUE` and `MINVALUE` must be greater than or equal to `INCREMENT BY int_value`.
NOMAXVALUE	`NOMAXVALUE` is the default value. If `NOMAXVALUE` is specified, the maximum value is (10²⁸-1 for an ascending sequence and -1 for a descending sequence.
START WITH int_value	The start value of the sequence. `int_value` must be smaller than or equal to `MAXVALUE`, and greater than or equal to `MINVALUE`. If no start value is specified, the start value is the minimum value for an ascending sequence and the maximum value for a descending sequence.
INCREMENT BY int_value	The increment step of the sequence. `int_value` cannot be 0. If the value is positive, the sequence ascends. If the value is negative, the sequence descends. If this parameter is not specified, the default value is 1.
CACHE int_value	The number of sequence values pre-allocated in the memory. By default, `int_value` is 20. The value of `CACHE int_value` must be greater than 1. If the value of `CACHE int_value` is 1, it is equivalent to `NOCACHE`.
NOCACHE	Specifies that no sequence values are pre-allocated. If neither `CACHE` nor `NOCACHE` is specified, the database caches 20 sequence values by default.
ORDER	Specifies that sequence values are generated in order.
NOORDER	`NOORDER` is the default value, which means it is not assured that sequence values are generated in order.
CYCLE	Specifies that sequence values are generated cyclically. That is, when the sequence reaches its maximum or minimum value, new sequence values will also be generated. If the sequence ascends and reaches its maximum value, the minimum sequence value will be generated. If the sequence descends and reaches its minimum value, the maximum sequence value will be generated. Notice If `CYCLE` is specified and the value of `INCREMENT BY int_value` is smaller than 0, `MINVALUE` must be specified. If `CYCLE` is specified, the number of values in the cache cannot be greater than the number of values in the cycle.
NOCYCLE	`NOCYCLE` is the default value. If it is specified, no more values are generated when the sequence reaches its maximum or minimum value.

Examples

Create a sequence named seq1 in the test schema and set the increment step to 2. In the following example, 1 is returned for the first reference to seq1.nextval, and 3 is returned for the second reference. The return value of each subsequent reference is greater than that of the previous reference by 2.

obclient> CREATE SEQUENCE test.seq1 START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
Query OK, 0 rows affected

obclient> SELECT seq1.nextval FROM DUAL;
+--------+
| nextval|
+--------+
| 1      |
+--------+
1 row in set

obclient> SELECT seq1.nextval FROM DUAL;
+--------+
| nextval|
+--------+
| 3      |
+--------+
1 row in set