序列和自动生成列使用指南

功能简介

PolarDB PostgreSQL版（兼容Oracle）提供了多种生成唯一序列值的方式，以满足不同场景的需求，并高度兼容Oracle数据库。

• 传统序列
  这是一个独立的数据库对象，通过CREATE SEQUENCE创建。在插入数据时，您需要通过调用NEXTVAL伪列（例如my_seq.NEXTVAL）显式地从序列中获取下一个值并插入到表中。这种方式最灵活，序列和表是解耦的。

• 自动生成列：
  这是Oracle 12c引入的特性，它将序列隐式地与表中的某一列绑定。在创建表时，您只需在列定义上添加GENERATED ... AS IDENTITY子句，数据库就会在插入新行时自动为该列生成值，无需在INSERT语句中显式处理。这种方式更加便捷，符合现代数据库的设计趋势。

自动生成列根据其行为策略，又分为以下三种类型：

• GENERATED ALWAYS AS IDENTITY：强制自动生成。系统总是为该列生成值，您不能在INSERT语句中为该列提供显式值，否则会报错。这种策略最严格，能确保ID完全由数据库控制。

• GENERATED BY DEFAULT AS IDENTITY：默认自动生成。只有在INSERT语句中未指定该列时，系统才会为其生成值。如果您提供了具体值，系统将使用您提供的值。但如果您显式提供NULL，则会报错。

• GENERATED BY DEFAULT ON NULL AS IDENTITY：默认或在NULL时自动生成。这是最灵活的策略。当INSERT语句中未指定该列，或为该列显式提供了NULL值时，系统都会自动为其生成值。这种方式在数据迁移或需要混合插入自定义ID和自动生成ID的场景中非常有用。

前提条件

您的PolarDB PostgreSQL版（兼容Oracle）集群的修订版本需为2.0.14.17.36.0及以上

优势

• 简化开发：自动生成列将ID生成的逻辑封装在表定义中，使INSERT语句更简洁，无需关心序列的调用。

• 保证唯一性：由数据库保证生成的序列值是唯一的，避免了应用层生成ID可能导致的冲突。

• 灵活性强：GENERATED BY DEFAULT ON NULL策略兼顾了自动生成和手动指定的双重需求，特别适合数据导入和历史数据迁移等复杂场景。

选型建议

为了帮助您更好地选择，下表对比了不同序列生成方式的特性：

最佳实践

• 对于新开发的应用，推荐使用GENERATED BY DEFAULT ON NULL AS IDENTITY，因为它提供了最大的灵活性。

• 在进行数据迁移或需要导入包含存量ID的数据时，GENERATED BY DEFAULT ON NULL是理想选择。

• 如果业务要求ID必须由数据库严格控制且绝不允许手动干预，请使用GENERATED ALWAYS AS IDENTITY。

• 在高并发插入场景下，为IDENTITY列或传统序列设置一个合理的CACHE值（如CACHE 20或更高）可以显著提升性能。

使用传统序列（Sequence）

这种方式下，序列和表是两个独立的对象。

1. 创建序列

   首先，使用CREATE SEQUENCE命令创建一个序列对象，并可以自定义其起始值、步长、最大/小值等属性。

   CREATE SEQUENCE emp_seq
       START WITH 1000       -- 起始值为1000
       INCREMENT BY 1        -- 每次递增1
       MAXVALUE 9999         -- 最大值为9999
       NOCYCLE               -- 不循环
       CACHE 20;             -- 缓存20个值以提高性能

   参数说明

   • START WITH：起始值。

   • INCREMENT BY：增量步长。

   • MAXVALUE/NOMAXVALUE：最大值。

   • MINVALUE/NOMINVALUE：最小值。

   • CYCLE/NOCYCLE：是否循环。

   • CACHE/NOCACHE：缓存数量。

   • ORDER/NOORDER：是否保证顺序。

