Migrate data from MySQL to Oracle

MYSQL has a data type that increases automatically. When you insert a record, you do not need to operate on this field. The data value is automatically obtained. ORACLE does not have a data type for automatic growth, so an automatic growth is required.

MYSQL has a data type that increases automatically. When you insert a record, you do not need to operate on this field. The data value is automatically obtained. ORACLE does not have a data type for automatic growth, so an automatic growth is required.

1. First, find some information on the website as follows:

1. Automatically increasing data type Processing

MYSQL has a data type that increases automatically. When you insert a record, you do not need to operate on this field. The data value is automatically obtained. Oracle does not have an auto-increasing data type. You need to create an auto-increasing serial number. When inserting a record, you need to assign the next value of the serial number to this field.

Create sequence serial number name (preferably table name + Serial number mark) increment by 1 start with 1 MAXVALUE 99999 cycle nocache;

INSERT statement INSERT this field value: name of the serial number. NEXTVAL

2. Single quotes

MYSQL can use double quotes to enclose strings. ORACLE can only use single quotes to enclose strings. You must replace single quotes before inserting and modifying strings: replace all the existing single quotes with two single quotes. Of course, if you use the Convert Mysql to Oracle tool, you don't have to consider this issue.

3. Processing of long strings

In ORACLE, the maximum length of a string that can be operated during INSERT and UPDATE is less than or equal to 4000 single bytes. To INSERT a longer string, use the CLOB type for the field, use the DBMS_LOB package that comes with ORACLE. Before inserting a modification record, you must make a non-null and length judgment. If the field value cannot be blank or the field value beyond the length is exceeded, a warning should be given, and the last operation is returned.

4. Processing of paging SQL statements

The SQL statement that MYSQL uses to process pages is relatively simple. It uses LIMIT to start the position and record the number of records. It is complicated for ORACLE to process paging SQL statements. Each result set has only one ROWNUM field to indicate its position, and only ROWNUM <100, not ROWNUM> 80 can be used.

The following two SQL statements (ID is the field name of the unique keyword) are better after analysis ):

Statement 1: select id, [FIELD_NAME,...] FROM TABLE_NAME where id in (select id from (select rownum as numrow, id from TABLE_NAME WHERE Condition 1 order by condition 2) where numrow> 80 and numrow <100) order by Condition 3;

Statement 2: SELECT * FROM (select rownum as numrow, c. * from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE Condition 1 order by condition 2) c) where numrow> 80 and numrow <100) order by Condition 3;

5. Processing date fields

Mysql date fields are divided into DATE and TIME. The oracle date field is only DATE, which contains information about the year, month, day, hour, minute, and second. the system TIME of the current database is SYSDATE, accurate to seconds.

The mathematical formulas for date fields vary greatly. MYSQL uses DATE_FIELD_NAME> SUBDATE (NOW (), INTERVAL 7 DAY) to locate the current time seven days from the current time and uses DATE_FIELD_NAME> SYSDATE-7;

6. Fuzzy comparison of strings

MYSQL uses the field name like '% string %', and ORACLE can also use the field name like '% string %'. However, this method cannot use indexes and is not fast, if you use a string to compare the instr function (field name, 'string') to 0, you will get more accurate search results.

7. Handling of null characters

Non-empty fields in MYSQL are also empty. empty fields are not allowed in ORACLE. The ORACLE table structure is defined based on the not null value of MYSQL. errors may occur when data is imported. Therefore, when importing data, you need to judge the NULL character. If it is NULL or NULL, you need to change it to a space string.

I will take the test as an example.

Ii. Use of tools

Many friends on the Internet use Convert Mysql to Oracle, and we use tools to solve problems solved by tools, it depends on whether the tool can solve the problem. There will be many problems through the tool, and you still have to write your own program to solve it. Later, it was found that the tool could still export data. The creation and modification of data tables could only be solved by writing a program. However, there are also problems with data import, as shown below:

Problems encountered when reading data

1. When text is sent to blob, this is a huge influence. If we don't want to see it, don't explain it more clearly.

2. If the character size in Mysql is Varchar or char is 2, it means that it can be written into two long data records, such as "12, China, and 1, oracle only allows two or more Chinese characters, therefore, you must pay attention to the size of the bit when storing data.

3. the character set must be set to positive in the Process of encoding. Otherwise, the expected number of characters may appear.

4. indexes cannot be imported. Note whether the table has an index and whether a NULL value is allowed.

5. The id auto-increment table in Mysql needs to be processed, and the sequence and trigger related are configured in oracle.

6. comment is a relational character in oracle and cannot be processed as a column.

7. Special Handling when the number of data items is large.

3. Write your own program to solve the problem

// Obtain the names of all tables

