当前位置： 首页 > news >正文

MySQL 迁移 OceanBase 的 Oracle模式中，实现自增主键的方法

news 来源：原创 2024/9/20 6:10:59

本文作者：赵黎明，爱可生 MySQL DBA 团队成员，熟练掌握Oracle、MySQL等数据库系统，擅长对数据库性能问题的诊断，以及事务与锁机制的分析等。负责解决客户在MySQL及爱可生自主研发的DMP平台日常运维中所遇到的各种问题，并且对开源数据库相关的技术抱有浓厚的兴趣。

背景

在将MySQL迁移到OceanBase Oracle模式的过程中，我们经常会面临如何在OB Oracle中实现自增主键的问题。OB社区中已有一些文章为我们提供了相应的解决方案，可以作为我们迁移过程中的参考。

本文将从实践角度出发，验证并总结一个比较可行的实施方案。

方案一

我们将通过创建创建自定义序列的方式来实现自增主键。

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}},"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' violatedZLM[ZLM]> insert into t(name) values('d'); 
ORA-00001: unique constraint '2' for key 'PRIMARY' violatedZLM[ZLM]> insert into t(name) values('d'); 
ORA-00001: unique constraint '3' for key 'PRIMARY' violatedZLM[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++))
doobclient -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 属性生成序列的方案时，还要额外考虑源端待迁移表当前自增列最大值的问题，这无疑增加了迁移的复杂度。

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

相关文章：

  • 北京网站建设多少钱？
  • 辽宁网页制作哪家好_网站建设
  • 高端品牌网站建设_汉中网站制作
  • 【网安第一章】——信息收集
  • RCE---无字母数字webshell
  • 基于php的图书管理系统 可学习使用
  • 数字孪生平台：构建智慧未来，重塑空间智能生态的钥匙
  • Python Flask 与 Node.js Express
  • TM1652段码屏芯片全解析——概况，性能，MCU连接，样例代码
  • 【数据结构】六、图：6.图的最短路径（BFS 算法、迪杰斯特拉(Dijkstra)算法、弗洛伊德(Floyd)算法）
  • 大数据当中常用的一些技术
  • 算法——动态规划：0/1 背包问题
  • springboot系列十二:拦截器和文件上传
  • Git使用错误分析
  • 如何保证电源的长期稳定性？
  • PHP开启多进程
  • SpringBoot基础(二)：配置文件详解
  • 序列建模之循环和递归网络 - 引言篇
  • (ckeditor+ckfinder用法)Jquery,js获取ckeditor值
  • 【翻译】Mashape是如何管理15000个API和微服务的（三）
  • C++类的相互关联
  • HTTP中GET与POST的区别 99%的错误认识
  • in typeof instanceof ===这些运算符有什么作用
  • IP路由与转发
  • JavaScript 是如何工作的:WebRTC 和对等网络的机制！
  • mockjs让前端开发独立于后端
  • NSTimer学习笔记
  • PaddlePaddle-GitHub的正确打开姿势
  • PHP 小技巧
  • PHP那些事儿
  • Python语法速览与机器学习开发环境搭建
  • Theano - 导数
  • vuex 笔记整理
  • 第十八天-企业应用架构模式-基本模式
  • 基于Dubbo+ZooKeeper的分布式服务的实现
  • 经典排序算法及其 Java 实现
  • 前端js -- this指向总结。
  • 区块链技术特点之去中心化特性
  • TPG领衔财团投资轻奢珠宝品牌APM Monaco
  • 如何通过报表单元格右键控制报表跳转到不同链接地址 ...
  • #define与typedef区别
  • #QT 笔记一
  • (day18) leetcode 204.计数质数
  • （Redis使用系列） Springboot 使用Redis+Session实现Session共享 ，简单的单点登录 五
  • （二）windows配置JDK环境
  • （附源码）springboot工单管理系统 毕业设计 964158
  • （三）Honghu Cloud云架构一定时调度平台
  • (转)Groupon前传：从10个月的失败作品修改，1个月找到成功
  • (转)清华学霸演讲稿：永远不要说你已经尽力了
  • （转）我也是一只ＩＴ小小鸟
  • (自用)网络编程
  • ./mysql.server: 没有那个文件或目录_Linux下安装MySQL出现“ls: /var/lib/mysql/*.pid: 没有那个文件或目录”...
  • .Net(C#)自定义WinForm控件之小结篇
  • .NET基础篇——反射的奥妙
  • .net解析传过来的xml_DOM4J解析XML文件
  • .net中应用SQL缓存（实例使用）
  • .php结尾的域名,【php】php正则截取url中域名后的内容
  • .xml 下拉列表_RecyclerView嵌套recyclerview实现二级下拉列表，包含自定义IOS对话框...