ORACLE和MYSQL是目前市面上使用最廣泛的兩款關(guān)系型數(shù)據(jù)庫軟件，因?yàn)閮煽顢?shù)據(jù)庫在存儲過程，函數(shù)，觸發(fā)器和sql等語法上存在較大差異，所以遷移一套完整的ORACLE到MYSQL，需要處理好不同數(shù)據(jù)類型的差異和各種編碼的差異。此文章主要分享了遷移數(shù)據(jù)上的一些方法和數(shù)據(jù)類型上的一些區(qū)別對比和選擇。

源數(shù)據(jù)庫（ORACLE數(shù)據(jù)庫）：

ip：192.169.100.107單機(jī)

目標(biāo)數(shù)據(jù)庫（mysql數(shù)據(jù)庫），目標(biāo)端需要安裝ORACLE客戶端，或者直接復(fù)制ORACLE的lib庫到目標(biāo)庫。

ip: 192.169.100.247單機(jī)

1. mysql中的date類型為日期類型（YYYY-MM-DD） 范圍：1000-01-01/9999-12-31 ，不包含時間值，所以可根據(jù)情況判斷使用date類型還是datetime類型替換oracle的date類型（取決于源端是否包含具體時間），推薦使用datetime 替換date類型。

2. 如果oracle源端使用的日期格式默認(rèn)值時（default sysdate），MYSQL端只能使用使用timestamp DEFAULT CURRENT_TIMESTAMP，因?yàn)閙sql 只有timestamp 才能使用默認(rèn)時間為系統(tǒng)時間。

3. mysql 端默認(rèn)字符集推薦使用：utf8mb4，具體根據(jù)情況而定。

   
   

   [

   load的方式遷移數(shù)據(jù)

   ]

 對于生產(chǎn)環(huán)境的數(shù)據(jù)庫，動輒上TB甚至PB級的數(shù)據(jù)量和數(shù)千張表，對于這樣的數(shù)據(jù)量和表的數(shù)量，我們就需要考慮比較快捷的方式去遷移數(shù)據(jù)。

 數(shù)據(jù)的導(dǎo)出有很多有方式，比如oralce可以使用：PL/SQL、toad、NavicatforORACLE或者其他工具導(dǎo)出數(shù)據(jù)，但是這些工具往往會因?yàn)閿?shù)據(jù)量的問題而受到各種局限，對于少量的數(shù)據(jù)時，使用這些工具是比較合適的，但是當(dāng)數(shù)據(jù)量達(dá)到海量時，不僅導(dǎo)出速度無法保證，而且無法直接落地到服務(wù)器，從而大大的限制了我們對數(shù)據(jù)的處理。

因此我推薦一個小的工具包sqlload2（或者也可以直接使用ketle將數(shù)據(jù)直接導(dǎo)出）,該工具可快速的將源數(shù)據(jù)導(dǎo)出成為txt/csv(推薦csv格式，csv格式可以更好的處理分隔符和封閉符的問題)，因?yàn)樵谡江h(huán)境中，導(dǎo)入的數(shù)據(jù)的正確性尤為重要，生產(chǎn)數(shù)據(jù)又可能出現(xiàn)各種特殊的符號，僅僅使用傳統(tǒng)的逗號作為分隔符已無法完全保證數(shù)據(jù)在導(dǎo)出或者導(dǎo)入時能正確的分隔，所以需要同時使用分隔符和封閉符，這樣才能保證數(shù)據(jù)的正確性，經(jīng)過多次驗(yàn)證建議使用特使的16進(jìn)制字符：0x07作為分隔符,并且該工具支持并行導(dǎo)出、以及多種分隔符、封閉符、自動拆分文件和通配符等等豐富的功能。

(以下模擬一個源庫包含大量數(shù)據(jù)，并且業(yè)務(wù)無法長時間停止，需要增量更新)

1. ORACLE文件導(dǎo)出工具sqlludr2

鏈接：https://pan.baidu.com/s/1JVo1BETvTJXPQQHburfVOg
提取碼：fxwf

2. ETL工具ketle

kettle可自行到官網(wǎng)下載：https://www.hitachivantara.com/en-us/products/data-management-analytics.html?source=pentaho-redirect（kettle為開源軟件，后續(xù)也有推出收費(fèi)版本）

使用sqlldr2導(dǎo)出數(shù)據(jù)并使用load加載數(shù)據(jù)：

1. 安裝ORACLE客戶端，并配置好環(huán)境變量：

安裝之后創(chuàng)建network/admin/文件夾，然后創(chuàng)建文件tnsnames.ora（用于連接源端ORACLE數(shù)據(jù)庫）

• zkl = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 1521)) ) (CONNECT_DATA = (SID = orcl ) (SERVER=DEDICATED ) 　　　 (INSTANCE_NAME = testdb1) )

