1 背景描述

OceanBase 數據庫中分為 MySQL 租戶與 Oracle 租戶，本文針對 OceanBase 中 Oracle 租戶怎樣創建自增列，以及如何更簡單方便的處理自增列的問題展開介紹。OceanBase 的 Oracle 租戶以下簡稱：OBOracle。

發現問題場景

業務需要將數據庫轉換為 OceanBase 數據庫，但源端涉及到 Oracle 及 MySQL 兩種不同數據庫，需要合并為 OceanBase 中單一的 Oracle 模式，其中源端 MySQL 數據庫需要改造為 OBOracle 并做異構數據遷移。

在數據遷移中發現，MySQL 中的自增列（AUTO_INCREMENT）在 OBOracle 中是不支持的，在 OBOracle 對應 MySQL 自增列的功能是通過序列實現的。通過測試以及閱讀相關文章，共測試完成了以下四種 OBOracle 創建并使用序列的方法。

2 四種 OBOracle 創建序列方法

1方法一：SEQUENCE + DML

在 OceanBase 中 Oracle 數據庫，我們可以通過以下語法創建序列：

CREATESEQUENCEsequence_name
[
MINVALUEvalue--序列最小值
MAXVALUEvalue--序列最大值
STARTWITHvalue--序列起始值
INCREMENTBYvalue--序列增長值
CACHEcache--序列緩存個數
CYCLE|NOCYCLE--序列循環或不循環
]

語法解釋：

sequence_name 是要創建的序列名稱

START WITH 指定使用該序列時要返回的第一個值，默認為 1

INCREMENT BY 指定序列每次遞增的值，默認為 1

MINVALUE 和 MAXVALUE 定義序列值的最小值和最大值

如果序列已經遞增到最大值或最小值，則會根據你的設置進行循環或停止自增長。CACHE設置序列預讀緩存數量。

CYCLE 表示循環序列

NOCYCLE 則表示不循環序列

通過 OB 官方文檔操作，創建序列，實現表的列自增，示例如下：

obclient[oboracle]>CREATETABLEtest(
->IDNUMBERNOTNULLPRIMARYKEY,
->NAMEVARCHAR2(480),
->AGENUMBER(10,0)
->);
QueryOK,0rowsaffected(0.116sec)

obclient[oboracle]>CREATESEQUENCEseq_testSTARTWITH100INCREMENTBY1;
QueryOK,0rowsaffected(0.026sec)

obclient[oboracle]>INSERTINTOtest(ID,NAME,AGE)VALUES(seq_test.nextval,'A',18);
QueryOK,1rowaffected(0.035sec)

obclient[oboracle]>INSERTINTOtest(ID,NAME,AGE)VALUES(seq_test.nextval,'B',19);
QueryOK,1rowaffected(0.001sec)

obclient[oboracle]>INSERTINTOtest(ID,NAME,AGE)VALUES(seq_test.nextval,'C',20);
QueryOK,1rowaffected(0.001sec)

obclient[oboracle]>select*fromtest;
+-----+------+------+
|ID|NAME|AGE|
+-----+------+------+
|100|A|18|
|101|B|19|
|102|C|20|
+-----+------+------+
3rowsinset(0.006sec)

2方法二：SEQUENCE + DDL

1、首先創建一個需要自增列的表。

obclient[oboracle]>CREATETABLEAtable(
->IDNUMBER(10,0),
->NAMEVARCHAR2(480),
->AGENUMBER(10,0),
->PRIMARYKEY(id)
->);
QueryOK,0rowsaffected(0.105sec)

obclient[oboracle]>descAtable;
+-------+---------------+------+-----+---------+-------+
|FIELD|TYPE|NULL|KEY|DEFAULT|EXTRA|
+-------+---------------+------+-----+---------+-------+
|ID|NUMBER(10)|NO|PRI|NULL|NULL|
|NAME|VARCHAR2(480)|YES|NULL|NULL|NULL|
|AGE|NUMBER(10)|YES|NULL|NULL|NULL|
+-------+---------------+------+-----+---------+-------+
3rowsinset(0.037sec)

2、創建一個序列并更改表中 ID 列的 DEFAULT 屬性為 sequence_name.nextval。

obclient[oboracle]>CREATESEQUENCEA_seq
->MINVALUE1
->MAXVALUE999999
->STARTWITH10
->INCREMENTBY1;
QueryOK,0rowsaffected(0.022sec)

obclient[oboracle]>ALTERTABLEAtableMODIFYidDEFAULTA_seq.nextval;
QueryOK,0rowsaffected(0.065sec)

