開發指南—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)