從MySQL到OBOracle：如何處理自增列？

1 背景描述

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

發(fā)現(xiàn)問題場景

業(yè)務(wù)需要將數(shù)據(jù)庫轉(zhuǎn)換為 OceanBase 數(shù)據(jù)庫，但源端涉及到 Oracle 及 MySQL 兩種不同數(shù)據(jù)庫，需要合并為 OceanBase 中單一的 Oracle 模式，其中源端 MySQL 數(shù)據(jù)庫需要改造為 OBOracle 并做異構(gòu)數(shù)據(jù)遷移。

在數(shù)據(jù)遷移中發(fā)現(xiàn)，MySQL 中的自增列（AUTO_INCREMENT）在 OBOracle 中是不支持的，在 OBOracle 對應(yīng) MySQL 自增列的功能是通過序列實現(xiàn)的。通過測試以及閱讀相關(guān)文章，共測試完成了以下四種 OBOracle 創(chuàng)建并使用序列的方法。

2 四種 OBOracle 創(chuàng)建序列方法

1方法一：SEQUENCE + DML

在 OceanBase 中 Oracle 數(shù)據(jù)庫，我們可以通過以下語法創(chuàng)建序列：

CREATESEQUENCEsequence_name
[
MINVALUEvalue--序列最小值
MAXVALUEvalue--序列最大值
STARTWITHvalue--序列起始值
INCREMENTBYvalue--序列增長值
CACHEcache--序列緩存?zhèn)€數(shù)
CYCLE|NOCYCLE--序列循環(huán)或不循環(huán)
]

語法解釋：

sequence_name 是要創(chuàng)建的序列名稱

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

INCREMENT BY 指定序列每次遞增的值，默認(rèn)為 1

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

如果序列已經(jīng)遞增到最大值或最小值，則會根據(jù)你的設(shè)置進行循環(huán)或停止自增長。CACHE設(shè)置序列預(yù)讀緩存數(shù)量。

CYCLE 表示循環(huán)序列

NOCYCLE 則表示不循環(huán)序列

通過 OB 官方文檔操作，創(chuàng)建序列，實現(xiàn)表的列自增，示例如下：

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、首先創(chuàng)建一個需要自增列的表。

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、創(chuàng)建一個序列并更改表中 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 表示調(diào)用 sequence_name 序列的 nextval 函數(shù)，該函數(shù)返回序列的下一個值。因此，執(zhí)行述語句后，當(dāng) tablename 表中插入一行數(shù)據(jù)時，會自動為 ID 列賦值為 sequence_name 序列的下一個值。

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

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 + 觸發(fā)器

OB 延用 Oracle 中創(chuàng)建觸發(fā)器的方法達(dá)到自增列的效果，具體步驟如下：

1、首先創(chuàng)建一個序列。

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

2、創(chuàng)建一個表。

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

3、創(chuàng)建一個觸發(fā)器，在每次向表中插入行時，觸發(fā)器將自動將新行的 ID 列設(shè)置為序列的下一個值。

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

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

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

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、在創(chuàng)建表時使用 GENERATED BY DEFAULT AS IDENTITY 語法來創(chuàng)建自增長的列。

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、驗證該方法是否達(dá)到自增列的效果。

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 可以非常簡單地創(chuàng)建自增長列，無需使用其他手段，例如觸發(fā)器。此方法不需要手動創(chuàng)建序列，會自動創(chuàng)建一個序列，在內(nèi)部使用它來生成自增長列的值。

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)

查看數(shù)據(jù)庫對象視圖 dba_objects，發(fā)現(xiàn)該方法通過創(chuàng)建對象內(nèi)部命名方式為 ISEQ$$_5000x_16。

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

Tips：

在 Oracle 12c 及以上版本中，可以使用 GENERATED BY DEFAULT AS IDENTITY 關(guān)鍵字來創(chuàng)建自增長的列；

在 PostgreSQL 數(shù)據(jù)庫中 GENERATED BY DEFAULT AS IDENTITY 也是適用的。

3 總結(jié)

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

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

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

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

以上就是對 OBOracle 中如何創(chuàng)建自增列的幾種方法的總結(jié)。

審核編輯：劉清