Sequence Functions

Sequence functions in TiDB are used to return or set values of sequence objects created using the CREATE SEQUENCE statement.

NEXTVAL()

The NEXTVAL() function returns the next value of a sequence.

Example:

Create a sequence named s1:

CREATE SEQUENCE s1;

Get the next value from s1:

SELECT NEXTVAL(s1);

The output is as follows:

+-------------+
| NEXTVAL(s1) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

NEXT VALUE FOR

The NEXT VALUE FOR function is an alias for NEXTVAL().

Example:

Get the next value from s1 using NEXTVAL():

SELECT NEXTVAL(s1);

The output is as follows:

+-------------+
| NEXTVAL(s1) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

Get the next value from s1 using NEXT VALUE FOR:

SELECT NEXT VALUE FOR s1;

The output is as follows:

+-------------------+
| NEXT VALUE FOR s1 |
+-------------------+
|                 3 |
+-------------------+
1 row in set (0.00 sec)

SETVAL()

The SETVAL(n) function sets the current value of a sequence.

Example:

Get the next value from s1:

SELECT NEXTVAL(s1);

The output is as follows:

+-------------+
| NEXTVAL(s1) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

Set the current value of s1 to 10:

SELECT SETVAL(s1, 10);

The output is as follows:

+----------------+
| SETVAL(s1, 10) |
+----------------+
|             10 |
+----------------+
1 row in set (0.00 sec)

Verify the next value after setting it to 10:

SELECT NEXTVAL(s1);

The output is as follows:

+-------------+
| NEXTVAL(s1) |
+-------------+
|          11 |
+-------------+
1 row in set (0.00 sec)

LASTVAL()

The LASTVAL() function returns the last value generated by a sequence in the current session.

Example:

Get the last value generated by s1 in the current session:

SELECT LASTVAL(s1);

The output is as follows:

+-------------+
| LASTVAL(s1) |
+-------------+
|          11 |
+-------------+
1 row in set (0.00 sec)

MySQL compatibility

MySQL does not support the functions and statements for creating and manipulating sequences as defined in ISO/IEC 9075-2.