obclient[oboracle]>descAtable;
+-------+---------------+------+-----+-------------------+-------+
|FIELD|TYPE|NULL|KEY|DEFAULT|EXTRA|
+-------+---------------+------+-----+-------------------+-------+
|ID|NUMBER(10)|NO|PRI|"A_SEQ"."NEXTVAL"|NULL|
|NAME|VARCHAR2(480)|YES|NULL|NULL|NULL|
|AGE|NUMBER(10)|YES|NULL|NULL|NULL|
+-------+---------------+------+-----+-------------------+-------+
3rowsinset(0.013sec)

此處為修改表 tablename 中的 ID 值為序列 sequence_name 的下一個值。具體而言，sequence_name.nextval 表示調用 sequence_name 序列的 nextval 函數，該函數返回序列的下一個值。因此，執行述語句后，當 tablename 表中插入一行數據時，會自動為 ID 列賦值為 sequence_name 序列的下一個值。

3、驗證該方法是否達到自增列的效果。

obclient[oboracle]>INSERTINTOAtable(NAME,AGE)VALUES('zhangsan',18);
QueryOK,1rowaffected(0.047sec)

obclient[oboracle]>INSERTINTOAtable(NAME,AGE)VALUES('lisi',19);
QueryOK,1rowaffected(0.002sec)

obclient[oboracle]>select*fromAtable;
+----+----------+------+
|ID|AME|AGE|
+----+----------+------+
|10|zhangsan|18|
|11|lisi|19|
+----+----------+------+
2rowsinset(0.013sec)

3 方法三：SEQUENCE + 觸發器

OB 延用 Oracle 中創建觸發器的方法達到自增列的效果，具體步驟如下：

1、首先創建一個序列。

obclient[oboracle]>CREATESEQUENCEB_seq
->MINVALUE1
->MAXVALUE999999
->STARTWITH1
->INCREMENTBY1;
QueryOK,0rowsaffected(0.023sec)

2、創建一個表。

obclient[oboracle]>CREATETABLEBtable(
->IDNUMBER,
->NAMEVARCHAR2(480),
->AGENUMBER(10,0)
->);
QueryOK,0rowsaffected(0.129sec)

3、創建一個觸發器，在每次向表中插入行時，觸發器將自動將新行的 ID 列設置為序列的下一個值。

obclient[oboracle]>CREATEORREPLACETRIGGERset_id_on_Btable
->BEFOREINSERTONBtable
->FOREACHROW
->BEGIN
->SELECTB_seq.NEXTVALINTO:new.idFROMdual;
->END;
->/
QueryOK,0rowsaffected(0.114sec)

該觸發器在每次向 Btable 表中插入行之前觸發，通過 SELECT B_seq.NEXTVAL INTO :new.id FROM dual; 將 ID 列設置為 B_seq 序列的下一個值。:new.id 表示新插入行的 ID列，dual 是一個虛擬的表，用于生成一行數據用以存儲序列的下一個值。

4、驗證該方法是否達到自增列的效果。

obclient[oboracle]>INSERTINTOBtable(NAME,AGE)VALUES('zhangsan',18);
QueryOK,1rowaffected(0.111sec)

obclient[oboracle]>INSERTINTOBtable(NAME,AGE)VALUES('lisi',19);
QueryOK,1rowaffected(0.002sec)

obclient[oboracle]>select*fromBtable;
+------+----------+------+
|ID|NAME|AGE|
+------+----------+------+
|1|zhangsan|18|
|2|lisi|19|
+------+----------+------+
2rowsinset(0.008sec)

4方法四：GENERATED BY DEFAULT AS IDENTITY 語法

1、在創建表時使用 GENERATED BY DEFAULT AS IDENTITY 語法來創建自增長的列。

obclient[oboracle]>CREATETABLECtable(
->IDNUMBERGENERATEDBYDEFAULTASIDENTITYMINVALUE1MAXVALUE999999INCREMENTBY1STARTWITH1primarykey,
->NAMEVARCHAR2(480),
->AGENUMBER(10,0)
->);
QueryOK,0rowsaffected(0.121sec)

obclient[oboracle]>descCtable;
+-------+---------------+------+-----+------------------+-------+
|FIELD|TYPE|NULL|KEY|DEFAULT|EXTRA|
+-------+---------------+------+-----+------------------+-------+
|ID|NUMBER|NO|PRI|SEQUENCE.NEXTVAL|NULL|
|NAME|VARCHAR2(480)|YES|NULL|NULL|NULL|
|AGE|NUMBER(10)|YES|NULL|NULL|NULL|
+-------+---------------+------+-----+------------------+-------+
3rowsinset(0.011sec)

