Considerations for migrating data from MySQL to Oracle

Considerations for migrating data from MySQL to Oracle


1. Automatic growth of data type processing
MySQL has an auto-growing data type that does not manipulate this field when inserting records and automatically obtains data values. Oracle does not have auto-growth
Data type, you need to create an auto-growing serial number, and when inserting a record, assign the next value of the sequence number to this field.
The name of the CREATE SEQUENCE serial number (preferably table name + serial number tag)
INCREMENT by 1 START with 1 MAXVALUE 99999 CYCLE NOCACHE;
Insert statement inserts this field value as: The name of the serial number. Nextval


2. Handling of single quotes
In MySQL, you can use double quotes to wrap strings, and Oracle can only wrap strings with single quotes. Must be done before inserting and modifying a string
Single quote substitution: Replaces all occurrences of a single quotation mark with two single quotes. Of course if you use Convert Mysql to Oracle
Tools don't have to think about that.


3. Processing of long strings
In Oracle, the maximum operational string length of insert and update is less than or equal to 4,000 single bytes, if you want to insert a longer character
String, consider the field using the Clob type, method to borrow the Dbms_lob package that comes with Oracle. Be sure to do this before inserting a change record
Null and length judgments, cannot be null for field values and out of length field values should be warned to return the last action.


4. Handling of page-turn SQL statements
MySQL processing the page of the SQL statement is relatively simple, with limit start position, record number. Oracle handles paging with SQL statements that are more cumbersome
The Each result set has only one rownum field indicating its location and can only be used with rownum<100, not rownum>80.


The following are two of the better-parsed Oracle page-flipping SQL statements (the field name with the unique keyword ID):
Statement one:
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 two:
SELECT *
From ((SELECT ROWNUM as Numrow, c.*
From (select Field_name,...
From table_name
WHERE Condition 1
ORDER by Condition 2) c) WHERE Numrow > Numrow < 100)
ORDER by Condition 3;


5. Processing of date fields
The MySQL date field is dated and time two, the Oracle Date field is only date, contains the date of the month and the minute, and the current database
The system time is sysdate, accurate to the second.
The mathematical formula for a date field differs greatly.
MySQL found 7 days from current time by: Date_field_name > Subdate (Now (), INTERVAL 7 day)
Oracle found 7 days from current time by: Date_field_name >SYSDATE-7;


6. Fuzzy Comparison of strings
MySQL uses the field name like '% string% ', Oracle can also use the field name as '% string% ' but this method cannot be used
Index, the speed is not fast, with the string comparison function InStr (field name, ' string ') >0 will get more accurate search results.


7. Handling of NULL characters
MySQL's non-empty fields also have empty content, and Oracle defines non-empty fields that are not allowed to have empty content. Set by not NULL for MySQL
Oracle table structure, which generates errors when conducting data. Therefore, null characters are judged when the data is being directed, and if NULL or null characters are
A string that needs to be changed to a space.

Oracle porting to MySQL considerations
The database used by the customer is MySQL, and the developed product supports Oracle, and we have to migrate the database environment from Oracle to MySQL. In
The migration process encountered the following problems, if we in the initial design, coding process to pay attention to the portability of the database, this situation
There is no need for extra work at all.


First, the database environment from Oracle porting to MySQL encountered problems.
1, Case sensitive differences (if the server OS is Linux).
In Oracle, it is generally not case-sensitive. Sometimes we are using Oracle not to pay attention to case, table name and field name do not add
Double quotes are case-insensitive, like this: INSERT into tableName and insert into tableName effects are the same,
Use the tool to export the Create/data initialization script, resulting in the general table name and field name converted to uppercase.


In MySQL, however, the case sensitivity of the operating system used determines the case sensitivity of the database name and table name. Database corresponding data
A directory in a directory in which each table in the database corresponds to at least one file in the database directory (or possibly multiple, depending on the storage engine).
Therefore, using a database or table is actually manipulating these files (folders), so using the case sensitivity of the operating system determines the database name and
The case sensitivity of the table name. is case-sensitive in Linux-kernel-based operating systems.


The solution is to keep the MySQL database name consistent with the case of Oracle, and the table name and the table name in the SQL string in the application remain
Consistent, if the field names in your application use double quotes, match the case of the field names in SQL with the characters in double quotes. If
The table names and fields referenced by your application are not uniformly case-sensitive, and that's a lot of trouble.


2, the difference between reserved words.
Function names such as the SQL language (such as: inteval,show) are reserved words. Reserved words in Oracle are available as table names and field names, and do not
The effect is used, but the reserved word in MySQL is not available as the table name and field name, if used to report a syntax error.


Workaround, the reserved word in the SQL statement with the "'" symbol, which is located on the Keyboard tab key, if the field name and another
The Outside method tablename. Field name. Like this:
INSERT INTO TableName (ID, ' interval ') value (...)
Or
INSERT INTO TableName (ID, tablename.inteval) value (...).


3, the difference between the type of automatic growth.
Oracle has Sequence,mysql, but has auto_increment properties.


The solution is to convert sequence in Oracle to use the Auto_increment attribute, and in some cases there may be a way to resolve
Problem, create a new stand-alone table to specifically record the autogrow data.


4, the difference of data type.
In MySQL, there is no varchar2, number,mysql, and numeric in Oracle, and of course there is no MySQL time type in Oracle.


The workaround is to replace.


5. The difference between the index length limit.
Starting with MySQL 4.1.2, the MyISAM and InnoDB table index lengths support 1000 bytes, which means that the index field cannot be longer than 1000
Bytes, if more than will report such a mistake:
ERROR 1071 (42000): Specified key was too long; Max key length is bytes.
If it is UTF-8 encoded, it is equivalent to the length of 333 characters (because UTF8 one character occupies 3 bytes). Oracle has a much looser index length limit than MySQL.


There is no need to say more about the solution, either change the definition of the index, or change the definition length of the field.


Second, for database compatibility, we should pay attention to what.
Database compatibility should be a concern for database design, because sometimes the customer has a database that is already in use, and
Do not want to maintain two databases at the same time, so compatible with a variety of databases can also become a selling point of the product.


The key to database compatibility is compliance with standard usage.


1, abide by the standard usage, try not to use a database-specific usage.
such as the use of msyql ' symbols,
For example, many people have this usage,
Create sequence when using Oracle development, select Seq.nextval from DUAL before inserting data into the table;
The value of the query is then inserted into the table as value, which is not adaptable to databases that do not have sequence, and each database has a self-
Usage of the dynamic growth type should be used completely if needed.
For example, different databases have extended paging queries, and PostgreSQL has offset,limit,oracle.


2, reserved words.
Requires that the database Designer try not to use reserved words for table names and field names. There are also many people who have this usage, adding ' _ ' to the table name and field name,
Like this: Create TABLE _tablename (_id integer). This will never cause problems with reserved words.


3, to avoid database case sensitive issues.
Select whether the database table name and field name are in uppercase or lowercase, and are fully unified during database design and coding.
When using the Convert Oracle to Mysql tool, note the control of the option "name is converted to uppercase"

Considerations for migrating data from MySQL to Oracle