Data migration from Mysql to Oracle what _mysql need to be aware of

The considerations for migrating data from MySQL to Oracle are as follows

1. Automatic growth of data type processing

MySQL has an automatically growing data type, and the data value is automatically obtained when you insert a record without manipulating this field. Oracle does not have an automatically growing data type, an automatic growth sequence number is required, and the next value of the serial number is assigned to the field when the record is inserted.
The name of the CREATE SEQUENCE serial number (preferably the 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. Single quotation mark processing

MySQL can be used in double quotes wrap strings, Oracle can only use single quotes wrap strings. A single quotation mark must be replaced before inserting and modifying a string: Replace all occurrences of one single quote with two single quotes. Of course, you don't have to consider this if you use the Convert Mysql to Oracle tool.

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-byte, and if you want to insert a longer string, consider using the CLOB type for the field to borrow the Dbms_lob package from Oracle. Be sure to do non-null and length judgments before inserting a change record, and you should warn about field values that are not empty and values that exceed the length field, and return to the last action.

4. The processing of the SQL statement page

MySQL processing pages of the SQL statement is relatively simple, with limit start position, the number of records. Oracle handles page-flipping SQL statements is cumbersome. Each result set has only one rownum field that indicates its position, and can only be used rownum<100, not rownum>80.

The following are two of the better Oracle page-Flipping SQL statements (field names with IDs as unique keywords):
Statement one:

SELECT ID, Field_name,.. .
From table_name
where ID in (select
rownum as Numrow, ID from
table_name
where condition 1
ORD ER by Condition 2
WHERE numrow >
numrow < (MB) 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 < (m) Order by
condition 3;

5. Processing of date fields

The MySQL date field is divided into date and time two, the Oracle Date field is only date, contains the minutes and seconds of the date, and is sysdate to the second with the current database system.
The mathematical formula for the date field is very different.
MySQL found 7 days from current time:date_field_name > Subdate (Now (), INTERVAL 7 day)
Oracle finds 7 days away from current time:date_field_name >SYSDATE-7;

6. Fuzzy Comparison of strings

MySQL in the field name like '% string% ', Oracle can also use the field name "%"% string "but this method can not be used
Index, speed is not fast, with string comparison function InStr (field name, ' string ') >0 will get more accurate finding results.

7. Processing of NULL characters

MySQL's Non-empty field also has empty content, Oracle defined a NON-EMPTY field does not allow empty content. The Oracle table structure is defined according to the NOT null of MySQL, and errors are generated when data is directed. Therefore, to guide the data to judge the null character, if null or empty characters, you need to change it to a space string.

Oracle porting to MySQL considerations

The customer database is MySQL, and the developed product supports Oracle, we have to migrate the database environment from Oracle to MySQL. Some of the following problems were encountered during the porting process, and if we were to focus on the porting of the database during the initial design and coding process, there would be no need for additional work at all.

One, the database environment from Oracle porting to MySQL encountered problems.

1, Case sensitive difference (if the server OS is Linux).

In Oracle, it is typically case-insensitive. Sometimes we use Oracle's problem of not paying attention to case, the table name and field name without double quotes are case-insensitive, like this: INSERT INTO TableName and insert into TableName effects are the same, using tools to export creation/data initialization scripts, The resulting general table name and field name are converted to uppercase.

In MySQL, however, the case sensitivity of the operating system determines the case sensitivity of the database name and table name. The database corresponds to a directory in the data directory where 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 case sensitivity of the database name and table name. It is case sensitive in Linux-kernel operating systems.

The solution is to keep the MySQL database name consistent with Oracle's case, and the table name is maintained with the table name in the SQL string in the application
Consistent, if the field name in your application has double quotes, keep the field name in SQL consistent with the characters in the double quotes. If
Your application refers to the table name, the field is not uniform case, that would be a big trouble.

2, the difference between reserved words.

function names like SQL language (such as: inteval,show) are reserved words. Reserved words in Oracle can be used as table names and field names, and do not affect use, but reserved words in MySQL cannot be used as table names and field names, if they are reported with syntax errors.

workaround , which causes the reserved word in the SQL statement to be used as the "' symbol, which is positioned above the keyboard's tab key, and another way to tablename the field name. Field name. Like this:
INSERT INTO tablename (ID, ' interval ') value (...)
or
INSERT INTO tablename (ID, tablename.inteval) value (...) .

3, the automatic growth type difference.

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 solve the problem by creating a separate table to specifically record automatically-growing data.

4, the data type difference.
There is no MySQL-like varchar2, Number,mysql in MySQL, and the corresponding varchar, numeric, of course, there is no time type in Oracle.

The solution is to replace.

5. The difference of index length limit.

Starting with the MySQL 4.1.2, the MyISAM and InnoDB table index lengths support 1000 bytes, which means the length of the indexed field cannot exceed 1000 bytes, if it exceeds the error that is reported:
ERROR 1071 (42000): Specified key was too long; Max key length is 1000 bytes.
If it is a UTF-8 encoding, the equivalent of 333 characters (because UTF8 is 3 bytes). Oracle's index length limit is much looser than MySQL.

Solution : You don't have to say more, either change the definition of the index or change the definition length of the field.

Second, we should pay attention to the compatibility of the database.
database compatibility should be a problem that database design should focus on, because sometimes customers have databases that are already in use, and
You don't want to maintain two databases at the same time, so compatibility with a variety of databases can be a selling point for your product.

The key to database compatibility is adherence to standard usage.

1, adhere to the standard usage, try not to use some kind of database-specific usage.

such as the use of "msyql" symbols, and again, many people have this use, to create sequence when using Oracle development, select Seq.nextval from DUAL before inserting data into tables, and then insert the value from the query as value into the table , this usage cannot adapt to the database without sequence, each database has its own
The usage of the dynamic growth type should be used completely if needed.
For example, different databases extend the paging query, and PostgreSQL have offset,limit,oracle.

2, reserved words.

The Database Designer is required to try not to use reserved words as table names and field names. There are also many people who have this usage, add ' _ ' to the table name and field name,
Like this: Create TABLE _tablename (_id integer). This will never result in a problem with reserved words.

3, to avoid the problem of sensitive database case.

Select whether the database table name and field name are in uppercase or lowercase and are fully unified in the design and encoding of the database.
When using the Convert Oracle to Mysql tool, be aware of the control of the "name conversion to uppercase" option.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.