MySQL to Oracle Program Migration Considerations (reprint)

Considerations for mysql|oracle| program MySQL to Oracle program migration

2001-09


There are many application projects, just started with the MySQL database can basically achieve a variety of functional requirements, with the increase in application users,
The increase in the amount of data, MySQL gradually overwhelmed the situation: the connection is slow or even downtime, so there is the data from MySQL relocated
Oracle's needs, the application will have to make some changes accordingly. I have summed up the following points of attention, I hope to help you.

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 automatically grow the data type, need to establish an automatic growth of the serial number, insert the record when the serial number of the next
Value is assigned to this field.

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;
The maximum value is determined by the length of the field, and if the defined automatically-growing serial number (6), the maximum is 999999
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. In inserting and modifying strings
Must be replaced by single quotes: Replace all occurrences of one single quote with two single quotes.

3. The processing of the SQL statement page
MySQL processing pages of the SQL statement is relatively simple, with limit start position, record number; PHP can also use seek to locate the result
The location of the set.
Oracle handles page-flipping SQL statements is cumbersome. Each result set has only one rownum field that indicates its location, and can only
With rownum<100, you can't use 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 ID from (SELECT)
RowNum as Numrow, ID from table_name WHERE Condition 1 order by condition 2 where Numrow >
Numrow <) 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 > and Numrow < (m) Order by condition 3;

4. Processing of long strings
Processing of long strings Oracle also has its own special place. Maximum operable string length of insert and update is less than or equal to
4,000 single-byte, if you want to insert a longer string, consider the CLOB type for the field, and use the DBMS_LOB program from Oracle.
Package. Make sure you do not empty and length judgments before inserting a change record, and you should warn that field values that cannot be empty, and values that exceed the length field.
Returns the last action.

5. Processing of date fields
The MySQL date field is divided into date and time two, and the Oracle Date field is only date, containing the seconds and minutes of the year and the current database
The system time is sysdate, accurate to seconds, or converted to a date-type function by a string to_date (' 2001-08-01 ', ' yyyy-mm-dd ')
Year-month-day 24 hours: minutes: SEC format yyyy-mm-dd HH24:MI:SS to_date () There are many different date formats to see
ORACLE DOC.
Date-type fields are converted to String functions to_char (' 2001-08-01 ', ' yyyy-mm-dd HH24:MI:SS ')

The mathematical formula for the date field is very different.
MySQL found 7 days from the current time
Date_field_name > Subdate (Now (), INTERVAL 7 day)
Oracle finds 7 days from current time
Date_field_name >SYSDATE-7;

6. 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, the null character should be judged when the data is directed
broken, if NULL or null character, you need to change it to a space string.

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

8. Procedures and functions, the operation of the database work, please note that the result set and the release of the pointer.


Interested to see the MySQL Administrator's Guide