Oracle 到 TDSQL Mysql数据迁移

Oracle 到 TDSQL Mysql数据迁移

一、背景

什么是“去IOE化”？又为什么要“去IOE化”？

IOE之中，I是指服务器提供商IBM，O是指数据库软件提供商Oracle，E则是指存储设备提供商EMC，三者构成了一个从软件到硬件的企业数据库系统。

从上世纪90年代起，由这“三座大山”构成的数据库系统在中国市场占垄断地位长达20年之久。借着国家为促进进电信行业发展而提出的“九七工程”这股东风，其市场占比一度逼近96%。即使到了2015年，仅Oracle一家的市场份额也还有56%之多。而所谓的“去IOE化”正是要消除这三家公司影响。再加上当前美国对中国的限制越来越多，企业去 “IOE” 的需求也越来越迫切了，特别是去 Oracle 数据库。

那作为 Oracle 数据库的替代品，各个企业的选择都不一样，有的使用 PostgreSQL，有的使用开源 MySQL。但是，为了加强自主可控，一些金融企业开始使用国产数据库，如腾讯云的 TDSQL，基于 MySQL 分支进行开发，完全兼容 MySQL，因其高可用性强、数据一致性高、稳定性好，以及腾讯的强大研发能力（避免开源分支闭源带来的风险），TDSQL 也是非常多金融企业的首选数据库。

那对于大量使用 Oracle 数据库的金融企业来说，如何从 Oracle 迁移到 TDSQL，是一个企业必然要面临的问题。

---

1.1 TDSQL 文档

https://cloud.tencent.com/document/product/557/8765

1.1.1 TDSQL使用限制

大特性限制

• 🚫不支持自定义函数、事件、表空间

• 🚫不支持视图、存储过程、触发器、游标

• 🚫不支持外键、自建分区

• 不支持复合语句，如 BEGIN END、LOOP

• 不支持主备同步相关的 SQL

小语法限制

DDL

• 不支持 CREATE TABLE … SELECT

• 不支持 CREATE TEMPORARY TABLE

• 不支持 CREATE/DROP/ALTER SERVER/LOGFILE GROUP

• 不支持 ALTER 对分表键（shardkey）进行改名

DML

• 不支持 SELECT INTO OUTFILE/INTO DUMPFILE/INTO var_name

• 不支持 query_expression_options，如：HIGH_PRIORITY/STRAIGHT_JOIN/SQL_SMALL_RESULT/SQL_BIG_RESULT/SQL_BUFFER_RESULT/SQL_CACHE/SQL_NO_CACHE/SQL_CALC_FOUND_ROWS

• 🚫不支持不带列名的 INSERT/REPLACE

• 不支持全局的 DELETE/UPDATE 使用 ORDER BY/LIMIT（版本>=1.14.4支持）

• 🚫不支持不带 WHERE 条件的UPDATE/DELETE

• 不支持 LOAD DATA/XML

• 不支持 SQL 中使用 DELAYED 和 LOW_PRIORITY，没有效果

• 🚫不支持 INSERT … SELECT（版本>1.14.4支持）

• 不支持 SQL 中对于变量的引用和操作，如：SET @c=1, @d=@c+1; SELECT @c, @d

• 不支持 index_hint

• 不支持 HANDLER/DO

管理 SQL

• 不支持 ANALYZE/CHECK/CHECKSUM/OPTIMIZE/REPAIR TABLE，需要用透传语法

• 不支持 CACHE INDEX

• 不支持 FLUSH

• 不支持 KILL

• 不支持 LOAD INDEX INTO CACHE

• 不支持 RESET

• 不支持 SHUTDOWN

• 不支持 SHOW BINARY LOGS/BINLOG EVENTS

• 不支持 SHOW WARNINGS/ERRORS和LIMIT/COUNT 的组合

• 不支持 SHOW GRANTS FOR CURRENT_USER 语句，需使用 SHOW GRANTS FOR <用户名称>进行查询

其他限制

默认支持最大建表数量为5000，如需超越该限制，可 提交工单 申请。

---

二 、迁移方案

2.1 迁移目标

由于 TDSQL 完全兼容 MySQL，迁移方案可参考 Oracle 到 MySQL 的迁移。Oracle 到 MySQL 的迁移是一个复杂过程，包含数据库对象迁移和数据库操作代码迁移。由于两个部分涉及的内容都较多，本文主要分享数据库对象中的表迁移。

2.2 Oracle 和 MySQL 数据格式映射关系

进行表迁移前，得了解 Oracle 和 MySQL 的数据类型差异，以及它们之间的映射关系，这样才便于解决迁移过程中遇到的问题，同时，对后续的应用开发更有帮助。