2、驗證該方法是否達到自增列的效果。

obclient[oboracle]>INSERTINTOCtable(NAME,AGE)VALUES('zhangsan',18);
QueryOK,1rowaffected(0.015sec)

obclient[oboracle]>INSERTINTOCtable(NAME,AGE)VALUES('lisi',19);
QueryOK,1rowaffected(0.001sec)

obclient[oboracle]>select*fromCtable;
+----+----------+------+
|ID|NAME|AGE|
+----+----------+------+
|1|zhangsan|18|
|2|lisi|19|
+----+----------+------+
2rowsinset(0.008sec)

3、通過驗證，使用 GENERATED BY DEFAULT AS IDENTITY 可以非常簡單地創建自增長列，無需使用其他手段，例如觸發器。此方法不需要手動創建序列，會自動創建一個序列，在內部使用它來生成自增長列的值。

obclient[SYS]>select*fromdba_objectswhereOBJECT_TYPE='SEQUENCE';
+-------+-----------------+----------------+------------------+----------------+-------------+-----------+---------------+------------------------------+--------+-----------+-----------+-----------+-----------+--------------+
|OWNER|OBJECT_NAME|SUBOBJECT_NAME|OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE|CREATED|LAST_DDL_TIME|TIMESTAMP|STATUS|TEMPORARY|GENERATED|SECONDARY|NAMESPACE|EDITION_NAME|
+-------+-----------------+----------------+------------------+----------------+-------------+-----------+---------------+------------------------------+--------+-----------+-----------+-----------+-----------+--------------+
|MYSQL|A_SEQ|NULL|1100611139403783|NULL|SEQUENCE|31-MAY-23|31-MAY-23|31-MAY-2302.21.42.603005PM|VALID|N|N|N|0|NULL|
|MYSQL|B_SEQ|NULL|1100611139403784|NULL|SEQUENCE|31-MAY-23|31-MAY-23|31-MAY-2303.28.39.222090PM|VALID|N|N|N|0|NULL|
|MYSQL|ISEQ$$_50012_16|NULL|1100611139403785|NULL|SEQUENCE|31-MAY-23|31-MAY-23|31-MAY-2304.01.23.577766PM|VALID|N|N|N|0|NULL|
|MYSQL|SEQ_TEST|NULL|1100611139403786|NULL|SEQUENCE|31-MAY-23|31-MAY-23|31-MAY-2305.09.33.981039PM|VALID|N|N|N|0|NULL|
+-------+-----------------+----------------+------------------+----------------+-------------+-----------+---------------+------------------------------+--------+-----------+-----------+-----------+-----------+--------------+
6rowsinset(0.042sec)

查看數據庫對象視圖 dba_objects，發現該方法通過創建對象內部命名方式為 ISEQ$$_5000x_16。

測試發現，關于序列對象的名稱在OB中不論是通過 GENERATED BY DEFAULT AS IDENTITY 自動創建，還是手動創建，都會占用 ISEQ$$_5000x_16 中 x 的位置，若刪除序列或刪除表，該對象名稱也不會復用，只會單調遞增。

Tips：

在 Oracle 12c 及以上版本中，可以使用 GENERATED BY DEFAULT AS IDENTITY 關鍵字來創建自增長的列；

在 PostgreSQL 數據庫中 GENERATED BY DEFAULT AS IDENTITY 也是適用的。

3 總結

方法一（SEQUENCE + DML）：也就是 OB 的官方文檔中創建序列的操作，在每次做 INSERT 操作時需要指定自增列并加入 sequence_name ，對業務不太友好，不推薦。

方法二（SEQUENCE + DDL）：相較于第一種該方法只需要指定 DDL 改寫 DEFAULT 屬性省去了 DML 的操作，但仍需再指定自己創建的序列名 sequence_name，每個表的序列名都不一致，管理不方便，不推薦。

方法三（SEQUENCE + 觸發器）：延用 Oracle 的序列加觸發器的方法，觸發器會占用更多的計算資源和內存，對性能會有影響，因此也不推薦。

方法四（GENERATED BY DEFAULT AS IDENTITY 語法）：既方便運維人員管理，對業務也很友好，還不影響性能。強烈推薦！！！

以上就是對 OBOracle 中如何創建自增列的幾種方法的總結。

審核編輯：劉清