本文验证并总结了在 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 建表语句去目标端手动建表,除了一些较特殊的场景,如以上这种给字段增加缺省属性为序列值情况。

建表时注意:

  1. 应将表名和字段名都改为大写,因为 Oracle 中严格区分数据库对象的大小写。
  2. 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
      }
    },
    "content": [
      {
        "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 次,未与表中已有记录冲突,才能插入成功。

对于这种场景,解决方案有两种,不过都比较繁琐:

  1. 插入记录前先获取当前序列的 NEXTVAL 值(需多次执行,执行次数 = 源端表记录数)。
  2. 创建序列时根据源端表上自增列最大值来指定 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 t\G
*************************** 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<=$i; j++))
do
 obclient -h10.186.65.43 -P2883 -uZLM@ob_oracle#bobo_ob:1675327512 -pzlm -A -c -DZLM -Nse "select ISEQ\$\$_50037_16.nextval from dual;" 1>/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 属性生成序列的方案时,还要额外考虑源端待迁移表当前自增列最大值的问题,这无疑增加了迁移的复杂度。

综上所述,更推荐使用自定义序列实现自增主键的方案

分类: OceanBase