JdbcType与Oracle、MySql数据类型对应列表

JdbcType	Oracle	MySql
CHAR	CHAR	CHAR
VARCHAR	VARCHAR	VARCHAR
LONGVARCHAR	LONG VARCHAR/LONGVARBINARY	LONGVARCHAR
CLOB	CLOB	TEXT
NCHAR	NCHAR
NVARCHAR
NCLOB	NCLOB
TINYINT		TINYINT
SMALLINT	SMALLINT	SMALLINT
INTEGER	INTEGER	INTEGER
BIGINT		BIGINT
DECIMAL	DECIMAL/NUMBER	DECIMAL
DOUBLE	NUMBER	DOUBLE
FLOAT	FLOAT	FLOAT
REAL	REAL	REAL
NUMERIC	NUMERIC/NUMBER	NUMERIC
DATE	DATE	DATE
TIME		TIME
TIMESTAMP	TIMESTAMP	TIMESTAMP/DATETIME
BLOB	BLOB	BLOB
BIT		BIT
BOOLEAN
CURSOR
BINARY
LONGVARBINARY
VARBINARY
ARRAY
STRUCT
OTHER
UNDEFINED
NULL

下图是 Oracle 官方中介绍的使用 SQL Developer 工具进行迁移时，Oracle 和 MySQL 数据类型的映射关系 ，见https://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm#BABHHAJC。

[Oracle Database SQL Developer MySQL迁移补充信息.pdf](./Oracle Database SQL Developer MySQL迁移补充信息.pdf)

• Table 2-4 Default Data Type Mappings Used by Oracle SQL Developer

MySQL Data Type	Oracle Data Type
BIGINT	NUMBER(19, 0)
BIT	RAW
BLOB	BLOB, RAW
CHAR	CHAR
DATE	DATE
DATETIME	DATE
DECIMAL	FLOAT (24)
DOUBLE	FLOAT (24)
DOUBLE PRECISION	FLOAT (24)
ENUM	VARCHAR2
FLOAT	FLOAT
INT	NUMBER(10, 0)
INTEGER	NUMBER(10, 0)
LONGBLOB	BLOB, RAW
LONGTEXT	CLOB, RAW
MEDIUMBLOB	BLOB, RAW
MEDIUMINT	NUMBER(7, 0)
MEDIUMTEXT	CLOB, RAW
NUMERIC	NUMBER
REAL	FLOAT (24)
SET	VARCHAR2
SMALLINT	NUMBER(5, 0)
TEXT	VARCHAR2, CLOB
TIME	DATE
TIMESTAMP	DATE
TINYBLOB	RAW
TINYINT	NUMBER(3, 0)
TINYTEXT	VARCHAR2
VARCHAR	VARCHAR2, CLOB
YEAR	NUMBER

2.2.1 列默认值

MySQL 与 Oracle 的不同之处在于它处理不允许 NULL 值的列的默认值的方式。

在MySQL中，对于不允许NULL值的列，并且在向表中插入数据时没有为该列提供数据，MySQL会为该列确定一个默认值。此默认值是列数据类型的隐式默认值。但是，如果启用严格模式，MySQL 会生成错误，并且对于事务表，它会回滚插入语句。

在Oracle中，当向表中插入数据时，必须为所有不允许NULL值的列提供数据。Oracle 不会为具有 NOT NULL 约束的列生成默认值。

2.2.2 字符数据类型

MySQL 和 Oracle 在支持的字符类型以及存储和检索字符类型值的方式方面存在一些差异。

对于长度小于 65,535 字节的字符类型，MySQL 支持 CHAR 和 VARCHAR 类型。CHAR 类型的最大长度为 255 字节，从 MySQL 3.23 开始，它也可以声明为 0 字节的长度。在MySQL 5.0.3之前，VARCHAR类型的长度规范与CHAR类型相同。从 MySQL 5.0.3 开始，VARCHAR 类型的最大长度为 65,535 字节。Oracle 支持四种字符类型：CHAR、NCHAR、NVARCHAR2 和 VARCHAR2。所有 Oracle 字符类型可以声明的最小长度是 1 个字节。CHAR 和 NCHAR 允许的最大大小为 2,000 字节，NVARCHAR2 和 VARCHAR2 允许的最大大小为 4,000 字节。

