从MySQL到OBOracle：如何处理自增列？

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 中如何创建自增列的几种方法的总结。

审核编辑：刘清