mysql的index转oracle_MySQL转数据到Oracle

一、首先從網絡上找到一些資料如下：

1． 自动增长的数据类型处理

MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。Oracle没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。

CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;

INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL

2. 单引号的处理

MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。当然你如果使用 Convert Mysql to Oracle 工具就不用考虑这个问题

3．长字符串的处理

在ORACLE中，INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告,返回上次操作。

4. 翻页的SQL语句的处理

MYSQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数。ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用ROWNUM<100, 不能用ROWNUM>80。

以下是经过分析后较好的两种ORACLE翻页SQL语句( ID是唯一关键字的字段名 )：

语句一：SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3;

语句二：SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY 条件3;

5. 日期字段的处理

MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE, 精确到秒。

日期字段的数学运算公式有很大的不同。MYSQL找到离当前时间7天用 DATE_FIELD_NAME > SUBDATE(NOW()，INTERVAL 7 DAY)ORACLE找到离当前时间7天用 DATE_FIELD_NAME >SYSDATE - 7;

6. 字符串的模糊比较

MYSQL里用 字段名 like '%字符串%',ORACLE里也可以用 字段名 like '%字符串%' 但这种方法不能使用索引, 速度不快，用字符串比较函数 instr(字段名,'字符串')>0 会得到更精确的查找结果。

7. 空字符的处理

MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

以上內容我作為參考。

二．工具的使用

網上好多朋友介紹使用Convert Mysql to Oracle這個工具，當然能用工具解決的問題我們就用工具，關鍵是看工具能不能解決問題。通过工具会出现好多问题，最终还是要自己写程式解决。后来发现工具导数据还是可以的，数据表的创建和修改只有自己写程式解决了。但是导数据也有问题,如下:

導入數據遇到的問題

1、text到blob的時候，這個是影響很大的，不是我們希望看到的，就不要做多說明。

2、在Mysql中如果是Varchar或char中字符大小為2，意味著它可以輸入“12、中國、1中”等2個長度的數據，而在Oracle中是針對字節的，它只允許輸入英文字符2個或一個中文漢字，所以這變在導數據的時候要注意欄位的大小。

3、導入的過程中字符集必須要設置正確，否則會出現亂碼的數據。

4、index是不可以導進來的，要注意table是否有Index；是否允許NULL值也要注意。

5、Mysql中id自動增長的table要做處理，在oracle中設置相關的sequence和trigger。

6、comment在oracle中是關鍵字，不能當做列來處理。

7、當數據量大的時候做特別處理。

三．自己写程式解决问题

//获得所有table的名字

SELECT

`TABLES`.`TABLE_SCHEMA`, `TABLES`.`TABLE_NAME`

FROM

`information_schema`.`TABLES`

WHERE

`TABLES`.`TABLE_TYPE` = 'base table'

and `TABLES`.`TABLE_SCHEMA`  ='netoffice';

//获得某table所有列的信息

SELECT * FROM

`information_schema`.`COLUMNS`

where `TABLE_SCHEMA`='netoffice'

and `TABLE_NAME`='drmcertification' order by `ORDINAL_POSITION`;

//java程式:

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.File;

import java.io.FileReader;

import java.io.FileWriter;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Hashtable;

import java.util.Iterator;

import java.util.Vector;

