查询与获取Sequence

本文主要介绍如何查询Sequence类型以及获取Sequence值。

查询Sequence

语法

SHOW SEQUENCES

示例

请在命令行输入如下代码：

mysql> SHOW SEQUENCES;

返回结果如下：

+------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
| 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)

说明 返回结果中的TYPE列，显示的是Sequence类型的缩写。

获取显式Sequence值

语法

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

示例

• 方法一请在命令行输入如下代码：

  mysql> SELECT sample_seq.nextval FROM dual;
  

返回结果如下：

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

• 方法二请在命令行输入如下代码：

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

说明

• 该方法是把sample_seq.nextval当做一个值写入了 SQL中。

• 如果建表时已经指定了AUTO_INCREMENT参数，INSERT时不需要指定自增列，可以让PolarDB-X自动维护。

批量获取Sequence值

语法

批量获取Sequence值的语法如下：

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

示例

请在命令行输入如下代码：

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

返回结果如下：

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