這幾天做榆林政協的項目，因為關系到從MYSQL向Oracle的遷移，所以，有了一些經驗。
因為項目一開始就是用MYSQL開發的，所以沒想到會出現的問題都出現了。
一、MYSQL可以導入導出腳本，本來應該准備好數據庫的備份的初始化數據庫和初始化數據的兩個初始化腳本，經由MYSQL導出為一個full.sql的總的初始化腳本，用過mysql的都知道，導出的數據庫腳本中，包含了建庫、建表、以及插入數據的語句。需要分割成兩個：建庫、建表的初始化腳本和插入數據的初始化腳本。於是我寫了一個類，用於分割SQL腳本，在類中，我查找到create table開頭的後面緊跟著的字符串，就是表的名字，取出insert開頭的語句，改行就是插入數據的，於是，通過文件流讀取文件的每一行，如果是插入的取出來放入一個方法convertInsertSQL處理後寫入一個腳本文件insert_data.sql中，剩下的寫入一個腳本文件create_table.sql中, 方法convertInsertSQL對於插入語句做處理，因為腳本中的插入語句是批量插入的，insert into 表名(列名) values(對應值),( 對應值),( 對應值)，所以需要拆分成insert into 表名(列名) values (對應值)這樣的語句，所以我通過將前面values(前的值截取後，對剩下的所有對應數據，進行通過),(用正則分割成String數組，對數組進行循環，每次追加組成插入語句，最後將插入語句全部返回後寫入文件流。
package com.test.file;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.HashMap;
import java.util.Map;
/**
* 將mysql數據庫導出的腳本
* 分割成為建庫和建表的兩個腳本
* @author gaofei
*
*/
public class MyFileReader {
/**
* @param args
*/
public static void main(String[] args) {
String full_sql_path="C:/Documents and Settings/gaofei/桌面/最終mysql腳本(12.15).sql";
String create_sql_path="C:/Documents and Settings/gaofei/桌面/建立數據庫初始化.sql";
String insert_sql_path="C:/Documents and Settings/gaofei/桌面/數據初始化.sql";
try {
readFull_to_insert(full_sql_path, create_sql_path, insert_sql_path);
} catch (IOException e) {
System.out.println("文件讀取或寫入出錯");
e.printStackTrace();
}
// String aa="insert into `templatetype`(`id`,`templatetypename`,`deflong`,`defdate`,`defvar`) values (0,'通用模板類型',0,NULL,NULL),(1,'首頁模板類型',0,NULL,NULL),(2,'欄目模板類型',0,NULL,NULL),(3,'專題模板類型',0,NULL,NULL),(4,'內容模板類型',0,NULL,NULL),(5,'留言模板類型',0,NULL,NULL),(6,'投票模板類型',0,NULL,NULL),(7,'特殊模板類型',0,NULL,NULL);";
// String bb=full_to_part(aa);
// System.out.println(bb);
}
/**
* 將整體的導出的mysql腳本，拆分為建庫建表和插入數據的腳本
* 將其中的批量插入數據語句轉換為每條插入數據語句的腳本
* @param full_sql_path 原始全部導出的mysql腳本
* @param create_sql_path 拆出來的建庫建表的腳本
* @param insert_sql_path 拆出來的插入數據腳本
* @throws IOException
*/
private static void readFull_to_insert(String full_sql_path,String create_sql_path,String insert_sql_path) throws IOException{
File fullFile=new File(full_sql_path);
File createFile=new File(create_sql_path);
if(!createFile.exists())
createFile.createNewFile();
File insertFile=new File(insert_sql_path);
if(!insertFile.exists())
insertFile.createNewFile();
InputStreamReader isr=new InputStreamReader(new FileInputStream(fullFile), "UTF-8");
BufferedReader br=new BufferedReader(isr);
OutputStreamWriter osw_create=new OutputStreamWriter(new FileOutputStream(createFile), "UTF-8");
OutputStreamWriter osw_insert=new OutputStreamWriter(new FileOutputStream(insertFile), "UTF-8");
BufferedWriter bw_create=new BufferedWriter(osw_create);
BufferedWriter bw_insert=new BufferedWriter(osw_insert);
Map<Integer, String> allData=new HashMap<Integer,String>();
String line=null;
int num=17;
while((line=br.readLine())!=null){
String lowerLine=line.toLowerCase();
if(lowerLine.startsWith("insert")){ //在該語句下用來判斷插入數據的先後順序
if(lowerLine.indexOf("`sequenceblock`")!=-1){
allData.put(1, line);
}else if(lowerLine.indexOf("`operationlogtype`")!=-1){
allData.put(2, line);
}else if(lowerLine.indexOf("`website`")!=-1){
allData.put(3, line);
}else if(lowerLine.indexOf("`fucdefine`")!=-1){
allData.put(4, line);
}else if(lowerLine.indexOf("`role`")!=-1){
allData.put(5, line);
}else if(lowerLine.indexOf("`department`")!=-1){
allData.put(6, line);
}else if(lowerLine.indexOf("`cmsuser`")!=-1){
allData.put(7, line);
}else if(lowerLine.indexOf("`account`")!=-1){
allData.put(8, line);
}else if(lowerLine.indexOf("`accountrole`")!=-1){
allData.put(9, line);
}else if(lowerLine.indexOf("`flowdefine`")!=-1){
allData.put(10, line);
}else if(lowerLine.indexOf("`flowtask`")!=-1){
allData.put(11, line);
}else if(lowerLine.indexOf("`rolefucperm`")!=-1){
allData.put(12, line);
}else if(lowerLine.indexOf("`templategroup`")!=-1){
allData.put(13, line);
}else if(lowerLine.indexOf("`templatetype`")!=-1){
allData.put(14, line);
}else if(lowerLine.indexOf("`template`")!=-1){
allData.put(15, line);
}else if(lowerLine.indexOf("`contenttype`")!=-1){
allData.put(16, line);
}else{
allData.put(num++, line);
}
}else{
bw_create.append(line+"\r\n");
}
}
for (int i = 1; i < num; i++) {
if(allData.containsKey(i)){
bw_insert.append(full_to_part(allData.get(i)));
}
}
bw_create.flush();
bw_insert.flush();
br.close();
bw_create.close();
bw_insert.close();
}
// private static void setSequence(){
//
// }
/**
* 將一行批量插入的數據轉換為多行插入
* @param line
* @return
*/
private static String full_to_part(String line){
StringBuffer sb=new StringBuffer();
String lowerLine=line.toLowerCase();
int firstDan=lowerLine.indexOf("`");
int firstQuot=lowerLine.indexOf("(");
String tableName=lowerLine.substring(firstDan, firstQuot);
System.out.println("--------------------------開始轉換插入----"+tableName+"---的數據----------");
int values_position=lowerLine.indexOf("values")+7;
String forward_line=line.substring(0, values_position);
String datas_line=line.substring(values_position,line.length()-1); //得到後面插入的數據
String[] datas=datas_line.split("\\)\\,\\(");//根據),(分割為一個字符串數組
for (int i = 0; i < datas.length; i++) {
String data=null;
if(datas.length==1){ //如果只有一條數據，不會被分割的，數組就會只有一條數據
data=datas[i];
}else{
if(i==0) //如果是第一條，那麼後面需要追加一個括號
data=datas[i]+")";
else if(i==datas.length-1) //如果是最後一條，需要在前面加一個括號
data="("+datas[i];
else //如果是中間的數據，前後都需要加括號
data="("+datas[i]+")";
}
sb.append(forward_line); //將insert 字段名和values先行加入
sb.append(data+";");
sb.append("\r\n");
}
sb.append("\r\n");
return sb.toString();
}
}
這是通用方法，以後需要得到MYSQL的兩個初始化腳本就通過這個方法已轉換就可以。
二、Oracle建表的問題沒有通過這個解決，可以通過Hibernate逆向生成。建表和表結構，表關系，都有了。
下面就是插入數據了：
關於MYSQL的insert腳本基本上和Oracle的一致，但是也有不一致，對於日期，對於一些符號’都是問題。將MYSQL的insert語句轉換成ORACLE的insert語句。
1、需要將’去掉，這個符號是在插入數據時的表名上括起來的，所以，將表名上的’都替換掉。
2、需要將date日期，經過函數to_date(date,”yyyy-MM-dd”)轉換一下，所以我通過正則表達式來查找到所有的日期，還有一種日期，攜帶時分秒的日期，需要另一個正則，最後當然都是替換成為年月日就可以了。
package com.sql.convert;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class FromMYSQLInsertToOracleInsert {
/**
* 需要轉換一下地方：
* 將所有的表名以及列名中的'去掉
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
String mysql_file="C:/Documents and Settings/gaofei/桌面/insertData.sql";
String oracle_file="C:/Documents and Settings/gaofei/桌面/oracle_insertData.sql";
turnSQL(mysql_file, oracle_file);
}
private static void turnSQL(String mysql_file,String oracle_file) throws IOException{
File mysqlFile=new File(mysql_file);
File oracleFile=new File(oracle_file);
if(!oracleFile.exists())
oracleFile.createNewFile();
InputStreamReader isr=new InputStreamReader(new FileInputStream(mysqlFile), "UTF-8");
OutputStreamWriter osw=new OutputStreamWriter(new FileOutputStream(oracleFile), "UTF-8");
BufferedReader br=new BufferedReader(isr);
BufferedWriter bw=new BufferedWriter(osw);
String line=null;
while((line=br.readLine())!=null){
bw.append(convertString(line));
bw.append("\r\n");
}
bw.flush();
br.close();
bw.close();
isr.close();
osw.close();
}
private static String convertString(String line){
line=line.replace("`", "");
Pattern p=Pattern.compile("'\\d{4}\\-\\d+\\-\\d+'");
Matcher m=p.matcher(line);
String date=null;
while(m.find()){
date=m.group(0);
line=line.replace(date, "to_date("+date+",'yyyy-MM-dd')");
}
p=Pattern.compile("'\\d{4}\\-\\d+\\-\\d+\\s\\d+\\:\\d+\\:\\d+'");
m=p.matcher(line);
date=null;
while(m.find()){
date=m.group(0);
String newDate=date.substring(0,date.indexOf(" "));
line=line.replace(date, "to_date("+newDate+"','yyyy-MM-dd')");
}
return line;
}
}
三、看似沒有問題了，直接執行腳本就沒問題了，但是問題又來了：
我是直接用控制開的sqlplus來訪問Oracle的，沒有工具，每次執行腳本，是通過@腳本名.sql來執行的，但是請注意如果是路徑太長，比如@”C:/Documents and Settings/gaofei/桌面/oracle_insertData.sql”路徑中間有空格需要加上””的，
執行報錯。很多錯：
1、關於數據中的 等這類的特殊，oracle會認為是plsql編程中自定義的變量，當然會出問題了，所以需要設置set define off，關掉自定義變量。
2、一個問題很少會有人用到，就是關於Oracle的插入數據，一個字段最多插入2999個字符。太多將會插入不進的。而我的數據都是模板或新聞，很多多是上萬，所以問題來了。沒有想到辦法。最後我是將很多的數據刪除的很短，插入成功後，通過程序一條條插入的。
遷移完成了 ，下面就是關於Oracle的備份問題了。www.2cto.com
說是可以准備SQL腳本，但是腳本到時候還會是INSERT語句，到時候又不能插入了。
所以是通過ORACLE的備份命令，備份出DMP文件。
剛開始是用SYSTEM用戶直接備份的，但是會有很多的系統表，這是不符合要求的。於是建立一個用戶future ，用future用戶逆向生成表結構、表關系。然後通過賦予futureDBA權限，通過insert into sequenceblock select * from system.sequenceblock;將所有表中有數據的表都從system導入到future用戶中。
這樣表就再future用戶的指定表空間中建立出來了。
通過future直接備份數據。有兩種備份方式。
$exp future/ylzxdb1219@ylzxdb file=D:/future_final.dmp full=y;
以future身份導出了所有庫的信息
$exp future/ylzxdb1219@ylzxdb file=D:/future_user.dmp owner=future;
以future的身份導出了future用戶中所有的數據。
但是future有DBA權限，所以會再備份時將系統表備份。所以推薦第二種方式。導出的只是當前用戶future的所有表的備份。
導入數據：
$imp future/ylzxdb1219@ylzxdb fromuser=future touser=future ignore=y file=D:/future_user.dmp;
（注：例句是將數據庫的備份文件放在了D:盤下的future.user.dmp文件）
本文出自 “另類的自由” 博客
