Considerations for migrating mysql to an oracle database

For databases, mysql can be suitable for data development in small and medium-sized enterprises, but it can be a very large data application in oracle. If baidu and sina use the ipve database, the following describes how to migrate a mysql database to an oracle database without any problems and precautions.

For databases, mysql can be suitable for data development in small and medium-sized enterprises, but it can be a very large data application in oracle. If baidu and sina use the ipve database, the following describes how to migrate a mysql database to an oracle database without any problems and precautions.

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)

The Code is as follows:
Increment by 1 start with 1 MAXVALUE 99999 cycle nocache;

The maximum value is determined by the length of the field. If the defined auto-increment serial NUMBER (6), the maximum value is 999999.

INSERT statement to 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.

3. Processing of paging SQL statements

The SQL statement for MYSQL to process paging is relatively simple. It uses LIMIT to start the position and record the number. In PHP, you can also use SEEK to locate the result set.

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:

The Code is as follows:
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:

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

4. Processing long strings

ORACLE processing of long strings also has some special features. The maximum length of a string that can be operated during INSERT and UPDATE is less than or equal to 4000 single bytes. If you want to INSERT a longer string, use the CLOB type for the field to 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.

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 current system TIME is SYSDATE, accurate to seconds, or convert the string into a date-type function.

TO_DATE ('2017-08-01 ', 'yyyy-MM-DD ')

Year-month-day 24 hours: minute: Second format YYYY-MM-DD HH24: MI: SS TO_DATE () There are many date formats, see oracle doc.

Convert a datetime field to a string Function

TO_CHAR ('2017-08-01 ', 'yyyy-MM-DD HH24: MI: ss ')

The mathematical formulas for date fields vary greatly.

MYSQL found 7 days from current time

DATE_FIELD_NAME> SUBDATE (NOW (), INTERVAL 7 DAY)

ORACLE found 7 days from current time

DATE_FIELD_NAME> SYSDATE-7;

6. 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.

7. Fuzzy comparison of strings

MYSQL uses the field name like '% string %'

You can also use the field name like '% string %' in ORACLE. However, this method cannot use indexes and is not fast.

Use a string to compare the instr function (field name, 'string') to 0 to get more accurate search results.

8. Programs and functionsAfter operating the database, pay attention to the release of the result set and pointer.

Editor's summary: we should pay attention to eight points during oracle migration, such as id auto-increment, empty characters, fuzzy comparison of strings, functions in related mysql, time and date processing, and single quotation marks.