MySQL CHAR 值在存储时用空格右填充至指定长度，并且在检索值时删除尾随空格。另一方面，VARCHAR 值使用给定的尽可能多的字符来存储，但在 MySQL 5.0.3 之前，存储和检索值时会删除尾随空格。如果该值短于列长度，Oracle 会将其 CHAR 和 NCHAR 类型的值空白填充到列长度，并且在检索时不会删除尾随空格。对于 NVARCHAR2 和 VARVHAR2 数据类型列，Oracle 完全按照给定的方式存储和检索值，包括尾随空格。

如果为字符类型列分配的值超过其指定长度，则 MySQL 会截断该值并且不会生成错误，除非设置了 STRICT SQL 模式。如果分配给字符类型列的值超过其指定长度，Oracle 会生成错误。

在 MySQL 中，每个字符类型（CHAR、VARCHAR 和 TEXT）列都有一个列字符集和排序规则。如果列定义中未显式定义字符集或排序规则，则隐含表字符集或排序规则（如果指定）；否则，选择数据库字符或排序规则。在 Oracle 中，CHAR 和 VARCHAR2 类型的字符集由数据库字符集定义，而 NCHAR 和 NVARCHAR 类型的字符集由国家字符集定义。

在 MySQL 中声明 CHAR 或 VARCHAR 类型时，对于 MySQL 4.1 及更高版本，默认长度语义是字符而不是字节。在 Oracle 中，CHAR 和 VARCHAR2 类型的默认长度语义是字节，NCHAR 和 NVARCHAR2 类型的默认长度语义是字符。

SQL Developer 将分别将 MySQL CHAR 和 VARCHAR 类型映射到 Oracle CHAR 和 VARCHAR2 类型。SQL Developer 将根据保存为相应 MySQL CHAR 和 VARCHAR 数据类型列指定的最大长度所需的字节数来确定 Oracle CHAR 和 VARCHAR2 数据类型列的最大字节数。如果 MySQL VARCHAR2 列的数据超过 4000 字节，请将该列转换为 Oracle CLOB 数据类型列。

M 表示最大显示尺寸。最大合法显示大小为 255。对 L 的引用适用于浮点类型并指示小数点后的位数。

MySQL	Size	Oracle
BLOB	L + 2 Bytes whereas L<2^16	RAW, BLOB
CHAR(m)	M Bytes, 0<=M<=255	CHAR
ENUM (VALUE1, VALUE2, …)	1 或 2 个字节，具体取决于枚举的数量。值（最多 65535 个值）
LONGBLOB	L + 4 Bytes whereas L < 2 ^ 32	RAW, BLOB
LONGTEXT	L + 4 Bytes whereas L < 2 ^ 32	RAW, CLOB
MEDIUMBLOB	L + 3 Bytes whereas L < 2^ 24	RAW, BLOB
MEDIUMTEXT	L + 3 Bytes whereas L < 2^ 24	RAW, CLOB
SET (VALUE1, VALUE2, …)	1、2、3、4 或 8 字节，具体取决于集合成员的数量（最多 64 个成员）
TEXT	L + 2 字节而 L < 2^16	VARCHAR2, CLOB
TINYBLOB	L + 1 Bytes whereas L<2 ^8	RAW, BLOB
TINYTEXT	L + 1 Bytes whereas L<2 ^8	VARCHAR2
VARCHAR(m)	L+1 字节，而 MySQL 5.0.3 之前的 L<=M 和 0<=M<=255 （MySQL 5.0.3 及更高版本中 0 <= M <= 65535；有效最大长度为 65,532 字节）	VARCHAR2, CLOB

2.2.3 数字类型

MySQL	Size	Oracle
BIGINT	8 Bytes	NUMBER (19,0)
BIT	大约 (M+7)/8 字节	RAW
DECIMAL(M,D)	如果 D > 0，则为 M+2 字节；如果 D = 0，则为 M+1 字节（如果 M < D，则为 D+2）	FLOAT(24), BINARY_FLOAT
DOUBLE	8 Bytes	FLOAT(24), BINARY_FLOAT, BINARY_DOUBLE
DOUBLE PRECION	8 Bytes	FLOAT(24), BINARY_DOUBLE
FLOAT(25<=X <=53)	8 Bytes	FLOAT(24), BINARY_FLOAT
FLOAT(X<=24)	4 Bytes	FLOAT, BINARY_FLOAT
INT	4 Bytes	NUMBER (10,0)
INTEGER	4 Bytes	NUMBER (10,0)
MEDIUMINT	3 Bytes	NUMBER (7,0)
NUMERIC	如果 D > 0，则为 M+2 字节；如果 D = 0，则为 M+1 字节（如果 M < D，则为 D+2）	NUMBER
REAL	8 Bytes	FLOAT(24), BINARY_FLOAT
SMALLINT	2 Bytes	NUMBER(5,0)
TINYINT	1 Byte	NUMBER(3,0)

2.2.4 日期和时间类型

下表比较了MySQL和Oracle的日期和时间类型：

MySQL	Size	Oracle
DATE	3 Bytes	DATE
DATETIME	8 Bytes	DATE
TIMESTAMP	4 Bytes	DATE
TIME	3 Bytes	DATE
YEAR	1 Byte	NUMBER

2.3 迁移原理

Oracle 到 TDSQL 迁移表时，主要涉及表结构迁移和表内容迁移。

2.3.1 表结构迁移

在知道了表数据类型的对应关系后，就可以进行表结构迁移了。从 Oracle 导出表结构定义语句，然后按照对应关系修改数据类型后，在 TDSQL 中执行建表语句。而此迁移方式，可以手动进行内容修改、也可以通过一些工具进行批量替换数据类型。

2.3.2 表数据迁移

表数据迁移可以在 Oracle 端进行数据导出，通过各种客户端工具，如 Navicat、DBeaver（开源免费）等，进行逻辑导出，得到数据内容的 CSV 或 SQL 等文件格式。

然后在 TDSQL 端，通过 mysql 的命令行进行导入，如 load data、mysql 或 mysqlsh 等工具。建议使用 mysqlsh，其可以导入 csv 文件，并可以通过使用并行提高导入速度。

2.3.3 迁移工具

另外，对于 MySQL、PostgreSQL 等数据库类型，也可直接通过腾讯云控制台提供的数据迁移的能力，执行对 TDSQL 的不停机迁移。

除了分步骤迁移外，还有一些集成迁移工具，但其内部原理也基本基于上述思路。常用的集成迁移工具包括 Oracle 的 OGG、开源的 KETTLE、商用的 Navicat 等。

1. OGG 的优势是支持异构数据库的迁移、支持数据实时同步且能进行数据回写，但是 OGG 需要单独配置且配置非常复杂，除非是有实时同步和数据回写的需求，否则不建议使用。

2. KETTLE 的优势是开源免费和功能完善，但是其功能还不是很完善，有时一些报错很难看懂，且配置有些复杂，对于一些数据类型简单的 Oracle 数据库，可以使用此工具。

3. Navicat 是一个商业版软件，使用简单，只需通过图形化界面简单配置源端和目标端，即可轻松实现数据迁移。

2.4 迁移注意事项

从 Oracle 到 TDSQL Mysql 的迁移过程中，重要的注意事项：

1. 备份数据：

   • 在开始任何迁移之前，请务必备份源数据库和目标数据库的数据，以防意外情况发生。

2. 版本和兼容性：

   • 确保源数据库和目标数据库的版本和兼容性，以避免因版本不匹配而导致的问题。

3. 数据类型和长度：

   • 注意源数据库和目标数据库中数据类型的差异，确保进行正确的类型转换和长度处理。

4. 日期和时间处理：

   • Oracle 和 Mysql 可能在日期和时间处理上有一些差异，需要特别注意。

5. 约束和索引：

   • 确保源数据库中的约束和索引得到正确的迁移和重建。

6. 事务处理：

   • 注意源数据库和目标数据库的事务控制和锁机制可能会有所不同。

7. 特殊字符和编码：

   • 确保在迁移过程中处理特殊字符和字符编码，以确保数据的准确性。

8. 数据一致性检查：

   • 在迁移结束后，进行一些数据一致性检查，确保所有数据都已成功迁移。

9. 性能调优：

   • 在迁移过程中，注意对性能进行监控和调优，确保迁移过程不会对业务产生负面影响。

10. 日志和记录：

    • 记录迁移过程中的所有步骤和操作，以便在出现问题时进行排查。

11. 测试和验证：

    • 在迁移结束后，进行充分的测试和验证，确保迁移后的数据行为符合预期。

12. 与团队沟通：

    • 在进行迁移之前，与相关团队成员进行充分的沟通，确保所有人了解迁移的计划和步骤。

13. 文档记录：

    • 记录迁移过程的详细步骤，以便未来的参考和审查。

14. 回滚计划：

    • 准备好在迁移过程中出现问题时的回滚计划，以最小化潜在的影响。

15. 遵守合规性要求：

    • 确保在迁移过程中遵守所有合规性和安全性要求，以保护数据的安全性和完整性。

---

三、迁移过程

以下是一个完整的基于 Navicat 的从 Oracle 迁移到 TDSQL MySQL 的示例。

3.1 源端和目标端介绍

源端为 Oracle 11g 数据库

在源端 Oracle 数据库创建测试用表 “test”.“qy”

CREATE TABLE "test"."qy" (	
  "id" NUMBER NOT NULL ENABLE, 
  "name" VARCHAR2(255), 
  "test" CHAR(10), 
  "context" LONG, 
  "age" NUMBER(12,0), 
  "create_time" TIMESTAMP(6), 
  "num" CLOB, 
  "price" FLOAT(2), 
  PRIMARY KEY ("id")
） 

往表里插入测试数据

INSERT INTO "test"."qy" VALUES ('100', 'testname', 'aaaaaaaa  ', 'asdfgagasdgasdgasdglkml;wqeijkoiwertghjoqenrgklasndmvlkzsjdfgopasjetfgiowejtgowpijeg ', '99999', TO_TIMESTAMP(' 2022-08-28 19:04:40:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), 'loblobloblob', '100');  

表查询结果如下：
目标端为 TDSQL 数据库，其中 kingdb 库下面只有图中 8 张表。暂时还没有名为 qy 的表，本次主要就是迁移 qy 这张表。

3.2 迁移配置

在进行迁移前，需在 Navicat 客户端上分别连接上源端和目标端数据库，然后配置迁移策略。

3.2.1 连接源端数据库

3.2.2 连接目标端数据库

3.2.3 配置迁移策略

依次点击 工具–》数据传输

源选择连接数据库类型为 “oracle”，模式选择要传输的 schema，本示例为 test 用户，可以看到 Navicat 自动查询出 test 模式下所有对象，包括本次示例的两张表 PRODUCTS 和 qy。目标直接选择数据库类型为 tdsql，要传输的目标 database，也就是 kingdb。

3.3 迁移数据

点击确定后，即开始迁移。迁移过程中，会先检查目标端表、索引等信息，然后删除重名表，开始传输数据。日志结尾会显示是否成功，成功有 Successfully 关键字，未成功则有 Unsuccessfully 关键字。

3.4 迁移校验

3.4.1 表记录校验

在目标端数据库查询，结果的记录数也是 1 条，数据内容与源端数据内容一致。

3.4.2 表结构校验

首先检查迁移后表结构信息，通过腾讯云数据库管理功能，看到表结构定义如下：

获取表结构定义的 DDL 语句

CREATE TABLE `qy` (
  `id` decimal(65,30) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `test` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `context` longtext COLLATE utf8mb4_general_ci,
  `age` decimal(12,0) DEFAULT NULL,
  `create_time` datetime(6) DEFAULT NULL,
  `num` longtext COLLATE utf8mb4_general_ci,
  `price` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

通过对比源端和目标端表结构发现，NUMBER 转换为 decimal，VARCHAR2 转换为 varchar，CHAR 还是 CHAR,LONG 转换为 longtext，TIMESTAMP 转换为 datetime，CLOB 转换为 longtext，FLOAT 转换为 double。数据库类型的转换与 SQL Developer 的结果一致，符合预期。从这里也可以看到，大多数工具的数据类型转换思路，如果是我们自行通过工具或脚本迁移，这些经验也是值得参考的。

---

四、 迁移总结

4.1 迁移问题总结

本次迁移遇到两个典型问题：

1） 字段类型不一致，无法转换

报错日志：[Err] [Dtf] 1426 - Too-big precision 7 specified for ‘create_time’. Maximum is 6.

报错原因：源端 oracle 中 create_time 字段的类型为 DATE，在 Oracle 里其精度为 7 位，而目标端 TDSQL (mysql) 的时间类型最多 6 位，所以无法导入。

解决方案：将 Oracle 的时间字段修改为 timestamp，长度修改为 6，保存后即可传输成功。

2） 源端表无主键

报错日志：

[Err] [Dtf] 3750 - Unable to create or change a table without a primary key, when the system variable ‘sql_require_primary_key’ is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

报错原因：源端 oracle 中 qy 表无主键，而目标端 TDSQL (mysql) 由于开启了 sql_require_primary_key 参数，要求所有表在创建时有主键，所以导致导入报错。

解决方案：将 qy 表的 id 字段修改为主键。

4.2 迁移经验总结

1） 做好多次失败的心理准备

迁移过程由于涉及不同数据库的各个方面的差异，会导致整个过程非常复杂。通常不会一帆风顺，会遇到各种意想不到的情况，所以也会面临多次失败的问题，大家一定要做好充分的心理准备。

2） 弄清原理、胸有成竹

虽然迁移过程艰难，但是只要弄清迁移的原理、数据库的差异并提前做好规划，那么在遇到问题时就可以对症下药、各个击破。