SELECT
'Tables '. 'table _ scheme', 'tables'. 'table _ name'
FROM
'Information _ scheme'. 'tables'
WHERE
'Tables '. 'table _ type' = 'base table'
And 'tables '. 'table _ scheme' = 'netoffice ';

// Obtain information about all columns in a table

SELECT * FROM
'Information _ scheme'. 'columns'

Where 'table _ scheme' = 'netoffice'

And 'table _ name' = 'drmcerication ication 'order by 'ordinal _ position ';

// Java program:

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 ";

// You only need to modify the host name, data volume name, user, and password.
Public static final String DBdriver = "com. mysql. jdbc. Driver ";
Public static final String DBURL = "jdbc: mysql: // host address: 3306/Data preview name? User = roots & password = 1234 ";
Public static final String DBSCHEMA = "data register name ";//

Public static void main (String [] args ){
New TestMysql ();
}

Public TestMysql (){

// Delete objects
DeleteFile ();

If (! ConnectionMethod ()){
System. out. println ("zookeeper connected ");
Return;
}

Vector Table = queryAllTable (DBSCHEMA );
If (table. size () = 0 ){
System. out. println ("no table found ");
Return;
}

For (int I = 0; I <table. size (); I ++) {// obtain the number of rows
Hashtable. put (table. get (I), handle_table (table. get (I )));
}

// Hashtable. put (table. get (0). toString (), handle_table (table. get (0 )));
System. out. println ("the operation is in progress. Please wait ......");
GeneratorString (hashtable); // generate a string

Close (); // closes the connection
System. out. println ("finish ");
}

Public void generatorString (Hashtable hashtable ){
Iterator iter = hashtable. keySet (). iterator ();
While (iter. hasNext ()){
String tablescript = ""; // lifecycle table sentence
String tablesequence = ""; // create a sequence
String tabletrigger = ""; // create a trigger
String tableprimarykey = ""; // create a master secret
String tableindex = ""; // create an index
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 (); // column name
String is_nullable = val. get (1). toString (); // whether it is null. If NO, YES is allowed.
String data_type = val. get (2). toString (); // int, varchar, text, timestamp, date
String character_maximun_length = val. get (3). toString (); // Length
String column_key = val. get (4). toString (); // indicates whether the primary partition is PRI.
// MUL (index)
// There are two PRI statements indicating that the index is merged
String extra = val. get (5). toString (); // whether the auto-increment column is
// Auto_increment
String column_default = val. get (6). toString (); // whether the specified value exists

If (data_type.equals ("varchar") | data_type.equals ("char") {// whether the certificate contains Chinese Characters
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) {// whether the specified value exists
If (column_default.equals ("CURRENT_TIMESTAMP ")){
Tablescript = tablescript + "default sysdate" + "";
} Else {
Tablescript = tablescript + "default" + column_default
+ "";
}
}

If (is_nullable.equals ("NO") {// whether it is null
Tablescript = tablescript + "not null ,";
} Else {
Tablescript = tablescript + ",";
}

If (extra. equals ("auto_increment") {// auto-increment Column
Int maxid = get_maxId (tablename, column_name );
Tablesequence = "create sequence sq _" + tablename + ""
+ "Minvalue" + maxid + ""
+ "Max value 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 _ scheme'. 'columns '"
+ "Where 'table _ scheme' = '" + DBSCHEMA + "'"
+ "And 'table _ name' = '" + tablename + "'"
+ "Order by 'ordinal _ position '";
// System. out. println ("SQL =" + queryDetail );
ResultSet rst = statement.exe cuteQuery (queryDetail );

While (rst. next ()){
Vector Vec = new Vector ();
String column_name = NulltoSpace (rst. getString ("COLUMN_NAME"); // column name
String is_nullable = NulltoSpace (rst. getString ("IS_NULLABLE"); // whether it is null. If NO, YES is allowed.
String data_type = NulltoSpace (rst. getString ("DATA_TYPE"); // int, varchar, text, timestamp, date
String character_maximun_length = NulltoSpace (rst
. GetString ("CHARACTER_MAXIMUM_LENGTH"); // the length.
String column_key = NulltoSpace (rst. getString ("COLUMN_KEY"); // whether the primary shard
// Yes, PRI
// MUL (index)
// There are two PRI statements indicating that the index is merged
String extra = NulltoSpace (rst. getString ("EXTRA"); // whether to auto-increment Columns
// Yes
// Auto_increment
String column_default = NulltoSpace (rst
. GetString ("COLUMN_DEFAULT"); // whether the specified value exists
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.exe cuteQuery (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.exe cuteQuery (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 _ scheme'. 'tables '"
+ "WHERE 'tables '. 'table _ type' = 'base table '"
+ "And 'tables '. 'table _ scheme' ='" + table_schema + "'";
// System. out. println ("SQL =" + queryTable );
ResultSet rst = statement.exe cuteQuery (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 ();
}
}

}