public class TestMysql {

public static Connection conn;

public static Statement statement;

public Hashtable>> hashtable = new Hashtable>>();

public static final String filepathCreateTable = "D://CreateTable.txt";

public static final String filepathCreateSequence = "D://CreateSequence.txt";

public static final String filepathCreateTrigger = "D://CreateTrigger.txt";

public static final String filepathCreatePrimarykey = "D://CreatePrimarykey.txt";

public static final String filepathCreateIndex = "D://CreateIndex.txt";

//只要修改主機名，數據庫名字和user、password

public static final String DBdriver = "com.mysql.jdbc.Driver";

public static final String DBURL = "jdbc:mysql://主機地址:3306/數據庫名字?user=roots&password=1234";

public static final String DBSCHEMA = "數據庫名字"; //

public static void main(String[] args) {

new TestMysql();

}

public TestMysql() {

//刪除文件

deleteFile();

if (!connectionMethod()) {

System.out.println("鏈接錯誤");

return;

}

Vector table = queryAllTable(DBSCHEMA);

if (table.size() == 0) {

System.out.println("沒有找到表");

return;

}

for (int i = 0; i < table.size(); i++) { //得到數據

hashtable.put(table.get(i), handle_table(table.get(i)));

}

// hashtable.put(table.get(0).toString(),handle_table(table.get(0)));

System.out.println("操作正在進行中,請耐心等待......");

generatorString(hashtable); //產生字符串

close();//關閉連接

System.out.println("finish");

}

public void generatorString(Hashtable hashtable) {

Iterator iter = hashtable.keySet().iterator();

while (iter.hasNext()) {

String tablescript = ""; // 創表語句

String tablesequence = ""; // 建立sequence

String tabletrigger = ""; // 建立trigger

String tableprimarykey = "";// 建立主鍵

String tableindex = "";// 建立索引

String primarkeyColumn = "";

String indexColumn = "";

int primarykey = 0;

int index = 0;

String tablename = (String) iter.next();

Vector valall = (Vector) hashtable.get(tablename);

tablescript = "create table " + tablename + "(";

for (int i = 0; i < valall.size(); i++) {

Vector val = (Vector) valall.get(i);

String column_name = val.get(0).toString();// 列名

String is_nullable = val.get(1).toString();// 是否為空，如果不允許NO,允許為YES

String data_type = val.get(2).toString();// int,varchar,text,timestamp,date

String character_maximun_length = val.get(3).toString();// 長度大小

String column_key = val.get(4).toString();// 是否主鍵 是的話為PRI

// MUL(index)

// 有兩個PRI說明是複合index

String extra = val.get(5).toString(); // 是否自動增長列 是的話

// auto_increment

String column_default = val.get(6).toString();// 是否有默認值

if (data_type.equals("varchar") || data_type.equals("char")) { // 驗證是否有中文字符

if (judge_china(tablename, column_name)) {

character_maximun_length = Integer

.parseInt(character_maximun_length)

* 3 + "";

}

}

tablescript = tablescript + column_name + " ";

if (data_type.equals("int")) {

tablescript = tablescript + "NUMBER" + " ";

} else if (data_type.equals("mediumint")) {

tablescript = tablescript + "NUMBER" + " ";

} else if (data_type.equals("char")) {

tablescript = tablescript + "varchar2("

+ character_maximun_length + ")" + " ";

} else if (data_type.equals("varchar")) {

tablescript = tablescript + "varchar2("

+ character_maximun_length + ")" + " ";

} else if (data_type.equals("text")) {

tablescript = tablescript + "varchar2(4000) ";

} else if (data_type.equals("timestamp")) {

tablescript = tablescript + "date" + " ";

} else if (data_type.equals("date")) {

tablescript = tablescript + "date" + " ";

} else if (data_type.equals("float")) {

tablescript = tablescript + "NUMBER" + " ";

} else if (data_type.equals("longtext")) {

tablescript = tablescript + "varchar2(4000) ";

} else if (data_type.equals("smallint")) {

tablescript = tablescript + "NUMBER" + " ";

} else if (data_type.equals("double")) {

tablescript = tablescript + "NUMBER" + " ";

} else if (data_type.equals("datetime")) {

tablescript = tablescript + "date" + " ";

}

if (column_default.length() > 0) { // 是否有默認值

if (column_default.equals("CURRENT_TIMESTAMP")) {

tablescript = tablescript + "default sysdate" + " ";

} else {

tablescript = tablescript + "default " + column_default

+ " ";

}

}

if (is_nullable.equals("NO")) { // 是否為空值

tablescript = tablescript + "not null,";

} else {

tablescript = tablescript + ",";

}

if (extra.equals("auto_increment")) { // 是否自動增長列

int maxid = get_maxId(tablename, column_name);

tablesequence = "create sequence sq_" + tablename + " "

+ "minvalue " + maxid + " "

+ "maxvalue 9999999999999999 " + "increment by 1 "

+ "start with " + maxid + " " + "cache 20;";

tabletrigger = "EXECUTE IMMEDIATE  'create trigger tr_"

+ tablename + " " + "before " + "insert on "

+ tablename + " for each row " + "begin "

+ "select sq_" + tablename + ".nextval into:new."

+ column_name + " from dual; " + "end;';";

}

if (column_key.length() > 0) {

if (column_key.equals("PRI")) {

primarykey++;

primarkeyColumn = primarkeyColumn + column_name + ",";

} else if (column_key.equals("MUL")) {

index++;

indexColumn = indexColumn + column_name + ",";

}

}

}

if (primarykey == 1) {

primarkeyColumn = primarkeyColumn.substring(0, primarkeyColumn

.length() - 1);

String key = "pr_" + tablename + "_" + primarkeyColumn;

if (key.length() > 30) {

key = "pr_" + primarkeyColumn;

}

tableprimarykey = "alter table " + tablename

+ "  add constraint " + key + " primary key ("

+ primarkeyColumn + ");";

} else {

primarkeyColumn = primarkeyColumn.substring(0, primarkeyColumn

.length() - 1);

String indextemp = tablename + "_index";

if (indextemp.length() > 30)

indextemp = primarkeyColumn.replace(',', '_') + "_index";

tableindex = "create index " + indextemp + " on " + tablename

+ " (" + primarkeyColumn + ");";

}

if (index > 0) {

indexColumn = indexColumn

.substring(0, indexColumn.length() - 1);

String indextemp = tablename + "_index";

if (indextemp.length() > 30)

indextemp = indexColumn.replace(',', '_') + "_index";

tableindex = "create index " + indextemp + " on " + tablename

+ " (" + indexColumn + ");";

}

tablescript = tablescript.substring(0, tablescript.length() - 1);

tablescript = tablescript + ");";

if (tablescript.length() > 0)

write(filepathCreateTable, tablescript);

if (tablesequence.length() > 0)

write(filepathCreateSequence, tablesequence);

if (tabletrigger.length() > 0)

write(filepathCreateTrigger, tabletrigger);

if (tableprimarykey.length() > 0)

write(filepathCreatePrimarykey, tableprimarykey);

if (tableindex.length() > 0)

write(filepathCreateIndex, tableindex);

}

}

public void close() {

try {

statement.close();

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public Vector> handle_table(String tablename) {

Vector> arg = new Vector>();

try {

String queryDetail = "SELECT * "

+ "FROM `information_schema`.`COLUMNS` "

+ "where `TABLE_SCHEMA`='" + DBSCHEMA + "' "

+ "and `TABLE_NAME`='" + tablename + "' "

+ "order by `ORDINAL_POSITION`";

// System.out.println("sql= "+queryDetail);

ResultSet rst = statement.executeQuery(queryDetail);

while (rst.next()) {

Vector vec = new Vector();

String column_name = NulltoSpace(rst.getString("COLUMN_NAME"));// 列名

String is_nullable = NulltoSpace(rst.getString("IS_NULLABLE"));// 是否為空，如果不允許NO,允許為YES

String data_type = NulltoSpace(rst.getString("DATA_TYPE"));// int,varchar,text,timestamp,date

String character_maximun_length = NulltoSpace(rst

.getString("CHARACTER_MAXIMUM_LENGTH"));// 長度大小

String column_key = NulltoSpace(rst.getString("COLUMN_KEY"));// 是否主鍵

// 是的話為PRI

// MUL(index)

// 有兩個PRI說明是複合index

String extra = NulltoSpace(rst.getString("EXTRA")); // 是否自動增長列

// 是的話

// auto_increment

String column_default = NulltoSpace(rst

.getString("COLUMN_DEFAULT"));// 是否有默認值

vec.add(column_name);

vec.add(is_nullable);

vec.add(data_type);

vec.add(character_maximun_length);

vec.add(column_key);

vec.add(extra);

vec.add(column_default);

arg.add(vec);

}

rst.close();

} catch (SQLException e) {

e.printStackTrace();

}

return arg;

}

public boolean judge_china(String tablename, String columnname) {

try {

String querysql = "select count(1) row from " + tablename

+ " where length(" + columnname + ")!=char_length("

+ columnname + ")";

// System.out.println("sql= "+querysql);

ResultSet rst = statement.executeQuery(querysql);

if (rst.next()) {

if (NulltoSpace(rst.getString("row")).equals("0")) {

return false;

} else {

return true;

}

}

rst.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

}

return true;

}

public int get_maxId(String tablename, String columnname) {

String maxValue = "0";

try {

String querysql = "select max(" + columnname + ") maxid from "

+ tablename;

// System.out.println("sql= "+querysql);

ResultSet rst = statement.executeQuery(querysql);

if (rst.next()) {

maxValue = NulltoSpace(rst.getString("maxid"));

}

rst.close();

} catch (SQLException e) {

}

return Integer.parseInt(maxValue + 1);

}

public Vector queryAllTable(String table_schema) {

Vector tableName = new Vector();

try {

String queryTable = "SELECT `TABLES`.`TABLE_NAME` "

+ "FROM `information_schema`.`TABLES` "

+ "WHERE `TABLES`.`TABLE_TYPE` = 'base table' "

+ "and `TABLES`.`TABLE_SCHEMA`  ='" + table_schema + "'";

// System.out.println("sql= "+queryTable);

ResultSet rst = statement.executeQuery(queryTable);

while (rst.next()) {

tableName.add(NulltoSpace(rst.getString("TABLE_NAME")));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

}

return tableName;

}

public boolean connectionMethod() {

try {

Class.forName(DBdriver).newInstance();

conn = DriverManager.getConnection(DBURL);

statement = conn.createStatement();

return true;

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

return false;

}

}

public static String NulltoSpace(Object o) {

if (o == null)

return "";

else if (o.equals("null")) {

return "";

} else {

return o.toString().trim();

}

}

public static void deleteFile(){

File f;

f= new File(filepathCreateTable);

if(f.exists()) f.delete();

f= new File(filepathCreatePrimarykey);

if(f.exists()) f.delete();

f= new File(filepathCreateSequence);

if(f.exists()) f.delete();

f= new File(filepathCreateTrigger);

if(f.exists()) f.delete();

f= new File(filepathCreateIndex);

if(f.exists()) f.delete();

}

public static void write(String path, String content) {

String s = new String();

String s1 = new String();

try {

File f = new File(path);

if (f.exists()) {

} else {

f.createNewFile();

}

BufferedReader input = new BufferedReader(new FileReader(f));

while ((s = input.readLine()) != null) {

s1 += s + "\r\n";

}

input.close();

s1 += content;

BufferedWriter output = new BufferedWriter(new FileWriter(f));

output.write(s1);

output.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}