MySQL?遷移OB?Oracle場景中自增主鍵實踐操作

更新時間：2023年10月18日 11:51:58 作者：愛可生開源社區(qū)

這篇文章主要介紹了MySQL?遷移OB?Oracle場景中自增主鍵實踐操作詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪

背景

本文驗證并總結(jié)了在 OB Oracle 中實現(xiàn)自增主鍵的兩種方法。

在 MySQL 遷移到 OB Oracle 的場景中，通常需要考慮 OB Oracle 中自增主鍵如何實現(xiàn)的問題。之前在社區(qū)已有文章給出了一些解決方案。

本文將從解決實際問題的角度出發(fā)，驗證并總結(jié)一個比較可行的實施方案。

方案一

我們將通過創(chuàng)建創(chuàng)建自定義序列的方式來實現(xiàn)自增主鍵。

MySQL 端創(chuàng)建測試表

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 導(dǎo)出表結(jié)構(gòu)

DBCAT 是 OB 提供的命令行工具，主要用于異構(gòu)數(shù)據(jù)庫遷移場景中非表對象的 DDL 導(dǎo)出和轉(zhuǎn)換，如： Oracle 中的序列、函數(shù)、存儲過程、包、觸發(fā)器、視圖等對象。

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 會對目標(biāo)表的表結(jié)構(gòu)做轉(zhuǎn)換，使其符合 Oracle 的語法，并在導(dǎo)出的 DDL 語句中寫入一行創(chuàng)建序列的偽 SQL ，可見，此工具也是建議創(chuàng)建序列來處理 MySQL 自增列的。

在 OB 目標(biāo)端創(chuàng)建序列

對于含有自增列的每個表，都需要創(chuàng)建一個序列與之對應(yīng)。
創(chuàng)建序列時，建議以 SEQ_<表名>_<字段名> 的方式命名。
當(dāng)不指定序列的 CYCLE 和 ORDER 屬性時，其默認(rèn)值都是 N ，即：不循環(huán)，不排序。
當(dāng)不指定序列的 CACHE 屬性時，默認(rèn)緩存 20 個序列。
字段 MIN_VALUE 對應(yīng)創(chuàng)建序列時 MIN_VALUE 屬性的值。
字段 LAST_NUMBER 對應(yīng)創(chuàng)建序列時 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 目標(biāo)端建表

基于第 1 步獲取的 DDL 和第 2 步創(chuàng)建的序列。

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)

通常表結(jié)構(gòu)及數(shù)據(jù)都是通過 OMS 來完成遷移的，很少會直接用 DBCAT 生成的 DDL 建表語句去目標(biāo)端手動建表，除了一些較特殊的場景，如以上這種給字段增加缺省屬性為序列值情況。

建表時注意：

應(yīng)將表名和字段名都改為大寫，因為 Oracle 中嚴(yán)格區(qū)分?jǐn)?shù)據(jù)庫對象的大小寫。

ID 列的 DEFAULT 值，指定為第 2 步所創(chuàng)建序列的下一個值，即：SEQ_T_ID.NEXTVAL

使用 DataX 遷移數(shù)據(jù)

DataX 是阿里開源的離線數(shù)據(jù)同步工具，支持多種異構(gòu)數(shù)據(jù)源，可以通過 OB 的 Reader 和 Writer 插件實現(xiàn) OB 與異構(gòu)數(shù)據(jù)庫之間的數(shù)據(jù)遷移。

-- 創(chuàng)建 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"
          }
        }
      }
    ]
  }
}

-- 執(zhí)行數(shù)據(jù)遷移
./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 - 
任務(wù)啟動時刻          : 2023-08-16 14:58:41
任務(wù)結(jié)束時刻          : 2023-08-16 14:58:45
任務(wù)總計耗時          :         3s
任務(wù)平均流量          :        3B/s
記錄寫入速度          :       1rec/s
讀出記錄總數(shù)          :          3
讀寫失敗總數(shù)          :          0
2023-08-16 14:58:45.190 [job-0] INFO PerfTrace - reset PerfTrace.

驗證效果

驗證主鍵列能否實現(xiàn)自增。

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)

新插入的數(shù)據(jù)每次都會先獲取 ID 列上序列的 NEXTVAL 值，于是就實現(xiàn)了主鍵自增的需求。

使用自定義序列的 NEXTVAL 作為主鍵列的 DEFAULT 值后，不必關(guān)心源端表上記錄的自增列最大值，將表遷移過去后，直接插入新數(shù)據(jù)時，不會與原來的數(shù)據(jù)沖突。

