為什么要遷移？

首先從運(yùn)營(yíng)成本考慮，用Mysql可以節(jié)約不少的費(fèi)用。另一方面，Mysql的穩(wěn)定性及功能不斷地提高與增強(qiáng)，基本上可以滿足客戶的需求，如支持多 節(jié)點(diǎn)部署，數(shù)據(jù)分區(qū)等。還有就是Mysql使用方便，比Oracle簡(jiǎn)單易用。故客戶就要求將已有的Oracle數(shù)據(jù)表與內(nèi)容遷移到Mysql來(lái)。

為什么要自己寫(xiě)腳本?
遷移的表與數(shù)據(jù)都蠻多的，有幾百?gòu)埍?。因此手工完成不太方便。也嘗試了一些免費(fèi)的遷移工具，如:MySQLMigrationTool等，發(fā)現(xiàn)轉(zhuǎn)移 的字段類型不太符合要求(可能是原來(lái)的Oracle表設(shè)計(jì)得不太好)，會(huì)導(dǎo)致數(shù)據(jù)不太完整，覺(jué)得不是太可靠，所以決定自己寫(xiě)遷移腳本放心一些，有不符合要求的也可立即調(diào)整，所以就開(kāi)始吧。

所用到的技術(shù)支持

1.php5

2.php oci8

3.mysql 5.1

遷移表結(jié)構(gòu)

獲取schema所有表

用以下語(yǔ)句可以從Oracle中獲得schema中所有的表名

SELECT table_name FROM user_tables

然后可以遍歷所有表向mysql進(jìn)行表結(jié)構(gòu)的創(chuàng)建與數(shù)據(jù)的遷移工作。

獲取單個(gè)表的所有字段與類型

用以下語(yǔ)句可以從oracle中獲得單個(gè)表的所有字段與類型

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT  

FROM USER_TAB_COLUMNS   WHERE TABLE_NAME = UPPER('{$table_name}') ORDER BY column_id ASC

這樣可以得知表字段的名稱，類型，長(zhǎng)度，是否允許為空，默認(rèn)值。因?yàn)閛racle與mysql的字段類型并不完全兼容，故需要建立字段類型的對(duì)應(yīng)關(guān)系表。

獲取單個(gè)表的Primary Foreign Unique Key用以下語(yǔ)句可以從oracle中獲得單個(gè)表的Primary Foreign Unique Key

SELECT C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, C.R_CONSTRAINT_NAME, C.DELETE_RULE, 

CC.COLUMN_NAME      FROM USER_CONSTRAINTS C, USER_CONS_COLUMNS CC 

WHERE C.TABLE_NAME=upper('{$table_name}') AND C.CONSTRAINT_TYPE!='C' 

AND C.CONSTRAINT_NAME=CC.CONSTRAINT_NAME AND C.OWNER=CC.OWNER AND C.TABLE_NAME=CC.TABLE_NAME

ORDER BY C.CONSTRAINT_TYPE, C.CONSTRAINT_NAME, CC.POSITION

獲取單個(gè)表的索引

用以下語(yǔ)句可以從oracle中獲得單個(gè)表的索引

SELECT T.INDEX_NAME,T.COLUMN_NAME,I.INDEX_TYPE FROM USER_IND_COLUMNS T,USER_INDEXES I 

WHERE T.INDEX_NAME = I.INDEX_NAME AND T.TABLE_NAME = I.TABLE_NAME AND 

T.TABLE_NAME = UPPER('{$table_name}')

這樣可以獲知表的索引名稱，被索引的字段。

將所有的表信息組裝成mysql的表結(jié)構(gòu) 有了表名，字段信息，主鍵，F(xiàn)oreign, Unique.索引等信息，就可以將表結(jié)構(gòu)建立起來(lái)。由于oracle沒(méi)有自增字段，一個(gè)變通的方法是用primary key做為mysql中的自增字段，因?yàn)樵趏racle中沒(méi)辦法準(zhǔn)確地知道哪個(gè)字段用到了seq來(lái)做自增，用這種方法也不是很準(zhǔn)確。

遷移視圖

獲取schema所有views。用以下語(yǔ)句可以從oracle中獲取schema所有views

SELECT VIEW_NAME,TEXT      FROM USER_VIEWS

這樣可以獲得view的名稱及建立view的構(gòu)成語(yǔ)句。

Oracle與Mysql不兼容的view處理

Oracle的對(duì)view的構(gòu)建語(yǔ)句比較寬松一些，mysql中對(duì)view的構(gòu)建語(yǔ)句要嚴(yán)格些，from不能從子查詢中構(gòu)建，也就是人樣的讓語(yǔ)句是不被允許的。

CREATE VIEW `view_name` AS SELECT * FROM (SELECT * FROM table_name) ...

那么處理這樣的view,有一個(gè)辦法是將子查詢?cè)俳⒊梢粋€(gè)view,將子查詢替換成新建立的view名即可。

遷移數(shù)據(jù)

數(shù)據(jù)的遷移，從Oracle數(shù)據(jù)庫(kù)中select出數(shù)據(jù)，拼裝成Mysql的insert語(yǔ)句就可以了。需要注意的兩點(diǎn)：一是oracle的日期類 型的數(shù)據(jù)格式與mysql的日期類型格式不太一致，所以要用TO_CHAR(field_name, 'YYYY-MM-DD')將其轉(zhuǎn)換成mysql的相應(yīng)的數(shù)據(jù)格式;二是oracle中的clob字段的內(nèi)容要用 oci_fetch_array($stid, OCI_ASSOC|OCI_RETURN_NULLS|OCI_RETURN_LOBS)將其取出，插入到mysql的text字段。 在mysql插入大量數(shù)據(jù)時(shí)，還要注意，在window的my.ini或my.cnf默認(rèn)max_allowed_packet是1M要將其改為

[mysqld]...max_allowed_packet = 16M...

要不會(huì)出現(xiàn)mysql goes away的錯(cuò)誤，linux版本沒(méi)有這個(gè)問(wèn)題，因?yàn)槠淠J(rèn)值就是16M。

不能遷移的內(nèi)容

Oracle中的觸發(fā)器、存儲(chǔ)過(guò)程與Mysql中是不一樣的，所以不能通過(guò)腳本程序自動(dòng)遷移過(guò)去。要手工進(jìn)行重寫(xiě)。

總結(jié)

從Oracle向Mysql數(shù)據(jù)遷移，table結(jié)構(gòu)與數(shù)據(jù)是基本上可以平移過(guò)去的，view有些還不是完全兼容oracle的語(yǔ)句，所以要對(duì)其作 出一些中間表。觸發(fā)器與存儲(chǔ)過(guò)程是不能平移的，這兩部份需要重寫(xiě)。表結(jié)構(gòu)平移過(guò)去后，看是否都比較合適，如果覺(jué)得不太好還可以通過(guò)alter等語(yǔ)句對(duì)其進(jìn) 行相應(yīng)的調(diào)整。在導(dǎo)入大量數(shù)據(jù)時(shí)還要注意一下mysql的配置文件（my.ini或my.cnf）里的max_allowed_packet值，這應(yīng)設(shè)置 得太小，以免導(dǎo)入數(shù)據(jù)時(shí)出錯(cuò)或?qū)Р贿M(jìn)去或亂碼等不穩(wěn)定因素。

【編輯推薦】

1. Oracle數(shù)據(jù)塊原理深入剖析
2. 影響SQL Server性能的三個(gè)關(guān)鍵點(diǎn)
3. Oracle數(shù)據(jù)庫(kù)設(shè)計(jì)提升性能的五條法則