本文主要介绍如何查询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时不需要指定自增列,可以让DRDS自动维护。

批量获取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)