2. 配置好oracle的環(huán)境變量，指定lib 庫

• export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export PATH=$PATH:$ORACLE_HOME/bin export ORACLE_SID=orcl export LD_LIBRARY_PATH=$ORACLE_HOME/lib:lib:/usr/lib export CLASSPATH=$ORACLE_HOME/JRE:$ORALCE_HOME/jlib:ORACLE_HOME/rdbms/jlib

3. 上傳sqlludr2工具并使用如下命令導(dǎo)出文件

• ./sqluldr2_linux64_10204.bin USER=用戶名/密碼@IP地址：端口號/數(shù)據(jù)庫名稱 query="查詢語句" table=表名稱 head=no charset=utf8 field=0x07 file=/sqlfile^CJ_WXMACINFO.csv log=/sqlfile/log.txt file=/sqlfile/data/WJ_WXMACINFO%B.CSV size=20MB safe=yes

常用參數(shù)說明：（更多參數(shù)可以參考：./sqluldr2_linux64_10204.binhelp=yes）

• query:query參數(shù)如果整表導(dǎo)出，可以直接寫表名，如果需要查詢運(yùn)算和where條件，query=“sql文本”，也可以把復(fù)雜sql寫入到文本中由query調(diào)用。 head：是否導(dǎo)出表頭 charset：指定字符集 field：默認(rèn)是逗號分隔符，通過field參數(shù)指定分隔符 file：導(dǎo)出的文件名 log：日志文件 size:對于大表可以輸出到多個文件中，指定行數(shù)分割或者按照文件大小分割rows=500000

示例：（導(dǎo)出db庫里面的test表里面的前10條數(shù)據(jù)，并且分4個并行線程，導(dǎo)出的文件每20M截?cái)啵?/span>

• ./sqluldr2_linux64_10204.bin USER=test/[email protected]/db query="select /*+ parallel(4) */ *from test where rownum<=100000" table=test head=no charset=utf8 field=0x07 file=/tmp/test.csv log=/tmp/log.txt file=/tmp/test%B.CSV size=20MB safe=yes

后續(xù)可寫入腳本批量導(dǎo)出。

4. 使用load在目標(biāo)數(shù)據(jù)庫批量入庫：

• load data infile /tmp/test.csv into table db.test character set utf8 fields terminated by 0x07 enclosed by ";

kettle簡介

  ETL是數(shù)據(jù)抽?。‥xtract）、清洗（Cleaning）、轉(zhuǎn)換（Transform）、裝載（Load）的過程。是構(gòu)建數(shù)據(jù)倉庫的重要一環(huán)，用戶從數(shù)據(jù)源抽取出所需的數(shù)據(jù)，經(jīng)過數(shù)據(jù)清洗,最終按照預(yù)先定義好的數(shù)據(jù)倉庫模型，將數(shù)據(jù)加載到數(shù)據(jù)倉庫中去。（典型的ETL工具：商業(yè)軟件：Informatica、IBM Datastage、Oracle ODI、Microsoft SSIS…開源軟件：Kettle、Talend、CloverETL、Kettle，Octopus …）

Kettle是一款國外開源的ETL工具，純java編寫，可以在Window、Linux、Unix上運(yùn)行，綠色無需安裝，數(shù)據(jù)抽取高效穩(wěn)定。Kettle 中文名稱叫水壺，該項(xiàng)目的主程序員MATT 希望把各種數(shù)據(jù)放到一個壺里，然后以一種指定的格式流出。

Kettle這個ETL工具集，它允許你管理來自不同數(shù)據(jù)庫的數(shù)據(jù)，通過提供一個圖形化的用戶環(huán)境來描述你想做什么，而不是你想怎么做。

Kettle中有兩種腳本文件，transformation和job，transformation完成針對數(shù)據(jù)的基礎(chǔ)轉(zhuǎn)換，job則完成整個工作流的控制。

新建轉(zhuǎn)換，配置增量更新：

1. 在核心對象中分別選擇兩個表輸入，作為舊數(shù)據(jù)源和新數(shù)據(jù)源，分別配置數(shù)據(jù)庫連接；

2. 字段選擇用來規(guī)范來源數(shù)據(jù)的格式和類型；

3. 合并記錄用于對比新舊數(shù)據(jù)源的差異，并將數(shù)據(jù)對比的結(jié)果放到標(biāo)志字段中；

4. Switch/Case用來決定差異的數(shù)據(jù)是應(yīng)該更新還是修改還是刪除。

5. 標(biāo)志字段值說明：

轉(zhuǎn)換調(diào)用

1) windows下調(diào)用kettle程序：

2) linux下調(diào)用kettle程序：

 至此使用load+kettle的的遷移和增量更新配置完成。