Query the type and values of a sequence

Query information about all sequences

Syntax

SHOW SEQUENCES

Example

You can use the SHOW SEQUENCE statement to query information about all sequences that can be accessed.

mysql> SHOW SEQUENCES;

In this example, the following information is returned.

+------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
| NAME | VALUE  | UNIT_COUNT | UNIT_INDEX | INNER_STEP | INCREMENT_BY | START_WITH | MAX_VALUE   | CYCLE | TYPE   |
+------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
| seq1 | 100000 | 1          | 0          | 100000     | N/A          | N/A        | N/A         | N/A   | GROUP  |
| seq2 | 400000 | 3          | 1          | 100000     | N/A          | N/A        | N/A         | N/A   | GROUP  |
| seq3 | N/A    | N/A        | N/A        | N/A        | N/A          | N/A        | N/A         | N/A   | TIME   |
| seq4 | 1006   | N/A        | N/A        | N/A        | 2            | 1000       | 99999999999 | N     | SIMPLE |
+------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
4 rows in set (0.00 sec)

Query a specific value of a sequence

Syntax

[<schema_name>.]<sequence name>.NEXTVAL

Examples

• Method 1

  You can use the SELECT statement to query a specific value of a sequence.

  mysql> SELECT sample_seq.nextval FROM dual;

  In this example, the following information is returned.

  +--------------------+
  | SAMPLE_SEQ.NEXTVAL |
  +--------------------+
  |             101001 |
  +--------------------+
  1 row in set (0.04 sec)

• Method 2

  You can use the INSERT statement to query a specific value of a sequence.

  mysql> INSERT INTO some_users (name,address,gmt_create,gmt_modified,intro) VALUES ('sun',sample_seq.nextval,now(),now(),'aa');

  Note

  • When you use the INSERT statement to query a specific value of a sequence, you must specify the sample_seq.nextval parameter as a value in the statement.
  • If the AUTO_INCREMENT parameter is specified when you create the table, you do not need to specify an auto-increment column in the INSERT statement. PolarDB-X can automatically manage the auto-increment column.

Query multiple values of a sequence at the same time

Syntax

You can use the following syntax to query multiple values of a sequence.

SELECT [<schema_name>.]<sequence name>.NEXTVAL FROM DUAL WHERE COUNT = <numeric value>

Example

You can use the SELECT statement to query multiple values of a sequence.

mysql> SELECT sample_seq.nextval FROM dual WHERE count = 10;

In this example, the following information is returned.

+--------------------+
| SAMPLE_SEQ.NEXTVAL |
+--------------------+
|             101002 |
|             101003 |
|             101004 |
|             101005 |
|             101006 |
|             101007 |
|             101008 |
|             101009 |
|             101010 |
|             101011 |
+--------------------+
10 row in set (0.04 sec)