Oracle与mysql数据迁移及解决方案

最近在做oracle与mysql数据之间的迁移，迁移数据主要是针对于表的历史数据，刚开始做的时候感觉貌似很简单，毕竟没有涉及视图、过程等其他结构的迁移，但是在实施的过程还是碰到可许多麻烦，比如跨平台(linux、windows)、数据类型、批导入、通用性等，记录一下待以后再用。
1.需要注意的问题：
单引号问题：
mysql可以用双引号包起字符串，但是oracle只可以用单引号包起字符串，在之间的互导过程中必须考虑到引号的转换问题。
自增长问题：
众所周知，Mysql有自动增长的数据类型，oracle 没有。所以要在oracle 里建立一个自增长的序列号。
日期问题：
mysql有date和time两种数据类型，oracle只有date（'2007-01-01', 'yyyy-mm-dd hh24:mi:ss'）。之间的转换过程中要考虑日期的转换和转换时的日期是否有偏差。
空字符处理：
Oralce 允许字段为空，在将oracle 空字段导出到csv文件时，oracle的空字段将不显示，而在用mysql load工具导入的时候，会将空字段赋予0处理，日期也是如此。此时的解决方法有两个，一个就是假如你使用sqluldr2等工具时，只要在命令行中添加"NULL='\N'"，即可将导出的csv文件中的空值转换成'\N'；另一个就是如果使用oracle 自带的spool工具，必须在查询语句中判断是否为null，比如"select nvl(xxx,'\N') from xxx"进行转换，两种方法的结果都是讲csv中空值改成'\N'。
大小写问题：
Oracle 对大小写不敏感，而mysql针对于所使用的操作系统的敏感性来决定敏感性。所以最好统一大小写。
字符编码、保留字问题等等。
2.针对于sqludr2解决方案:
在windows下有一个oracle 导出利器，即sqludr2,。测试过它的导出速度所用时间不高于用spool导出的70%，而且不用关心格式问题。spool要设置输出格式这个是比较恶心的。
使用：
进入cmd命令行，cd 进入sqluldr工具解压目录，执行如下命令即可。
sqluldr2 user=user/user@testdb query="select f.name , f.addr from tab f"  feild=0x09 record=0x0d0x0a file=xxx.csv  NULL=\N;
record参数表示以\r\n为记录后缀。可以通过修改charset参数来改变导出字符编码。
通过csv导入mysql的，主要使用mysql import工具或者load命令。这里主要使用load命令。
使用：
LOAD DATA local INFILE 'file.csv'  REPLACE INTO TABLE tab_name CHARACTER SET gbk FIELDS TERMINATED BY ',' ENCLOSED BY '' LINESTERMINATED BY '\r\n‘ ";
在远程登录mysql使用load命令时，如果不加local的话，会提示没有权限，所以要加上local。
3.针对于spool解决方案：
使用spool的好处就是，基本不用考虑在哪个平台。因为Sqluldr2.exe只能在windows下运行。但是spool输出需要设置格式。
这里贴出能够产生csv文件的格式：
set echo off
set verify off
set termout on
set heading off
set pages 50000
set feedback off
set newpage none
set linesize 2000

set trimout on
set trimspool on

同时要注意的是在使用spool导出时要注意query的格式：
Spool filepath;
select t.id||'|'||nvl(to_char(数字类型),'\N')||'|'||nvl(to_char(),'\N')||'|'||nvl(to_char(),'\N')，nvl(to_char(日期类型,'yyyy-mm-dd  hh24:mi:ss'),'\N')||'|'||nvl(字符类型),'\N') from tab t<=200;
spool off;
exit;
这个query指定csv文件中field分割为'|'，也可以使用其他符号。
4.我的解决方法：
为了解决平台相关性，我选择使用python来写。同时python有很强大的字符串处理能力。
将平台相关的配置，通过congfig.conf文件来定义。


通过使用python ConfigParser模块读取文件信息。使用os.system()函数调用命令。
调用mysql:


调用oracle:

在oracle命令调用中，使用了tab_name_temp.sql临时文件，这是为了支持批量导入。针对于每个导出的表，这个文件的格式如下：
set echo off
set verify off
set termout on
set heading off
set pages 50000
set feedback off
set newpage none
set linesize 2000

spool /home/xxx/work3/csv/xxx.csv
select xxx||'|'||xxx||'|'||nvl(to_char(xxx),'\N')||'|'||nvl(to_char(xxx,'yyyy-mm-dd  hh24:mi:ss'),'\N')||'|'||nvl(to_char(xxx),'\N') from xxx;
spool off;
exit;

系统读取spoolsetting路径中的设置文件，将spool设置读进内存，同时将sqlfilename下的文件一并读入内存，通过拼凑字符串将数据写入到临时文件。

这样可以将setting与query分开，支持批量处理，但是也失去了一些效率。
在将数据导出后，可以进行表的check，主要是打印两个数据库相关表的count和sum值。
5.不足：
可以看出还有很多不足的地方，比如效率不高，主要瓶颈在导出效率。spool要将数据导出到磁盘，这个过程是很慢的。还有就是临时文件的问题，拼凑字符串生成临时文件可以解决批处理的问题，但是还是要写磁盘，速度当然不快了。可以通过启动多个脚本达到并行的目的，但是感觉在脚本中启动多个线程，导出和导入同时进行当然是最好的了。还是有待改进啊。