目錄

1簡介... 1

2 準(zhǔn)備... 1

2.1 環(huán)境說明... 1

3 安裝... 2

4 配置... 2

5 使用... 2

5.1 驗(yàn)證實(shí)驗(yàn)... 2

5.2 問題分析... 4

6 延展... 5

7 總結(jié)... 5

Mysql遷移到Oracle方法

1簡介

基于mysql select into outfile的方式導(dǎo)出文本文件并結(jié)合sqlldr導(dǎo)入數(shù)據(jù)到oracle里

2?準(zhǔn)備

2.1?環(huán)境說明

1 mysql版本10.3.13-MariaDB,同理其它mysql版本效果類似。

-- 查看mysql版本 SELECT VERSION() AS mysql_Version;

mysql_Version

10.3.13-MariaDB

-- mysql 字符集 show variables like '%char%';

Variable_name	Value
character_set_client	utf8
character_set_connection	utf8
character_set_database	utf8
character_set_filesystem	binary
character_set_results
character_set_server	utf8
character_set_system	utf8
character_sets_dir	E:\Program Files\MariaDB 10.3\share\charsets\

2 oracle版本為11gR2，同理其它版本效果類似。

--查看數(shù)據(jù)庫版本 SELECT * FROM v$version;

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

"CORE?? 11.2.0.4.0? Production"

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

--查看字符集 SELECT * FROM v$nls_parameters where parameter='NLS_CHARACTERSET';

PARAMETER	VALUE
NLS_CHARACTERSET	AL32UTF8

3 安裝

無.

4?配置

無

5?使用

5.1?驗(yàn)證實(shí)驗(yàn)

1) 通過mysql的 select into outfile的方式拼接SQL生成導(dǎo)出txt的語句。并將生成出來的sql語句拷貝出來并保存成export.sql

SELECT CONCAT('SELECT ', group_concat('IFNULL(',COLUMN_NAME,','''')'), ' into outfile "\/var\/lib\/mysql-files\/tmp\/data\/', table_name, '.txt','"? fields terminated by "\\t" lines terminated by ''\\r\\n'' from test.',table_name,';') from information_schema.`COLUMNS` WHERE TABLE_schema = 'test' GROUP BY TABLE_NAME

注:

這里 /var/lib/mysql-files/tmp/data可以改成其它mysql用戶權(quán)限訪問的目錄。

這里假設(shè)export.sql 保存在/var/lib/mysql-files/tmp/目錄里。

2) 通過mysql <的方式執(zhí)行外部的sql文件,這里可以將該命令封裝在shell里

#! /bin/sh mysql -uroot -ppasswd -Dtest<'/var/lib/mysql-files/tmp/export.sql' 1>/tmp/log.log

3) 如果oracle和mysql不在一臺(tái)服務(wù)器上，那么需要通過命令將txt文件拷貝到oracle服務(wù)器里。如果在一臺(tái)服務(wù)器上，則忽略這個(gè)操作。

scp -r /var/lib/mysql-files/tmp/data/*.txt root@192.168.56.32:/root/script/data/

4) 通過sql拼出sqlldr的ctl文件，拷貝執(zhí)行出來的內(nèi)容到imp.txt里

select concat('load data','\r','characterset utf8','\r','INFILE ''\/root\/script\/data\/',table_name,'.txt''','\r','Append into table ',table_name,'\r','fields terminated by '"'\\t'"'','\r','trailing nullcols','\r','(','\r', group_concat(COLUMN_NAME),');' ) from information_schema.`COLUMNS` WHERE table_schema in('test') GROUP BY TABLE_NAME

5) 通過python代碼按照“;”拆分成ctl文件。

import osdef splitFile(fileIn,dirOut):file1 = open(fileIn, 'r', encoding='utf-8')try:str=""for line in file1.readlines():if line.find('.') >0:fileName=line.split('/')[-1].replace('txt\'','ctl').strip("\n")print(fileName)file2 = open(dirOut + '\\' + fileName, 'w', encoding='utf-8')str=str+" "+lineif line[-2]==";":#line = line.strip("\n")###print(str);file2.write(str.replace(";",'').replace("GXSJC","GXSJC date \"yyyy-mm-dd hh24:mi:ss\""))str = ""file2.close()finally:file1.close()if __name__ == '__main__':splitFile(r'D:\temp\imp.txt',r'D:\temp\impCtls')

6) 生成sqlldr執(zhí)行腳本,將SQL拼接里的內(nèi)容拷貝出到Imp.sh內(nèi)。

select concat('sqlldr userid=test/passwd@Instance? rows=500000 bindsize=30720000 control=/root/script/ctl/' ,table_name ) from information_schema.tables where table_schema='test';

7) 運(yùn)行Imp.sh并結(jié)合log文件對錯(cuò)誤進(jìn)行分析。

5.2?問題分析

報(bào)字符串長度超了,oracle里的長度varchar2最長為4000，而且漢字占用3個(gè)字符，所以需要跟mysql里的varchar乘以3;這個(gè)僅限oracle字符集是UTF8的情況。

mysql導(dǎo)出txt出現(xiàn)NULL或者\(yùn)N的情況，解決方法在mysql導(dǎo)出時(shí)加IFNULL函數(shù)。當(dāng)前的導(dǎo)出腳本里已經(jīng)加了。但是建議基于mysql的字典表，對字段可能為NULL的加加上IFNULL函數(shù)，其它的不加。

?ORA-01861: literal does not match format string 需要指定時(shí)間格式;

可能的時(shí)間格式 date 'yyyy-mm-dd hh24:mi:ss'，此操作可以在ctl文件里修改,即對應(yīng)的字段后面加上時(shí)間格式轉(zhuǎn)換，如：

Col_name date 'yyyy-mm-dd hh24:mi:ss'

? ? ?4. Field in data file exceeds maximum length;

這種錯(cuò)誤可能是字段里含有中文字符，解決方法，在ctl里對有問題的字段，后面加char(4000)轉(zhuǎn)換，如：

Col_name char(4000)

? ? ? 5. ORA-01722: invalid number;

這種錯(cuò)誤可能是因?yàn)楫?dāng)前表的最后一個(gè)字段是數(shù)值型的且當(dāng)前導(dǎo)出的數(shù)據(jù)行分隔符是\r\n,即回車換行。

解決方法,導(dǎo)出的txt的行分隔符換成\n

? ? ?6.導(dǎo)入時(shí)出現(xiàn)字段和記錄對不上的問題

可能的情況是字段里含有行分隔符\r\n，處理方法是換個(gè)行分隔符或者對出錯(cuò)的字段替換\r\n，即

REPLACE(REPLACE(char(13),''),CHAR(10),'')

6?延展

無

7?總結(jié)

暫無.有問題可以咨詢?nèi)缦?#xff1a;

總結(jié)

以上是生活随笔為你收集整理的Mysql迁移到Oracle方法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。