Why do you want to migrate?
First from the operating cost considerations, with MySQL can save a lot of money. On the other hand, MySQL stability and function constantly improve and enhance, basically can meet customer needs, such as support multi-node deployment, data partitioning and so on. And MySQL is easy to use, easier than Oracle. So the customer requires the existing Oracle data table and content to migrate to MySQL.
Why do you write your own script?
The tables and data migrated are quite numerous, with hundreds of tables. So it's not convenient to do it manually. Also tried some free migration tools, such as: Mysqlmigrationtool, and so on, found that the transfer of the field type is not very satisfied with the requirements (may be the original Oracle table design is not very good), will result in incomplete data, feel not too reliable, so decided to write their own migration script relieved some, There are not meet the requirements can be adjusted immediately, so start it.
The technical support that is used
1.php5
2.php Oci8
3.mysql 5.1
Migrate table structure
Get all tables of schema
Use the following statement to get all the table names in the schema from Oracle
SELECT table_name FROM user_tables
You can then traverse all tables to create a table structure and migrate data to MySQL.
Get all the fields and types of a single table
Use the following statement to get all the fields and types of a single table from Oracle
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT
FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('{$table_name}') ORDER BY column_id ASC
This lets you know the name, type, length of the table field, whether it is allowed to be empty, and the default value. Because Oracle is not fully compatible with MySQL's field type, it is necessary to establish a corresponding relational table for the field type.
Oracle |
Mysql |
Number (<11) > |
Int |
Number (>11) |
bigint |
varchar |
varchar |
VARCHAR2 (<255) > |
varchar |
VARCHAR2 (>255) |
Text |
Clob |
Text |
Date |
Date |