Lists the sequences in the current database, with optional filtering by name or type.
SHOW SEQUENCESreturns only sequences in the current database. To query sequences across all databases in the instance, use theINFORMATION_SCHEMA.SEQUENCESview.
Syntax
SHOW SEQUENCES [ WHERE <filter_condition> ]Result columns
Each row describes one sequence. Columns that do not apply to a sequence type appear as N/A in the result.
| Column | Description | Default | Applies to |
|---|---|---|---|
SCHEMA_NAME | The schema that owns the sequence. | — | All types |
NAME | The sequence name. | — | All types |
VALUE | The last allocated value. | — | NEW, GROUP |
UNIT_COUNT | The number of allocation units. | — | GROUP |
UNIT_INDEX | The index of the current allocation unit. | — | GROUP |
INNER_STEP | The step size within each allocation unit. | — | GROUP |
INCREMENT_BY | The increment between consecutive values. | — | NEW |
START_WITH | The starting value. | — | NEW |
MAX_VALUE | The maximum value before the sequence cycles or stops. | — | NEW |
CYCLE | Whether the sequence restarts after reaching MAX_VALUE. Y = restarts; N = stops. | — | NEW |
TYPE | The sequence type: NEW, TIME, or GROUP. | — | All types |
PHY_SEQ_NAME | The internal physical sequence name. | — | NEW |
Examples
List all sequences
SHOW SEQUENCES;+-------------+-----------+-------+------------+------------+------------+--------------+------------+---------------------+-------+-------+------------------------------------------+
| SCHEMA_NAME | NAME | VALUE | UNIT_COUNT | UNIT_INDEX | INNER_STEP | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE | PHY_SEQ_NAME |
+-------------+-----------+-------+------------+------------+------------+--------------+------------+---------------------+-------+-------+------------------------------------------+
| partest | newseq1 | 1 | N/A | N/A | N/A | 1 | 1 | 9223372036854775807 | N | NEW | pxc_seq_ee6653d17586a275d41522852aa36c80 |
| partest | newseq2 | 1 | N/A | N/A | N/A | 2 | 100 | 200 | Y | NEW | pxc_seq_6a2a3689aa90a0179b71183b8bfc426c |
| partest | timeseq1 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | TIME | N/A |
| partest | timeseq2 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | TIME | N/A |
| partest | groupseq1 | 0 | 1 | 0 | 100000 | N/A | N/A | N/A | N/A | GROUP | N/A |
| partest | groupseq2 | 200000| 1 | 0 | 100000 | N/A | N/A | N/A | N/A | GROUP | N/A |
+-------------+-----------+-------+------------+------------+------------+--------------+------------+---------------------+-------+-------+------------------------------------------+Filter by type
Use the WHERE clause to narrow results to a specific sequence type.
SHOW SEQUENCES WHERE TYPE='NEW';+-------------+---------+-------+------------+------------+------------+--------------+------------+---------------------+-------+------+------------------------------------------+
| SCHEMA_NAME | NAME | VALUE | UNIT_COUNT | UNIT_INDEX | INNER_STEP | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE | PHY_SEQ_NAME |
+-------------+---------+-------+------------+------------+------------+--------------+------------+---------------------+-------+------+------------------------------------------+
| partest | newseq1 | 1 | N/A | N/A | N/A | 1 | 1 | 9223372036854775807 | N | NEW | pxc_seq_ee6653d17586a275d41522852aa36c80 |
| partest | newseq2 | 1 | N/A | N/A | N/A | 2 | 101 | 300 | N | NEW | pxc_seq_6a2a3689aa90a0179b71183b8bfc426c |
+-------------+---------+-------+------------+------------+------------+--------------+------------+---------------------+-------+------+------------------------------------------+Filter by name
SHOW SEQUENCES WHERE NAME LIKE '%seq1%';+-------------+-----------+-------+------------+------------+------------+--------------+------------+---------------------+-------+-------+------------------------------------------+
| SCHEMA_NAME | NAME | VALUE | UNIT_COUNT | UNIT_INDEX | INNER_STEP | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE | PHY_SEQ_NAME |
+-------------+-----------+-------+------------+------------+------------+--------------+------------+---------------------+-------+-------+------------------------------------------+
| partest | newseq1 | 1 | N/A | N/A | N/A | 1 | 1 | 9223372036854775807 | N | NEW | pxc_seq_ee6653d17586a275d41522852aa36c80 |
| partest | timeseq1 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | TIME | N/A |
| partest | groupseq1 | 0 | 1 | 0 | 100000 | N/A | N/A | N/A | N/A | GROUP | N/A |
+-------------+-----------+-------+------------+------------+------------+--------------+------------+---------------------+-------+-------+------------------------------------------+该文章对您有帮助吗？