7、Sequence

[TOC]
# Sequence 介绍

**Sequence** 是一种自增整数序列，主要用于生成唯一标识符。其值按照一定规则自增，具有唯一标识性，因此在数据库中常常被用作主键。

---

# Sequence 使用方法

## 创建 Sequence

**CREATE SEQUENCE 语句的语法格式**

```sql
CREATE SEQUENCE name
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH ] start ]
[ CACHE cache ]
[ [ NO ] CYCLE | NOCYCLE ]
[ OWNED BY { table_name.column_name | NONE } ];
```

### 参数说明

- **name**：将要创建的序列名称。仅可使用小写字母（a~z）、大写字母（A~Z）、数字和特殊字符"#"、"_"、"$"的组合。
- **increment**：指定序列的步长。正数生成递增序列，负数生成递减序列，缺省值为1。
- **MINVALUE**：执行序列的最小值。缺省值为1（递增）或 -2^63-1（递减）。
- **start**：指定序列的起始值。缺省值为最小值（递增）或最大值（递减）。
- **cache**：为了快速访问，在内存中预先存储序列号的个数。缺省值为1。
- **CYCLE**：使序列达到最大值或最小值后可循环。如果声明了 NO CYCLE，则达到最大值后返回错误。
- **OWNED BY**：将序列与一个表的指定字段关联，删除字段或表时会自动删除已关联序列。

### 注意事项

- 不建议同时定义 cache 和 maxvalue 或 minvalue，因为这会导致序列号不连续，可能产生空洞。
- 建议 cache 值不超过 100000000，以避免性能问题。
- 通过 OWNED BY 创建的序列不应被其他表共享。

### 示例

#### 创建一个序列

```sql
CREATE SEQUENCE my_sequence INCREMENT BY 1 START WITH 1 CACHE 10;
```

#### 创建与表关联的序列

```sql
CREATE TABLE my_table (
    id integer NOT NULL DEFAULT nextval('my_sequence'),
    name text
);
```

---

## 修改 Sequence

**ALTER SEQUENCE 语句的语法格式**

```sql
ALTER SEQUENCE [ IF EXISTS ] name
[ MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ OWNED BY { table_name.column_name | NONE } ];
```

### 参数说明

- **IF EXISTS**：如果序列不存在，该选项不会出现错误，仅有通知。
- **MAXVALUE**：序列所能达到的最大值。
- **OWNED BY**：将序列与一个表的指定字段关联。

### 示例

- 修改序列的最大值：

```sql
ALTER SEQUENCE my_sequence MAXVALUE 1000;
```

- 关联序列与表的字段：

```sql
ALTER SEQUENCE my_sequence OWNED BY my_table.id;
```

---

## 删除 Sequence

**DROP SEQUENCE 语句**

```sql
DROP SEQUENCE [ IF EXISTS ] {[schema.]sequence_name} [ , ... ] [ CASCADE | RESTRICT ];
```

### 参数说明

- **IF EXISTS**：当序列不存在时使用该选项不会出现错误消息，仅有一个通知。
- **CASCADE**：级联删除依赖于该序列的对象。
- **RESTRICT**：如果存在任何依赖的对象，则拒绝删除序列（默认值）。

### 示例

```sql
DROP SEQUENCE my_sequence CASCADE;
```

---

# Sequence 相关函数

## `nextval(regclass)`

- **功能**：递增序列并返回新值，返回类型为 bigint。
- **调用示例**：

```sql
SELECT nextval('my_sequence');
```

### 注意事项

- `nextval()` 不支持回滚，获取的值即使在事务中中断也不会被退回。
- 在并发环境下，`nextval()` 会产生“空洞”，因为序列值的生成并不保证连续性。

## `currval(regclass)`

- **功能**：返回当前会话里最近一次 `nextval` 返回的指定序列的数值。
- **调用示例**：

```sql
SELECT currval('my_sequence');
```

### 注意事项

- 在当前会话未调用过 `nextval` 时，调用 `currval` 会报错。

## `lastval()`

- **功能**：返回当前会话里最近一次 `nextval` 返回的数值，不需要序列名。
- **调用示例**：

```sql
SELECT lastval();
```

### 注意事项

- 在默认情况下，`lastval()` 不支持，需要设置 `enable_beta_features` 为 true。

## `setval(regclass, bigint)`

- **功能**：设置序列的当前数值及 `is_called` 标志。
- **调用示例**：

```sql
SELECT setval('my_sequence', 10);
```

### 注意事项

- `setval` 对当前会话及 GTM 上立刻生效，但对其他会话，若定义了 cache，则需等到缓存值用尽后，才会感知其变动。

---

# Sequence 使用注意事项

- 新 Sequence 值的产生依赖 GTM，频繁的序列值申请可能成为性能瓶颈，尤其在批量数据导入时要特别注意。
- 对于大规模的 `INSERT FROM SELECT` 操作，建议控制 `bulkload` 的并发数，避免耗尽 GTM 连接资源。
- 在使用 `setval()` 函数时，需保证设置的新值不能与已有的序列值冲突。

---

# Sequence 使用案例

## 案例 (1)

- 创建一个从 101 开始的递增序列：

```sql
CREATE SEQUENCE serial START 101 CACHE 20;
```

- 从序列中选出下一个数字：

```sql
SELECT nextval('serial');
```

- 创建与表关联的序列：

```sql
CREATE TABLE customer_address (ca_address_sk integer NOT NULL);
CREATE SEQUENCE serial1 START 101 CACHE 20 OWNED BY customer_address.ca_address_sk;
```

## 案例 (2)

- 使用 `serial` 创建主键自增序列表：

```sql
CREATE TABLE serial_table (a int, b serial);
INSERT INTO serial_table (a) VALUES (1), (2), (3);
SELECT * FROM serial_table ORDER BY b;
```

- 将序列 `serial` 的最大值修改为 200：

```sql
ALTER SEQUENCE serial MAXVALUE 200;
```

- 创建一个表并定义默认值：

```sql
CREATE TABLE T1 (C1 bigint DEFAULT nextval('serial'));
ALTER SEQUENCE serial OWNED BY T1.C1;
```

## 案例 (3)

- 删除名为 `seq1` 的序列：

```sql
DROP SEQUENCE seq1;
```

- 使用函数 `setval` 对自增列值进行重置：

```sql
SELECT setval('seqDemo', 1);
```

## 案例 (4)

- 查询序列的 LAST_VALUE：

```sql
SELECT lastval();
```

## 案例 (5)

- 查询 SEQUENCE 的当前最新值：

```sql
SELECT currval('seq1');
```

DBLOG

导航1234

最近发表

友情链接