方案二

利用 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)

# 此時，系統(tǒng)自動創(chuàng)建了名為 ISEQ$$_50034_16 的序列，其他默認(rèn)值與自定義創(chuàng)建的序列一致

-- 查看表結(jié)構(gòu)
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

-- 重新導(dǎo)入數(shù)據(jù)
./bin/datax.py job/t.json
略 ...

-- 插入數(shù)據(jù)
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 條數(shù)據(jù)，當(dāng)執(zhí)行插入時，START WITH 實際上還是從默認(rèn)值 1 開始的。

每次執(zhí)行插入，ID 都會獲取序列的 NEXTVAL 值，直到執(zhí)行至第 4 次，未與表中已有記錄沖突，才能插入成功。

對于這種場景，解決方案有兩種，不過都比較繁瑣：

插入記錄前先獲取當(dāng)前序列的 NEXTVAL 值（需多次執(zhí)行，執(zhí)行次數(shù) = 源端表記錄數(shù)）。
創(chuàng)建序列時根據(jù)源端表上自增列最大值來指定 START WITH 屬性。

注意：

使用 GENERATED BY DEFAULT AS IDENTITY 屬性生成的序列無法直接刪除，會報錯。

ORA-32794: cannot drop a system-generated sequence

獲取表中自增列最大值

方法 1：MAX 函數(shù)

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：系統(tǒng)視圖

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)
-- 導(dǎo)入數(shù)據(jù)
./bin/datax.py job/t.json
略 ...
-- 執(zhí)行腳本并確認(rèn)返回結(jié)果正常
[root@10-186-65-73 ~]# cat init_sequence.sh
#!/bin/bash
## 獲取當(dāng)前表自增列最大值
i=$(obclient -h10.186.60.68 -P3332 -uzlm -pzlm -Nse "SELECT MAX(id)+1 FROM zlm.t;" 2>/dev/null | head -1)
## 循環(huán)執(zhí)行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
-- 執(zhí)行插入
ZLM[ZLM]> insert into t(name) values('d');
Query OK, 1 row affected (0.01 sec)

序列經(jīng)過初始化處理后，當(dāng)完成數(shù)據(jù)導(dǎo)入并直接插入新增記錄時，就不會再產(chǎn)生唯一性沖突的報錯了。同樣地，先用腳本獲取到自增列的最大值，在創(chuàng)建序列時指定 START WITH 與自增列最大值一致，也可以解決以上問題，這里不展開了。

總體而言，GENERATED BY DEFAULT AS IDENTITY 屬性創(chuàng)建（方案二）的序列不如自定義序列（方案一）好用。

總結(jié)

本文驗證并闡述了在 OB Oracle 中實現(xiàn)自增主鍵的兩種方法：創(chuàng)建自定義序列和利用 GENERATED BY DEFAULT AS IDENTITY 屬性生成序列。

方案一

創(chuàng)建自定義序列的時，需要為每張有自增列的表創(chuàng)建一個單獨(dú)的序列，序列名建議與表名關(guān)聯(lián)，但無需關(guān)注 START WITH 的取值。當(dāng)插入新記錄時，會自動去獲取下一個可用的序列值。

方案二

利用 GENERATED BY DEFAULT AS IDENTITY 屬性生成序列時，存在一些限制：

因序列由系統(tǒng)自動創(chuàng)建并管理，需要查詢系統(tǒng)視圖才能獲取序列名，無法與業(yè)務(wù)表名對應(yīng)。
創(chuàng)建序列時需要根據(jù)自增列最大值來指定 START WITH 的取值，當(dāng)有大量表需要處理時，較繁瑣。

利用 GENERATED BY DEFAULT AS IDENTITY 屬性生成的序列名，在內(nèi)部有一個計數(shù)器，會累計增加，即使刪除了原來的序列，原有的名字也不會被重用。刪除表時，會自動清理由 GENERATED BY DEFAULT AS IDENTITY 屬性生成的序列（直接刪除該序列會報錯），但不會影響之前創(chuàng)建的其他自定義序列。

采用 GENERATED BY DEFAULT AS IDENTITY 屬性生成序列的方案時，還要額外考慮源端待遷移表當(dāng)前自增列最大值的問題，這無疑增加了遷移的復(fù)雜度。

綜上所述，更推薦使用自定義序列實現(xiàn)自增主鍵的方案。

以上就是MySQL 遷移OB Oracle場景中自增主鍵實踐操作的詳細(xì)內(nèi)容，更多關(guān)于MySQL遷移OB Oracle自增主鍵的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: