Oracle迁移Mysql案例分享

最新推荐文章于 2024-05-19 03:18:57 发布

北海beihai

最新推荐文章于 2024-05-19 03:18:57 发布

阅读量932

点赞数 10

文章标签： mysql 数据库 oracle

本文链接：https://blog.csdn.net/MR_Cyq_Miracle/article/details/137927773

版权

简介

基于项目要求，需要将Oracle数据库迁移至Mysql，生产环境无法使用外部迁移工具，且在完整的上线流程中需要编写流畅的上线步骤及各环节出错应对方案，整个迁移包括各数据库对象（表结构，存储过程、视图、自定义方法、触发器、序列）及所有表数据。

1.方案简介

首先，笔者使用的Mysql版本为8.0（原先使用了5.0，后发现5.0版本自带函数过少，后进行了升级），开发环境能使用各数据库管理软件（如Navicat或Dbeaver），所以大部分数据库对象迁移可在测试环境完成后导出对应脚本，关于数据迁移，笔者所在项目有较为成熟的数据卸载工具（基于java），可将各种类型的数据库数据生成dat文件（定长数据文件）及标志文件（包含表结构信息，单行数据长度，数据条目数等），故在原Oracle数据库服务器上卸载出文件后一次性将所有表的dat及标志文件打包后通过跳板机sftp至Mysql服务器，在表机构迁移完成的情况下，通过批处理工具建立批量作业，批量的将各表数据入库，流程如下：

步骤编号	步骤名称	迁移方法
1	数据库对象迁移（表结构，存储过程、视图、自定义方法、触发器、序列）	Navicat批量迁移表结构，手动迁移存储过程、视图、自定义方法、触发器、序列
2	生成全库表数据双文件，sftp至Mysql服务器	自定义卸数工具
3	数据载入Mysql数据库	批处理平台（ETL工具）

2.迁移流程详解

2.1 数据库对象迁移

需要迁移的数据库对象有表结构，存储过程、视图、自定义方法、触发器、序列，涉及大量的工作量，Oracle和Mysql在以上各个对象的语法上都存在差异且mysql无序列，需要手动编写方法实现序列功能（关于序列的方法后续有空更新），存储过程、视图、自定义方法中都需要对原先Oracle使用的方法进行修改，这里举几个例子：

2.1.1 函数差异

这里举几个常用到的函数，其实实际开发下来修改量不小，而且很多函数不是只替换函数名就可以，用法参数都有差异。

方法名	Oracle版本	Mysql版本
字符串拼接	\|\|	concat()
基于分组行专列	listagg()	group_concat()
空值判断并赋默认值	nvl()	ifnull()
日期转字符	to_char()	date_format()
字符转日期	to_date()	str_to_date()

2.1.2 存储过程、视图

存储过程、视图的修改其实是基于数据库用法特性的修改，这里也举几个常用的点：

用法	Oracle版本	Mysql版本
DDL语句	编写动态sql后使用execute immediate关键字执行	直接写
异常处理	直接在存储过程尾端抛出异常	需要现在存储过程头部定义异常类型如ExitHander等
空值的插入	''等同于null可插入任何类型字段	''为空字符串只能插入字符型字段，如日期数字等奖报错，null值可插入任何字段
存储过程更新	create or replace procedure	需要先删除再创建，格式为 drop procedure procname if exists; delimiter $$ create procedure ... end; $$ delimiter ;
覆盖更新	merge into	可考虑使用replace into，但需考虑实际场景，replace into必须建立主键

2.2 数据迁移

在使用卸数工具卸载出双文件后，开发数据文件导入脚本（shell），再使用ETL调度工具将数据文件载入库中，脚本思路为：

（1）解压数据文件

（2）读取标志文件文件头中的表信息（每行数据长度、整个文件数据条数），和实际的数据文件比对，如一致则进行下一步，如不一致提示错误信息

（3）使用sqlload将文件导入进数据库中

以下为实际开发中遇到的一些问题及解决方案：

2.2.1 数据乱码

Oracle数据库编码为GBK，Mysql为UTF-8，所以在脚本中需要对数据文件进行编码转换

2.2.2 日期类型数据载入错误

问题描述：

Mysql能直接载入大部分原Oracle时间戳、日期数据，但由于两者格式仍存在差异，有些日期或时间会被Mysql判定为错误格式，Mysql的默认配置中针对这类错误格式数据有预设的处理方式，如针对错误的日期格式，Mysql将存入0000-00-00，而这个日期在实际的使用时将产生报错。

解决方法：

针对此类表，建立日期字段类型全为字符型的中间表，再使用日期转换函数将该表数据insert进目标表

2.2.3 字段排序规则问题

问题描述：

项目起始之初，选择了Mysql5.0，其数据库默认的字符型字段排序规则为utf8_general_ci，该排序规则的特点为不区分大小写（排序规则中带ci的都不区分大小写），在首次迁移完成使用数据后造成了不少数据问题，例如两个账号分别为TEST01和test01，在此排序规则下视为同一值，将造成主键重复等问题。

解决方法：

修改排序规则为utfmb4_bin，修改方式为：

1.对表和字段进行alter操作，需要注意的是mysql的alter是对字段的所有属性进行修改，所以需要确保alter语句中包含字段类型、是否为空、默认值、字段注释、排序规则等一应俱全。

2.将数据库的默认字符型字段排序规则修改为utfmb4_bin，然后将表数据进行备份，接着对整个表进行重建，最后将备份表数据导入回原表。修改默认的排序规则方法为：

（1）查看当前字符集和排序规则

SHOW VARIABLES LIKE 'collation%';

SHOW VARIABLES LIKE 'character%';

（2）修改排序规则和编码

                set character_set_client ='utf8mb4';
                set character_set_connection ='utf8mb4';
                set character_set_database ='utf8mb4';
                set character_set_results ='utf8mb4';
                set character_set_server ='utf8mb4';
                set collation_connection='utf8mb4_bin';
                set collation_database='utf8mb4_bin';
                set collation_server='utf8mb4_bin';

（3）以上方法在数据库重启后悔回复原预设值，所以在条件允许的情况下请修改配置文件，windows下为my.ini或mysql.ini，linux下则为my.cnf或mysql.cnf