PolarDB：序列和自動產生列使用指南

功能簡介

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)