本文验证并总结了在 OB Oracle 中实现自增主键的两种方法。
作者：赵黎明，爱可生 MySQL DBA 团队成员，熟悉 Oracle、MySQL 等数据库，擅长数据库性能问题诊断、事务与锁问题的分析等，负责处理客户 MySQL 及我司自研 DMP 平台日常运维中的问题，对开源数据库相关技术非常感兴趣。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文约 3200 字，预计阅读需要 10 分钟。
背景
在 MySQL 迁移到 OB Oracle 的场景中，通常需要考虑 OB Oracle 中自增主键如何实现的问题。之前在社区已有文章给出了一些解决方案。
本文将从解决实际问题的角度出发，验证并总结一个比较可行的实施方案。
方案一
我们将通过创建创建自定义序列的方式来实现自增主键。
MySQL 端创建测试表
zlm@10.186.60.68 [zlm]> desc t;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
zlm@10.186.60.68 [zlm]> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.00 sec)
使用 DBCAT 导出表结构
DBCAT 是 OB 提供的命令行工具，主要用于异构数据库迁移场景中非表对象的 DDL 导出和转换，如： Oracle 中的序列、函数、存储过程、包、触发器、视图等对象。
cd /opt/oceanbase_package/tools/dbcat-1.9.1-RELEASE/bin
./dbcat convert -H 10.186.60.68 -P 3332 --user=zlm --password=zlm --database=zlm --no-schema --no-quote --from mysql57 --to oboracle32x --table t --file=/tmp
Parsed args:
[--no-quote] true
[--no-schema] true
[--table] [t]
[--host] 10.186.60.68
[--port] 3332
[--user] zlm
[--password] ******
[--database] zlm
[--file] /tmp
[--from] mysql57
[--to] oboracle32x
2023-08-16 14:41:58 INFO Init convert config finished.
2023-08-16 14:41:58 INFO {dataSource-1} inited
2023-08-16 14:41:58 INFO Init source druid connection pool finished.
2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xColumnConverter
2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xIndexConverter
2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xPrimaryKeyConverter
2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xUniqueKeyConverter
2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xPartitionConverter
2023-08-16 14:41:59 INFO Load meta/mysql/mysql56.xml, meta/mysql/mysql57.xml successed
2023-08-16 14:42:09 INFO Query 0 dependencies elapsed 17.35 ms
2023-08-16 14:42:09 INFO Query table: "t" attr finished. Remain: 0
2023-08-16 14:42:09 INFO Query 1 tables elapsed 69.71 ms
2023-08-16 14:42:09 WARN Include types is empty. Ignore schema: ZLM
2023-08-16 14:42:09 WARN Skip to compare/convert sequences as SEQUENCE is unsupported
2023-08-16 14:42:09 INFO Starting to convert schema to path: "/tmp/dbcat-2023-08-16-144209/ZLM"
2023-08-16 14:42:09 INFO Successed to generate report in the path: "/tmp/dbcat-2023-08-16-144209/ZLM-conversion.html"
2023-08-16 14:42:09 INFO {dataSource-1} closing ...
2023-08-16 14:42:09 INFO {dataSource-1} closed
cd /tmp/dbcat-2023-08-16-144209/ZLM
cat TABLE-schema.sql
CREATE TABLE t (
id NUMBER(19,0),
name VARCHAR2(30 BYTE),
CONSTRAINT PRIMARY PRIMARY KEY (id)
);
-- CREATE SEQUENCE xxx START WITH 1 INCREMENT BY 1 ... for t
DBCAT 会对目标表的表结构做转换，使其符合 Oracle 的语法，并在导出的 DDL 语句中写入一行创建序列的伪 SQL ，可见，此工具也是建议创建序列来处理 MySQL 自增列的。
在 OB 目标端创建序列
- 对于含有自增列的每个表，都需要创建一个序列与之对应。
- 创建序列时，建议以
SEQ__的方式命名。 - 当不指定序列的
CYCLE和 ORDER 属性时，其默认值都是 N ，即：不循环，不排序。 - 当不指定序列的 CACHE 属性时，默认缓存 20 个序列。
- 字段
MIN_VALUE对应创建序列时 MIN_VALUE 属性的值。 - 字段
LAST_NUMBER对应创建序列时 START WITH 属性的值。
ZLM[ZLM]> CREATE SEQUENCE SEQ_T_ID MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH 1;
Query OK, 0 rows affected (0.03 sec)
ZLM[ZLM]> SELECT SEQUENCE_NAME,MIN_VALUE,LAST_NUMBER,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='ZLM';
+---------------+-----------+-------------+------------+------------+------------+
| SEQUENCE_NAME | MIN_VALUE | LAST_NUMBER | CYCLE_FLAG | ORDER_FLAG | CACHE_SIZE |
+---------------+-----------+-------------+------------+------------+------------+
| SEQ_T_ID | 1 | 1 | N | N | 20 |
+---------------+-----------+-------------+------------+------------+------------+
1 row in set (0.01 sec)
ZLM[ZLM]> drop sequence SEQ_T_ID;
Query OK, 0 rows affected (0.03 sec)
ZLM[ZLM]> CREATE SEQUENCE SEQ_T_ID MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH 10;
Query OK, 0 rows affected (0.03 sec)
ZLM[ZLM]> SELECT SEQUENCE_NAME,MIN_VALUE,LAST_NUMBER,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='ZLM';
+---------------+-----------+-------------+------------+------------+------------+
| SEQUENCE_NAME | MIN_VALUE | LAST_NUMBER | CYCLE_FLAG | ORDER_FLAG | CACHE_SIZE |
+---------------+-----------+-------------+------------+------------+------------+
| SEQ_T_ID | 1 | 10 | N | N | 20 |
+---------------+-----------+-------------+------------+------------+------------+
1 row in set (0.03 sec)
在 OB 目标端建表
基于第 1 步获取的 DDL 和第 2 步创建的序列。
ZLM[ZLM]> CREATE TABLE "ZLM"."T" (
-> "ID" NUMBER(19,0) DEFAULT SEQ_T_ID.NEXTVAL,
-> "NAME" VARCHAR2(30 BYTE),
-> CONSTRAINT "PRIMARY" PRIMARY KEY ("ID"));
Query OK, 0 rows affected (0.15 sec)
通常表结构及数据都是通过 OMS 来完成迁移的，很少会直接用 DBCAT 生成的 DDL 建表语句去目标端手动建表，除了一些较特殊的场景，如以上这种给字段增加缺省属性为序列值情况。
建表时注意：
应将表名和字段名都改为大写，因为 Oracle 中严格区分数据库对象的大小写。
ID 列的 DEFAULT 值，指定为第 2 步所创建序列的下一个值，即：SEQ_T_ID.NEXTVAL
使用 DataX 迁移数据
DataX 是阿里开源的离线数据同步工具，支持多种异构数据源，可以通过 OB 的 Reader 和 Writer 插件实现 OB 与异构数据库之间的数据迁移。
-- 创建 DataX 配置文件（存放在 dataX 的 ./job 目录下）
cat t.json
{
"job": {
"setting": {
"speed": {
"channel": 4
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"con服务器托管网tent": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "zlm",
"password": "zlm",
"column": [
"*"
],
"connection": [
{
"table": [
"t"
],
"jdbcUrl": ["jdbc:mysql://10.186.60.68:3332/zlm?useUnicode=true&characterEncoding=utf8"]
}
]
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"obWriteMode": "insert",
"column": [
"*"
],
"preSql": [
"truncate table T"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||jingbo_ob:ob_oracle||_dsc_ob10_dsc_||jdbc:oceanbase://10.186.65.22:2883/ZLM?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
"table": [
"T"
]
}
],
"username": "ZLM",
"password":"zlm",
"writerThreadCount":10,
"batchSize": 1000,
"memstoreThreshold": "0.9"
}
}
}
]
}
}
-- 执行数据迁移
./bin/datax.py job/t.json
DataX (20220610-external), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
full db is not specified.
schema sync is not specified.
java -server -Xms4g -Xmx16g -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/home/admin/datax3/log -DENGINE_VERSION=20220610-external -Xms4g -Xmx16g -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/home/admin/datax3/log -Dloglevel=info -Dproject.name=di-service -Dfile.encoding=UTF-8 -Dlogback.statusListenerClass=ch.qos.logback.core.status.NopStatusListener -Djava.security.egd=file:///dev/urandom -Ddatax.home=/home/admin/datax3 -Dlogback.configurationFile=/home/admin/datax3/conf/logback.xml -classpath /home/admin/datax3/lib/*:. -Dlog.file.name=in_datax3_job_t_json com.alibaba.datax.core.Engine -mode standalone -jobid -1 -job /home/admin/datax3/job/t.json -fulldb false -schema false
2023-08-16 14:58:41.088 [main] INFO Engine - running job from /home/admin/datax3/job/t.json
2023-08-16 14:58:41.374 [main] INFO VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2023-08-16 14:58:41.382 [main] INFO Engine - the machine info =>
略...
2. record average count and max count task info :
PHASE | AVERAGE RECORDS | AVERAGE BYTES | MAX RECORDS | MAX RECORD`S BYTES | MAX TASK ID | MAX TASK INFO
READ_TASK_DATA | 3 | 6B | 3 | 6B | 0-0-0 | t,jdbcUrl:[jdbc:mysql://10.186.60.68:3332/zlm]
2023-08-16 14:58:45.189 [job-0] INFO MetricReportUtil - reportJobMetric is turn off
2023-08-16 14:58:45.189 [job-0] INFO StandAloneJobContainerCommunicator - Total 3 records, 6 bytes | Speed 3B/s, 1 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2023-08-16 14:58:45.190 [job-0] INFO LogReportUtil - report datax log is turn off
2023-08-16 14:58:45.190 [job-0] INFO JobContainer -
任务启动时刻 : 2023-08-16 14:58:41
任务结束时刻 : 2023-08-16 14:58:45
任务总计耗时 : 3s
任务平均流量 : 3B/s
记录写入速度 : 1rec/s
读出记录总数 : 3
读写失败总数 : 0
2023-08-16 14:58:45.190 [job-0] INFO PerfTrace - reset PerfTrace.
验证效果
验证主键列能否实现自增。
SYS[ZLM]> select * from t;
+----+------+
| ID | NAME |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.01 sec)
SYS[ZLM]> insert into t(name) values('d');
Query OK, 1 row affected (0.02 sec)
SYS[ZLM]> select * from t;
+----+------+
| ID | NAME |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
4 rows in set (0.00 sec)
新插入的数据每次都会先获取 ID 列上序列的 NEXTVAL 值，于是就实现了主键自增的需求。
使用自定义序列的 NEXTVAL 作为主键列的 DEFAULT 值后，不必关心源端表上记录的自增列最大值，将表迁移过去后，直接插入新数据时，不会与原来的数据冲突。
方案二
利用 GENERATED BY DEFAULT AS IDENTITY 属性生成序列的方案是否好用？先来看一个测试吧！
-- 删除并重建测试表
ZLM[ZLM]> DROP TABLE T;
Query OK, 0 服务器托管网rows affected (0.10 sec)
ZLM[ZLM]> CREATE TABLE "ZLM"."T" (
-> "ID" NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 999999,
-> "NAME" VARCHAR2(30 BYTE),
-> CONSTRAINT "PRIMARY" PRIMARY KEY ("ID"));
Query OK, 0 rows affected (0.15 sec)
-- 查看序列
ZLM[ZLM]> SELECT SEQUENCE_NAME,MIN_VALUE,LAST_NUMBER,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='ZLM';
+-----------------+-----------+-------------+------------+------------+------------+
| SEQUENCE_NAME | MIN_VALUE | LAST_NUMBER | CYCLE_FLAG | ORDER_FLAG | CACHE_SIZE |
+-----------------+-----------+-------------+------------+------------+------------+
| SEQ_T_ID | 1 | 21 | N | N | 20 |
| ISEQ$$_50034_16 | 1 | 1 | N | N | 20 |
+-----------------+-----------+-------------+------------+------------+------------+
2 rows in set (0.00 sec)
# 此时，系统自动创建了名为 ISEQ$$_50034_16 的序列，其他默认值与自定义创建的序列一致
-- 查看表结构
ZLM[ZLM]> desc t;
+-------+--------------+------+-----+------------------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+------------------+-------+
| ID | NUMBER(19) | NO | PRI | SEQUENCE.NEXTVAL | NULL |
| NAME | VARCHAR2(30) | YES | NULL| NULL | NULL |
+-------+--------------+------+-----+------------------+-------+
2 rows in set (0.02 sec)
# 注意，ID 列的 DEFAULT 值为 SEQUENCE.NEXTVAL ，而不是 ISEQ$$_50034_16.NEXTVAL
-- 重新导入数据
./bin/datax.py job/t.json
略 ...
-- 插入数据
ZLM[ZLM]> insert into t(name) values('d');
ORA-00001: unique constraint '1' for key 'PRIMARY' violated
ZLM[ZLM]> insert into t(name) values('d');
ORA-00001: unique constraint '2' for key 'PRIMARY' violated
ZLM[ZLM]> insert into t(name) values('d');
ORA-00001: unique constraint '3' for key 'PRIMARY' violated
ZLM[ZLM]> insert into t(name) values('d');
Query OK, 1 row affected (0.01 sec)
ZLM[ZLM]> select "ISEQ$$_50034_16".CURRVAL from dual;
+---------+
| CURRVAL |
+---------+
| 4 |
+---------+
1 row in set (0.00 sec)
表中有 3 条数据，当执行插入时，START WITH 实际上还是从默认值 1 开始的。
每次执行插入，ID 都会获取序列的 NEXTVAL 值，直到执行至第 4 次，未与表中已有记录冲突，才能插入成功。
对于这种场景，解决方案有两种，不过都比较繁琐：
- 插入记录前先获取当前序列的 NEXTVAL 值（需多次执行，执行次数 = 源端表记录数）。
- 创建序列时根据源端表上自增列最大值来指定 START WITH 属性。
注意：
使用 GENERATED BY DEFAULT AS IDENTITY 属性生成的序列无法直接删除，会报错。
ORA-32794: cannot drop a system-generated sequence
获取表中自增列最大值
方法 1：MAX 函数
zlm@10.186.60.68 [zlm]> SELECT MAX(id)+1 as AUTO_INCREMENT FROM t;
+----------------+
| AUTO_INCREMENT |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
方法 2：系统视图
zlm@10.186.60.68 [zlm]> select AUTO_INCREMENT from information_schema.tables where table_name='t';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
方法3：show create table 命令
zlm@10.186.60.68 [zlm]> show create table tG
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
脚本初始化序列的一个示例
-- 删除并重建表
ZLM[ZLM]> drop table t;
Query OK, 0 rows affected (0.02 sec)
ZLM[ZLM]> CREATE TABLE "ZLM"."T" (
-> "ID" NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 999999,
-> "NAME" VARCHAR2(30 BYTE),
-> CONSTRAINT "PRIMARY" PRIMARY KEY ("ID"));
Query OK, 0 rows affected (0.04 sec)
-- 导入数据
./bin/datax.py job/t.json
略 ...
-- 执行脚本并确认返回结果正常
[root@10-186-65-73 ~]# cat init_sequence.sh
#!/bin/bash
## 获取当前表自增列最大值
i=$(obclient -h10.186.60.68 -P3332 -uzlm -pzlm -Nse "SELECT MAX(id)+1 FROM zlm.t;" 2>/dev/null | head -1)
## 循环执行SQL初始化序列值
for ((j=1; j/dev/null 2>&1
done
[root@10-186-65-73 ~]# sh init_sequence.sh
[root@10-186-65-73 ~]# echo $?
0
-- 执行插入
ZLM[ZLM]> insert into t(name) values('d');
Query OK, 1 row affected (0.01 sec)
序列经过初始化处理后，当完成数据导入并直接插入新增记录时，就不会再产生唯一性冲突的报错了。同样地，先用脚本获取到自增列的最大值，在创建序列时指定 START WITH 与自增列最大值一致，也可以解决以上问题，这里不展开了。
总体而言，GENERATED BY DEFAULT AS IDENTITY 属性创建（方案二）的序列不如自定义序列（方案一）好用。
总结
本文验证并阐述了在 OB Oracle 中实现自增主键的两种方法：创建自定义序列和利用 GENERATED BY DEFAULT AS IDENTITY 属性生成序列。
方案一
创建自定义序列的时，需要为每张有自增列的表创建一个单独的序列，序列名建议与表名关联，但无需关注 START WITH 的取值。当插入新记录时，会自动去获取下一个可用的序列值。
方案二
利用 GENERATED BY DEFAULT AS IDENTITY 属性生成序列时，存在一些限制：
- 因序列由系统自动创建并管理，需要查询系统视图才能获取序列名，无法与业务表名对应。
- 创建序列时需要根据自增列最大值来指定 START WITH 的取值，当有大量表需要处理时，较繁琐。
利用 GENERATED BY DEFAULT AS IDENTITY 属性生成的序列名，在内部有一个计数器，会累计增加，即使删除了原来的序列，原有的名字也不会被重用。删除表时，会自动清理由 GENERATED BY DEFAULT AS IDENTITY 属性生成的序列（直接删除该序列会报错），但不会影响之前创建的其他自定义序列。
采用 GENERATED BY DEFAULT AS IDENTITY 属性生成序列的方案时，还要额外考虑源端待迁移表当前自增列最大值的问题，这无疑增加了迁移的复杂度。
综上所述，更推荐使用自定义序列实现自增主键的方案。
更多技术文章，请访问：https://opensource.actionsky.com/
关于 SQLE
爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，支持多场景审核，支持标准化上线流程，原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。
SQLE 获取
| 类型 | 地址 |
|---|---|
| 版本库 | https://github.com/actiontech/sqle |
| 文档 | https://actiontech.github.io/sqle-docs/ |
| 发布信息 | https://github.com/actiontech/sqle/releases |
| 数据审核插件开发文档 | https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse |
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
