CREATE SEQUENCE

Grammar Description

CREATE SEQUENCE The user creates a sequence object. A sequence is a special database object that can be used to automatically generate a unique sequence of numbers. Typically, a sequence is used to automatically generate unique values for the primary key field of a table.

CREATE SEQUENCE is a command used to create a sequence of self-increment numbers. It is used to generate unique, continuous numeric values, usually for generating values for primary key columns or other columns that require self-increment numbers.

Grammar Structure

> CREATE SEQUENCE [ IF NOT EXISTS ] SEQUENCE_NAME
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue] [ MAXVALUE maxvalue]
    [ START [ WITH ] start ] [ [ NO ] CYCLE ]

Syntax interpretation

data_type

Optional clause AS data_type specifies the data type of the sequence. Supported data types are smalllint [unsigned], integer [unsigned] and bigint [unsigned], and the default bigint is the default value. The data type determines the default minimum and maximum values of the sequence.

INCREMENT

Optional clause INCREMENT [ BY ] increment Specifies which value to add to the current sequence value to create a new value. Positive values will generate ascending order, negative values will generate descending order; the default value is 1.

MINVALUE

Optional clause MINVALUE minvalue determines the minimum value a sequence can generate. If this clause is not provided or MINVALUE is not specified, the default value is used. The default value for ascending sequences is 1. The default value of a descending sequence is the minimum value of the data type.

MAXVALUE

Optional clause MAXVALUE maxvalue determines the maximum value of the sequence. If this clause is not provided or MAXVALUE is not specified, the default value is used. The default value of an ascending sequence is the maximum value of the data type. The default value for descending sequences is -1.

START

Optional clause START [ WITH ] start Allows the sequence to start anywhere. The default start value is the minimum value of the ascending sequence and the maximum value of the descending sequence.

CYCLE

The CYCLE option allows the sequence to be wound back when ascending or descending sequences reach the maximum or minimum value, respectively. If the limit is reached, the next number generated will be the minimum or maximum value, respectively.

Syntax Operation

Operate the value of the sequence, using the function:

`NEXTVAL(sequence_name)`

Set the current value to the incremented value and return.

`CURRVAL(sequence_name)`

Returns the current value.

`SETVAL(sequence_name, n [,b])`

Set the current value;

b Default setting is true, the next time nextval() is called, it will directly return n
b If false is set, return n+increment

`LASTVAL()`

Returns the value of any sequence that was most recently obtained with NEXTVAL in the current session, and LASTVAL()() can only be initialized by NEXTVAL.

LASTVAL() is affected by SETVAL(sequence_name, n [,true]) changing the current value. The example is as follows:

Suppose a Sequence named seq_id is created, with a starting value of 1, each time incremented by 1, and a maximum value of 1000:

CREATE SEQUENCE seq_id INCREMENT BY 1 MAXVALUE 1000 START with 1;

You can then use the NEXTVAL() function to get the next sequence value and automatically increase the counter of the sequence by 1:

SELECT NEXTVAL('seq_id');

Next, you can use the LASTVAL() function to return the current value of the current Sequence:

SELECT LASTVAL();

You can also use the SETVAL() function to set the current value to 30, and the parameter [,b] is true:

SELECT SETVAL('seq_id', 30);

Then, you can use the NEXTVAL() function again to get the next sequence value:

SELECT NEXTVAL('seq_id');

31 will be returned because the current value has been set to 30, and the NEXTVAL() function will return the next sequence value 31.

SELECT LASTVAL();

Use LASTVAL() to return the current value of the current Sequence, which will return 31.

The above example shows that if the current value is set first through SETVAL(sequence_name, n [,true]) and then use NEXTVAL to obtain the next sequence value, then use LASTVAL() again, and return the value of the sequence obtained by NEXTVAL.

Use `SEQUENCE` in the table

To use a sequence in a table, you need to complete the following steps:

Create a sequence object: You can use the following SQL command to create a sequence named "my_sequence":
```
CREATE SEQUENCE my_sequence;
```
This will create a simple sequence object that will start at 1 and increment in 1.
Apply a sequence to a field in a table: In order to apply a sequence to a field in a table, you need to specify a default value in the table definition as the next value of the sequence, as shown below:
```
CREATE TABLE my_table (
  id INTEGER DEFAULT nextval('my_sequence'),
  name VARCHAR(50));
```
In the example above, the "id" field will automatically get the next unique value from the sequence as its default value.
Insert data: After the table and sequence have been defined, you can use the INSERT statement to insert data into the table. When inserting a row of data, if no value of the "id" field is specified, MatrixOne will automatically obtain the next unique value from the sequence as its default value.

For example, the following statement will insert a row of data into the "my_table" table and automatically assign a unique value to the "id" field:
```
INSERT INTO my_table (name) VALUES ('John');
INSERT INTO my_table (name) VALUES ('Tom');
```
By using sequences, unique identifiers can be easily assigned automatically in the table, avoiding the errors that can result from manual identifier allocation. Use the following statement for query verification:
```
mysql> select * from my_table;
+------+------+
| id | name |
+------+------+
| 1 | John |
| 2 | Tom |
+------+------+
2 rows in set (0.01 sec)
```

Note

When using SEQUENCE in a table, you need to note that auto_increment and sequence cannot be used together, otherwise an error will be reported.

Example

-- Created a sequence called "seq_id" which starts with 1 and increases by 1 each time, with a maximum value of 1000:
CREATE SEQUENCE seq_id INCREMENT BY 1 MAXVALUE 1000 START with 1;
-- After creating the sequence, you can use the NEXTVAL function to get the next sequence value as follows:
mysql> SELECT NEXTVAL('seq_id');
+-----------------------+
| nextval(seq_id) |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.02 sec)
-- This command returns the next value in the sequence (for example, 1) and automatically increments the counter of the sequence by 1.

-- The CURRVAL function returns the current value.
mysql> SELECT CURRVAL('seq_id');
+-----------------------+
| currval(seq_id) |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.01 sec)

-- Returns the value of any sequence that was most recently obtained with NEXTVAL in the current session.
mysql> SELECT LASTVAL();
+---------------+
| lastval() |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

-- Set the current value to 30.
mysql> SELECT SETVAL('seq_id', 30);
+-------------------------+
| setval(seq_id, 30) |
+-------------------------+
| 30 |
+-------------------------+
1 row in set (0.02 sec)

-- NEXTVAL function gets the next sequence value.
mysql> SELECT NEXTVAL('seq_id');
+-----------------------+
| nextval(seq_id) |
+-----------------------+
| 31 |
+-----------------------+
1 row in set (0.00 sec)