2. 在INSERT语句中使用序列

   在向表中插入数据时，通过调用序列的NEXTVAL伪列来获取新的ID。

   -- 创建一个使用序列的表
   CREATE TABLE employees (
       emp_id NUMBER(10) PRIMARY KEY,
       emp_name VARCHAR2(50) NOT NULL
   );
   
   -- 插入数据时调用emp_seq.NEXTVAL
   INSERT INTO employees (emp_id, emp_name) VALUES (emp_seq.NEXTVAL, 'John Smith');
   INSERT INTO employees (emp_id, emp_name) VALUES (emp_seq.NEXTVAL, 'Jane Doe');
   
   -- 查询结果
   SELECT * FROM employees;
   -- emp_id  |  emp_name
   -- --------+------------
   -- 1000    | John Smith
   -- 1001    | Jane Doe
   

3. 查询结果

   SELECT * FROM employees;

   返回结果：

    emp_id |  emp_name  
   --------+------------
      1000 | John Smith
      1001 | Jane Doe
   (2 rows)

4. 管理序列

   您可以查询user_sequences视图来查看序列的当前状态，或使用ALTER SEQUENCE修改其属性。

   -- 查看序列的当前值（需要先调用一次NEXTVAL）
   SELECT emp_seq.CURRVAL FROM dual;
   
   -- 修改序列的步长和缓存
   ALTER SEQUENCE emp_seq INCREMENT BY 2 CACHE 50;

使用自动生成列（Identity Column）

这种方式将序列与表列直接绑定，更为现代化和便捷。

序列管理与维护

无论是传统序列还是与自动生成列关联的内部序列，您都可以对其进行管理和维护。

SELECT sequence_name, min_value, max_value, increment_by,
       last_number, cache_size, cycle_flag
FROM user_sequences;

-- 将emp_seq序列的步长改为2，最大值改为99999，缓存改为50
ALTER SEQUENCE emp_seq
    START WITH 1005
    INCREMENT BY 2
    MAXVALUE 99999
    CACHE 50;

-- 删除现有序列
DROP SEQUENCE emp_seq;

-- 重新创建序列，并设置新的起始值
CREATE SEQUENCE emp_seq START WITH 5000 INCREMENT BY 1;

实际应用场景

在用户注册场景中，GENERATED BY DEFAULT ON NULL提供了极大的便利。它既能为新注册用户自动生成ID，也允许在数据迁移或批量导入时指定历史ID。

1. 创建用户账户表。

   CREATE TABLE user_accounts (
       user_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (
           START WITH 100000
           CACHE 100
       ),
       username VARCHAR2(50) UNIQUE NOT NULL,
       email VARCHAR2(100) UNIQUE NOT NULL,
       created_date DATE DEFAULT SYSDATE
   );

2. 场景1：新用户正常注册，自动生成ID。

   INSERT INTO user_accounts (username, email) VALUES ('john_doe', 'john@example.com');

3. 场景2：从旧系统迁移数据，指定历史ID。

   INSERT INTO user_accounts (user_id, username, email, created_date) VALUES (99999, 'legacy_user', 'leg***@system.com', DATE '2020-01-01');

4. 场景3：批量导入，混合使用自动生成和指定ID。

   INSERT ALL
       INTO user_accounts (username, email) VALUES ('alice', 'ali**@test.com')
       INTO user_accounts (user_id, username, email) VALUES (88888, 'system_acct', 'sys***@app.com')
   SELECT * FROM dual;

5. 查询最终结果。

   SELECT * FROM user_accounts ORDER BY user_id;

   返回结果：

    user_id |  username   |       email       |    created_date     
   ---------+-------------+-------------------+---------------------
      88888 | system_acct | sys***@app.com    | 2025-09-17 10:21:38
      99999 | legacy_user | leg***@system.com | 2020-01-01 00:00:00
     100000 | john_doe    | john@example.com  | 2025-09-17 10:21:38
     100001 | alice       | ali**@test.com    | 2025-09-17 10:21:38
   (4 rows)