Problems and workarounds for Oracle data transfer MySQL

A few days ago because of work need I have to the Oracle database dozens of tables transferred to MySQL, because there is no relevant experience, the Internet simple Baidu after choosing Navcat Premium software work.

Navcat Premium for data transfer There are three ways: 1, copy table 2, export, import data 3, export to SQL script, execute.

To start with the method of copying the table, open the menu bar "tools" data transfer function, select the table to be copied to the left, select the connection to the right and then select the target database, followed by next to next one-click operation. This method is really convenient, but there are many bugs, the biggest bug is the numeric type mismatch. The value type in Oracle is Number2, and copying to MySQL defaults to double type, and I need int type. Using this method fails.

Then try to export the Oracle table data into a file and then import MySQL, because the import and export operations are somewhat complex, and can not be bulk operation, you need to create a table in MySQL, and then import the data, so give up.

Finally, it attempts to dump the data and the table structure into a SQL script file, which is executed in MySQL. Open the data transfer function of the menu bar "Tools", select the table to be copied to the left, and select the file on the right. There was a problem executing the SQL file in MySQL, the SQL script file generated by the Oracle dump has a numeric type of decimal (XX,XX), the date type is datetime (x), and there is no decimal numeric type in MySQL. The date type only supports datetime and does not have an explicit size limit, so it is decided to modify it with the sublime editor. Use the sublime Lookup feature find all to find all DateTime (XX,XX), unify to int, and then find an error after execution. The original field type is decimal (xx,xx) and the field type is decimal (x,x), Deciaml (xx,x) ..., and the other characters are deleted by mistake when unified. Then think of regular expression matching, just sublime support regular lookup, using regular lookup function to match all decimal (xx,xx), Decimal (xx,x), decimal (X,XX) ... Unification to the type int, and then the same way to modify the datetime type, and then execute the SQL script, which finally successfully executed, the data is also correct, done!

Finally, the limitation of this method: if there is such a requirement, the MySQL part of the field value type needs to be double, part of the need to be an int, how is this good? The only time you can think of is to create a table in MySQL and import the data one after another.

Problems and workarounds for Oracle data transfer MySQL