Manage sequences

This topic describes how to create, modify, and drop sequences in MySQL mode.

In OceanBase Database, a sequence is an auto-increment sequence number generated in the database based on some rules. It is usually a set of numeric values (of the numeric data type). A sequence auto-increments and therefore is often used as a primary or unique key.

A sequence provides two pseudo-columns: CURRVAL and NEXTVAL, which return the current sequence value and the next sequence value respectively. Each reference to NEXTVAL will update the value of CURRVAL.

To use a sequence, you must qualify CURRVAL and NEXTVAL with the name of the sequence followed by a period (.).

For example, if the name of the sequence is SEQ_FOO, you can use SEQ_FOO.CURRVAL to query the current value of the SEQ_FOO sequence. Likewise, you can use SEQ_FOO.NEXTVAL to query the next value of the SEQ_FOO sequence.

Create a sequence

You can use the CREATE SEQUENCE statement to create a sequence. SQL syntax:

CREATE SEQUENCE [database_name.]sequence_name
[MINVALUE int_value | NOMINVALUE]
[MAXVALUE int_value | NOMAXVALUE]
[START WITH int_value]
[INCREMENT BY int_value]
[CACHE int_value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];

Statement usage notes:

• To execute this statement, you must have the CREATE privilege.

• database_name.: specifies the database in which the sequence resides. If you do not specify database_name., the sequence is created in the current logon database by default.

• MINVALUE int_value | NOMINVALUE: specifies the minimum value of the sequence. The value range is [-10²⁷, (10²⁷-1)].

  If NOMINVALUE is specified, the minimum value is 1 for an ascending sequence and -(10²⁷-1) for a descending sequence.

  If MINVALUE is not specified, NOMINVALUE takes effect by default.

• MAXVALUE int_value | NOMAXVALUE: specifies the maximum value of the sequence. The value range is [(-10²⁷+1), (10²⁸-1)].

  If NOMAXVALUE is specified, the maximum value is (10²⁸-1) when the sequence ascends, and is -1 when the sequence descends.

  If MAXVALUE is not specified, NOMAXVALUE takes effect by default.

• START WITH: the start value of the sequence. The value must be less 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: the auto-increment step of the sequence. The 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 1 takes effect.

• CACHE: the number of sequence values that the database preallocates and keeps in memory. The default value is 20.

• ORDER | NOORDER: specifies whether the values of the sequence are generated sequentially. ORDER indicates sequential generation, and NOORDER indicates non-sequential generation. The default value is NOORDER.

• CYCLE | NOCYCLE: specifies whether the values of the sequence are cyclically generated. CYCLE indicates a cycling sequence, and NOCYCLE indicates a non-cycling sequence. The default value is NOCYCLE.

Take note of the following rules when you create a sequence:

• When both MINVALUE and MAXVALUE are specified, MINVALUE must be less than MAXVALUE.

• The difference between MAXVALUE and MINVALUE must be greater than or equal to INCREMENT BY int_value.

• The value of CACHE must be greater than 1. If the value of CACHE INT_VALUE is 1, it is equivalent to NOCACHE.

• If CYCLE is specified and the value of INCREMENT BY is less than 0, MINVALUE must be specified.

• If CYCLE is specified, the number of values in the cache must be less than the number of values in the cycle.

The following example creates the sequence seq1 and specify the auto-increment step to 2:

obclient> CREATE SEQUENCE seq1 START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;

Use a sequence

After you create a sequence, you can reference the sequence.

CURRVAL and NEXTVAL values of a sequence can be used in:

• The select list of a SELECT statement.

• The VALUES clause of an INSERT statement.

• The SET clause of an UPDATE statement.

Example:

obclient> SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /*The sequence number increases for each execution.*/

obclient> SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The sequence number remains unchanged for each execution.*/

The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined step size and return a new value. Any reference to CURRVAL always returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.

Before you refer to CURRVAL of a sequence in the session, you must use NEXTVAL to initialize the sequence values first.

When you create a sequence, you can define its initial value and the step size between its values. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined step size and return a new value. Any reference to CURRVAL always returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.

Modify a sequence

After you create a sequence, you can use the ALTER SEQUENCE statement to modify the attributes of the sequence. OceanBase Database allows you to modify the start value, minimum value, maximum value, increment step, and cycling attribute of a sequence. SQL syntax for modifying a sequence:

ALTER SEQUENCE [database_name.]sequence_name
{
[RESTART]
|[START WITH int_value]
|[MINVALUE int_value | NOMINVALUE]
|[MAXVALUE int_value | NOMAXVALUE]
|[INCREMENT BY int_value]
|[CACHE int_value | NOCACHE]
|[ORDER | NOORDER]
|[CYCLE | NOCYCLE]
};

To execute this statement, you must have the ALTER privilege.

Examples:

1. Modify the maximum value of the seq2 sequence and enable cyclic generation of sequence values.

   obclient> ALTER SEQUENCE seq2 MAXVALUE 1024 CYCLE;
   

2. Modify the start value of the sequence.

   • If the value of INCREMENT BY is greater than 0, you can reset the sequence to make the sequence start from MINVALUE. If the value of INCREMENT BY is less than 0, you can execute the following statement to reset the sequence to make the sequence start from MAXVALUE:

     obclient> ALTER SEQUENCE seq3 RESTART;
     Query OK, 0 rows affected
     

   • Execute the following statement to reset the sequence and make the sequence start from 3:

     obclient> ALTER SEQUENCE seq4 RESTART START WITH 3;
     Query OK, 0 rows affected
     

   When you modify the start value of a sequence, RESTART is not necessarily placed after the sequence name. It can be placed anywhere in the statement, as shown in the following example:

   obclient> ALTER SEQUENCE seq4 START WITH 3 RESTART MINVALUE -100 CYCLE;
   Query OK, 0 rows affected
   

For more information about the ALTER SEQUENCE statement, see ALTER SEQUENCE.

Drop a sequence

You can use the DROP SEQUENCE statement to drop a sequence. Syntax:

DROP SEQUENCE [database_name.]sequence_name;

Statement usage notes:

• To execute this statement, you must have the DROP privilege.

• database_name. specifies the database in which the sequence resides. If you do not specify database_name., the system takes by default that the sequence is in the current logon database.

Example: Drop the sequence S1.

obclient> DROP SEQUENCE S1;
Query OK, 0 rows affected